Neo-ZQYY/docs/prd/specs/board-finance-dws-area-refactor.md

SPEC: 财务看板 DWS 区域维度重构

创建日期：2026-03-28 前置 SPEC：board-finance-phase2（已完成）、board-finance-phase2-validation（已完成） 状态：待确认 优先级：P1

---

一、背景与问题

当前财务看板后端 6 个板块的数据来源分散：

• overview/cashflow/recharge 从 dws_finance_daily_summary（全局日汇总，无区域维度）
• revenue 从 dwd_settlement_head（结算单级别，有桌台→区域映射）
• expense 从 dws_finance_expense_summary + dws_platform_settlement
• coach_analysis 从 dws_assistant_salary_calc

核心 bug：area≠all 时，优惠查询仍从全局 DWS 取数，导致 B区发生额 ¥43,049 但优惠 ¥179,884（全局优惠），优惠占比 417.9%。

二、方案概述

两层架构：

• 方案 A（原子层）：新建 dws_finance_area_daily，按 (stat_date, area_code) 日粒度存储，ETL 每天计算
• 方案 B（缓存层）：新建 dws_finance_board_cache，缓存已完成周期的聚合结果，避免重复计算

后端查询逻辑：先查缓存 → 未命中则从日粒度表实时 SUM。

三、方案 A — 原子层 dws_finance_area_daily

3.1 表结构

CREATE TABLE dws.dws_finance_area_daily (
    id              BIGSERIAL PRIMARY KEY,
    site_id         BIGINT NOT NULL,
    tenant_id       BIGINT NOT NULL,
    stat_date       DATE NOT NULL,
    area_code       VARCHAR(20) NOT NULL,  -- all/hall/hallA/hallB/hallC/vip/snooker/mahjong/ktv

    -- ── 收入结构（从 dwd_settlement_head 按区域聚合）──
    table_fee_amount        NUMERIC(14,2) NOT NULL DEFAULT 0,
    goods_amount            NUMERIC(14,2) NOT NULL DEFAULT 0,
    assistant_pd_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,  -- 基础课（陪打）
    assistant_cx_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,  -- 激励课（超休）
    gross_amount            NUMERIC(14,2) NOT NULL DEFAULT 0,  -- = 四项之和

    -- ── 优惠拆分（从 dwd_settlement_head 按区域聚合，6 项恒等式）──
    discount_groupbuy       NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_vip            NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_manual         NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_gift_card      NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_rounding       NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_other          NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_total          NUMERIC(14,2) NOT NULL DEFAULT 0,  -- = 6 项之和

    -- ── 确认收入 ──
    confirmed_income        NUMERIC(14,2) NOT NULL DEFAULT 0,  -- = gross_amount - discount_total

    -- ── 现金流（仅 area_code='all' 时有值，区域级无法拆分）──
    cash_pay_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_paper_amount       NUMERIC(14,2) NOT NULL DEFAULT 0,
    scan_pay_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    groupbuy_pay_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
    recharge_cash_inflow    NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_inflow_total       NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_outflow_total      NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_balance_change     NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- ── 卡消费（仅 area_code='all'）──
    card_consume_total      NUMERIC(14,2) NOT NULL DEFAULT 0,
    recharge_card_consume   NUMERIC(14,2) NOT NULL DEFAULT 0,
    gift_card_consume       NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- ── 充值（仅 area_code='all'）──
    recharge_cash           NUMERIC(14,2) NOT NULL DEFAULT 0,
    first_recharge_cash     NUMERIC(14,2) NOT NULL DEFAULT 0,
    renewal_cash            NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- ── 订单统计 ──
    order_count             INTEGER NOT NULL DEFAULT 0,

    -- ── 元数据 ──
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (site_id, stat_date, area_code)
);

3.2 area_code 枚举

area_code	含义	物理区域映射
all	全部区域	所有桌台
hall	大厅（A+B+C+包厢+斯诺克+麻将+团建）	同 all（历史兼容）
hallA	A区	site_table_area_name = 'A区'
hallB	B区	site_table_area_name = 'B区'
hallC	C区	'C区'/'TV台'/'美洲豹赛台'
vip	台球包厢	'VIP包厢'
snooker	斯诺克	'斯诺克区'
mahjong	麻将房	'麻将房'/'M7'/'M8'/'666'/'发财'
ktv	团建房	'K包'/'k包活动区'/'幸会158'

