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 表结构

```sql
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 全局表取）：

```sql
-- 每张结算单通过 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 表结构

```sql
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 计算完日粒度数据后，对已完成周期的源数据计算指纹：

```python
# 指纹 = 该时间范围内所有日粒度行的 (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 和后端共用：

```python
# 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 计算）