3.3 ETL 计算逻辑

每次 ETL 运行时，对当天（按营业日切点 BUSINESS_DAY_START_HOUR=8）：

1. 从 dwd_settlement_head + dim_table 按区域聚合收入和优惠字段
2. all 行 = 所有区域之和 + 现金流/充值等全局字段（从现有 dws_finance_daily_summary 逻辑复用）
3. 各区域行 = 该区域的结算单聚合，现金流/充值字段为 0（无法按区域拆分）
4. delete-before-insert 策略：DELETE WHERE site_id=X AND stat_date=Y; INSERT 9 行

3.4 优惠按区域拆分方案

优惠字段从 dwd_settlement_head 按桌台区域直接聚合（不再从 DWS 全局表取）：

-- 每张结算单通过 table_id → dim_table.site_table_area_name → area_code 映射
SELECT area_code,
       SUM(coupon_amount) AS discount_groupbuy,        -- 团购券抵扣
       SUM(member_discount_amount) AS discount_vip,     -- 会员折扣
       SUM(adjust_amount) AS discount_manual_raw,       -- 手动调整（含大客户优惠）
       SUM(gift_card_amount) AS discount_gift_card,     -- 赠送卡抵扣（= balance_amount - recharge_card_amount）
       SUM(rounding_amount) AS discount_rounding        -- 抹零
FROM dwd_settlement_head h
JOIN dim_table t ON h.table_id = t.table_id AND t.scd2_is_current = 1
WHERE settle_type IN (1, 3)
  AND biz_date(create_time, 8) = :stat_date
GROUP BY area_code

这样每个区域的优惠就是该区域桌台上实际发生的优惠，不存在分摊问题。

3.5 优惠按区域拆分 — 样例验证

以 2026-03 本月 B区为例，对比修复前后：

指标	修复前（全局优惠）	修复后（按结算单归属）
B区发生额	¥43,049	¥43,050
B区优惠	¥179,884	¥31,327
优惠占比	417.9%	72.8%

B区 72.8% 的优惠率仍偏高，但查看具体结算单后确认是真实业务现象：

• Top 8 优惠单中 7 张是会员折扣 = 台费全额（会员用储值卡全额抵扣）
• 手动调整和抹零在 B区几乎为 0
• 不存在"整单优惠被错误归属到小区域"的问题——每张结算单对应一张桌台

结论：按结算单直接归属区域是正确的，不需要分摊。

注意：discount_gift_card 的口径是赠送卡消费金额（ETL 中 gift_card_consume_amount），不是结算单的 gift_card_amount（赠送卡支付金额）。新表需要复用现有 ETL 的同一计算逻辑。

四、方案 B — 缓存层 dws_finance_board_cache

4.1 表结构

CREATE TABLE dws.dws_finance_board_cache (
    id              BIGSERIAL PRIMARY KEY,
    site_id         BIGINT NOT NULL,
    time_range      VARCHAR(20) NOT NULL,  -- month/lastMonth/week/lastWeek/quarter/lastQuarter/quarter3/half6
    area_code       VARCHAR(20) NOT NULL,  -- all/hall/hallA/.../ktv
    start_date      DATE NOT NULL,         -- 当期起始日
    end_date        DATE NOT NULL,         -- 当期截止日
    prev_start_date DATE,                  -- 上期起始日（环比用）
    prev_end_date   DATE,                  -- 上期截止日

    -- ── 经营一览（overview）──
    occurrence          NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount            NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_rate       NUMERIC(8,4) NOT NULL DEFAULT 0,
    confirmed_revenue   NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_in             NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_out            NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_balance        NUMERIC(14,2) NOT NULL DEFAULT 0,
    balance_rate        NUMERIC(8,4) NOT NULL DEFAULT 0,

    -- ── 数据指纹（用于缓存失效检测）──
    data_fingerprint    VARCHAR(64),        -- 源数据 hash，用于检测补录导致的数据变化
    computed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- ── 元数据 ──
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (site_id, time_range, area_code)
);

4.2 缓存策略

时间范围	缓存行为	失效条件
month/week/quarter	不缓存（当期数据每天在变）	—
lastMonth/lastWeek/lastQuarter	缓存（已完成周期）	数据指纹变化
quarter3/half6	缓存（不含本月）	数据指纹变化

4.3 数据指纹机制

每次 ETL 计算完日粒度数据后，对已完成周期的源数据计算指纹：

# 指纹 = 该时间范围内所有日粒度行的 (stat_date, gross_amount, discount_total) 的 hash
fingerprint = hashlib.md5(
    json.dumps(sorted(rows, key=lambda r: r['stat_date'])).encode()
).hexdigest()

ETL 流程：

1. 计算当天日粒度数据（方案 A）
2. 对每个已完成周期，计算新指纹
3. 与缓存表中的 data_fingerprint 对比
4. 不一致 → 重算该周期的缓存（从日粒度表 SUM）
5. 一致 → 跳过

五、后端查询改造

5.1 查询流程

请求: GET /api/xcx/board/finance?time=X&area=Y&compare=Z

1. 判断 time_range 是否为已完成周期
2. 已完成周期 → 查 dws_finance_board_cache
   - 命中 → 直接返回缓存数据
   - 未命中 → 从日粒度表 SUM，写入缓存后返回
3. 当期周期 → 从 dws_finance_area_daily SUM
4. compare=1 → 对上期也执行同样逻辑，然后 calc_compare

5.2 各板块数据来源改造

板块	当前来源	改造后来源
overview	dws_finance_daily_summary（全局）	dws_finance_area_daily（按 area_code 过滤）
recharge	dws_finance_recharge_summary + dws_finance_daily_summary	不变（仅 area=all 时显示）
revenue	dwd_settlement_head（实时查 DWD）+ dws_finance_daily_summary（优惠/渠道）	dws_finance_area_daily（收入+优惠+渠道全部预计算）
cashflow	dws_finance_daily_summary（全局）	dws_finance_area_daily（area_code='all'，现金流无法按区域拆分）
expense	dws_finance_expense_summary + dws_platform_settlement	不变（仅 area=all 时显示）
coach_analysis	dws_assistant_salary_calc	不变（仅 area=all 时显示）

5.3 area≠all 时的行为

area≠all 时，只有 overview 和 revenue 需要按区域过滤：

• overview：从 dws_finance_area_daily WHERE area_code=Y SUM
• revenue：从 dws_finance_area_daily WHERE area_code=Y SUM，构建 structure_rows/discount_items/price_items/channel_items
• cashflow/expense/coach_analysis：仍用全局数据（前端隐藏这些板块，但后端仍返回）
• recharge：返回 null

5.4 接口契约（硬约束 — 前端零改动）

本次重构仅改变后端数据来源（从实时查 DWD/DWS → 查预计算的 dws_finance_area_daily），API 签名和返回数据结构完全不变。

5.4.1 API 签名不变

GET /api/xcx/board/finance?time={FinanceTimeEnum}&area={AreaFilterEnum}&compare={0|1}
Authorization: Bearer {token}
Response: FinanceBoardResponse (response_model_exclude_none=True)

5.4.2 返回结构不变（Pydantic Schema 不改）

以下 Schema 类保持原样，不新增、不删除、不改名任何字段：

Schema 类	说明
FinanceBoardResponse	顶层：overview + recharge? + revenue + cashflow + expense + coach_analysis
OverviewPanel	8 项核心指标 + 8 组环比字段
RechargePanel	储值卡 5 指标 + 赠送卡 3×4 矩阵 + 全卡余额
RevenuePanel	structure_rows + price_items + discount_items + channel_items + 总计
CashflowPanel	consume_items + recharge_items + total
ExpensePanel	4 组 items + total
CoachAnalysisPanel	basic + incentive（各含 rows + 总计）

5.4.3 字段值语义不变

字段	语义约束
overview.discountRate	area=all 时 0~1；area≠all 时可能 > 1（区域级优惠占比）
overview.occurrence/discount/confirmedRevenue	area≠all 时 = revenue 板块的对应值（后端覆盖逻辑保留）
recharge	area≠all 时为 null
revenue.discount_items	固定 5 项（团购/会员折扣/手动调整/赠送卡/其他）
revenue.channel_items	固定 3 项（储值卡结算冲销/现金线上支付/团购核销）
cashflow.total	≥ SUM(consume_items) + SUM(recharge_items)（可能包含额外项）
环比字段	compare=1 时非空（"X.X%"/"持平"/"新增"），compare=0 时为 null

5.4.4 前端验证清单

重构完成后，用现有 scripts/ops/validate_board_finance.py 跑 39 组合验证：

• area=all 时所有板块数据与重构前完全一致（回归测试）
• area≠all 时优惠数据合理（discountRate 不再出现 400%+ 的异常值）
• 环比数据基于该区域的历史数据（不是全局对比）

六、ETL 任务设计

6.1 新增任务：DWS_FINANCE_AREA_DAILY

• 位置：apps/etl/connectors/feiqiu/tasks/dws/finance_area_daily.py
• 依赖：DWD_LOAD_FROM_ODS（结算单已入 DWD）
• 调度：每小时（与现有 DWS_FINANCE_DAILY 同频）
• 策略：delete-before-insert（按 site_id + stat_date 删除 9 行后重新插入）

计算步骤：

1. 从 dwd_settlement_head + dim_table 按区域聚合收入和优惠
2. 从现有 dws_finance_daily_summary 取全局现金流/充值/卡消费字段
3. 构建 9 行（all + 8 个区域），all 行 = 各区域之和 + 全局字段
4. 写入 dws_finance_area_daily

6.2 新增任务：DWS_FINANCE_BOARD_CACHE

• 位置：apps/etl/connectors/feiqiu/tasks/dws/finance_board_cache.py
• 依赖：DWS_FINANCE_AREA_DAILY
• 调度：每天一次（营业日切点后）
• 策略：指纹对比，不一致则重算

计算步骤：

1. 遍历已完成周期（lastMonth/lastWeek/lastQuarter/quarter3/half6）
2. 对每个周期 × 9 个区域，计算源数据指纹
3. 与缓存表对比，不一致则从 dws_finance_area_daily SUM 重算
4. 写入/更新 dws_finance_board_cache

6.3 区域映射共享配置

将区域映射从 Python 硬编码抽成共享配置，ETL 和后端共用：

# packages/shared/src/neozqyy_shared/area_mapping.py
AREA_LABEL_MAP = {
    "hallA":   ["A区"],
    "hallB":   ["B区"],
    "hallC":   ["C区", "TV台", "美洲豹赛台"],
    "vip":     ["VIP包厢"],
    "snooker": ["斯诺克区"],
    "mahjong": ["麻将房", "M7", "M8", "666", "发财"],
    "ktv":     ["K包", "k包活动区", "幸会158"],
}
# hall = 所有区域之和（不含 all）
# all = 所有区域之和

七、营业日切点

• .env 中 BUSINESS_DAY_START_HOUR=8
• ETL 使用 biz_date_sql_expr(create_time, cutoff_hour) 计算 stat_date
• 新表同样使用此切点，确保与现有 DWS 表一致

八、实施计划

阶段	任务	预估工时
T1	共享区域映射配置	0.5h
T2	DDL：创建 dws_finance_area_daily + RLS 视图	0.5h
T3	ETL：DWS_FINANCE_AREA_DAILY 任务	3h
T4	DDL：创建 dws_finance_board_cache + RLS 视图	0.5h
T5	ETL：DWS_FINANCE_BOARD_CACHE 任务（含指纹机制）	2h
T6	后端：改造 fdw_queries.py 6 个查询函数	3h
T7	后端：改造 board_service.py 缓存查询逻辑	2h
T8	验证：重跑 144 组合验证脚本	1h
T9	历史数据回填：对已有日期范围批量计算	1h

九、风险与缓解

1. 区域映射一致性：抽成共享配置（T1），ETL 和后端共用同一份映射
2. 优惠按区域拆分：直接从结算单按桌台区域聚合，不做分摊。每张结算单对应一张桌台，优惠归属该桌台所在区域
3. 缓存失效：数据指纹机制（T5），补录后自动检测并重算
4. 营业日切点：从 .env 读取，ETL 和后端共用
5. 向后兼容：新表是增量，不修改现有 dws_finance_daily_summary，可并行运行验证

十、验证标准

• 144 组合全量验证脚本通过（复用 scripts/ops/validate_board_finance.py）
• area=all 时数据与现有逻辑完全一致（回归测试）
• area≠all 时优惠数据合理（discountRate ≤ 1 或接近 1）
• 已完成周期缓存命中率 100%（第二次请求不触发 SUM 计算）