# dws_finance_area_daily 区域日粒度财务原子层表

> 生成时间：2026-03-28
> 关联 SPEC：board-finance-dws-area-refactor

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_finance_area_daily |
| 主键 | id |
| 唯一键 | (site_id, stat_date, area_code) |
| 数据来源 | dwd_settlement_head + dim_table + dws_finance_daily_summary |
| 更新频率 | 每小时更新当日数据 |
| 幂等策略 | delete-before-insert（按 site_id + stat_date 删除后插入 9 行） |
| 说明 | 按 (site_id, stat_date, area_code) 粒度存储 9 个区域的收入/优惠/现金流预计算数据 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | stat_date | DATE | NO | 统计日期（营业日，按 BUSINESS_DAY_START_HOUR=8 切点） |
| 5 | area_code | VARCHAR(20) | NO | 区域编码：all/hall/hallA/hallB/hallC/vip/snooker/mahjong/ktv |
| 6 | table_fee_amount | NUMERIC(14,2) | NO | 台费正价 |
| 7 | goods_amount | NUMERIC(14,2) | NO | 商品正价 |
| 8 | assistant_pd_amount | NUMERIC(14,2) | NO | 助教基础课正价（陪打） |
| 9 | assistant_cx_amount | NUMERIC(14,2) | NO | 助教激励课正价（超休） |
| 10 | gross_amount | NUMERIC(14,2) | NO | 毛收入 = 四项之和 |
| 11 | discount_groupbuy | NUMERIC(14,2) | NO | 团购优惠 |
| 12 | discount_vip | NUMERIC(14,2) | NO | 会员折扣 |
| 13 | discount_manual | NUMERIC(14,2) | NO | 手动调整（adjust_amount） |
| 14 | discount_gift_card | NUMERIC(14,2) | NO | 赠送卡消费金额口径 |
| 15 | discount_rounding | NUMERIC(14,2) | NO | 抹零 |
| 16 | discount_other | NUMERIC(14,2) | NO | 其他优惠 |
| 17 | discount_total | NUMERIC(14,2) | NO | 优惠合计 = 六项之和 |
| 18 | confirmed_income | NUMERIC(14,2) | NO | 确认收入 = gross_amount - discount_total |
| 19 | cash_pay_amount | NUMERIC(14,2) | NO | 收银实付（仅 all 行有效） |
| 20 | cash_paper_amount | NUMERIC(14,2) | NO | 纸币支付（仅 all 行有效） |
| 21 | scan_pay_amount | NUMERIC(14,2) | NO | 扫码支付（仅 all 行有效） |
| 22 | groupbuy_pay_amount | NUMERIC(14,2) | NO | 团购支付金额（仅 all 行有效） |
| 23 | recharge_cash_inflow | NUMERIC(14,2) | NO | 充值现金流入（仅 all 行有效） |
| 24 | cash_inflow_total | NUMERIC(14,2) | NO | 现金流入合计（仅 all 行有效） |
| 25 | cash_outflow_total | NUMERIC(14,2) | NO | 现金流出合计（仅 all 行有效） |
| 26 | cash_balance_change | NUMERIC(14,2) | NO | 现金余额变动（仅 all 行有效） |
| 27 | card_consume_total | NUMERIC(14,2) | NO | 卡消费合计（仅 all 行有效） |
| 28 | recharge_card_consume | NUMERIC(14,2) | NO | 充值卡消费（仅 all 行有效） |
| 29 | gift_card_consume | NUMERIC(14,2) | NO | 赠送卡消费（仅 all 行有效） |
| 30 | recharge_cash | NUMERIC(14,2) | NO | 充值现金（仅 all 行有效） |
| 31 | first_recharge_cash | NUMERIC(14,2) | NO | 首充现金（仅 all 行有效） |
| 32 | renewal_cash | NUMERIC(14,2) | NO | 续充现金（仅 all 行有效） |
| 33 | order_count | INTEGER | NO | 结账单数 |
| 34 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 35 | updated_at | TIMESTAMPTZ | NO | 更新时间 |


## 约束与索引

| 约束/索引 | 类型 | 列 |
|-----------|------|-----|
| dws_finance_area_daily_pkey | PRIMARY KEY | id |
| dws_finance_area_daily_site_id_stat_date_area_code_key | UNIQUE | (site_id, stat_date, area_code) |

## RLS 视图

```sql
CREATE OR REPLACE VIEW dws.v_dws_finance_area_daily AS
SELECT * FROM dws.dws_finance_area_daily
WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;
```

## 数学恒等式

```
gross_amount = table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount
discount_total = discount_groupbuy + discount_vip + discount_manual + discount_gift_card + discount_rounding + discount_other
confirmed_income = gross_amount - discount_total
```

- `area_code ≠ 'all'` 时：现金流/卡消费/充值字段 = 0
- `all` 行收入/优惠 = hallA~ktv 各行对应字段之和
- `hall` 行 = hallA~ktv 各行之和（历史兼容）
- 每个 (site_id, stat_date) 恰好 9 行

## 变更原因

解决财务看板在 `area≠all` 时优惠数据从全局 DWS 表取数导致区域级优惠占比严重失真的 bug（如 B区优惠占比 417.9%）。

## 兼容性影响

| 组件 | 影响 |
|------|------|
| ETL 任务 | 新增 DWS_FINANCE_AREA_DAILY 任务，依赖 DWD_LOAD_FROM_ODS |
| 后端 API | board_service.py 改为从本表查询 overview/revenue 板块数据 |
| 小程序 | 无直接影响（API 签名不变） |
| dws_finance_daily_summary | 不改动，本表的 all 行现金流/充值/卡消费复用其数据 |

## 回滚策略

```sql
DROP VIEW IF EXISTS dws.v_dws_finance_area_daily;
DROP TABLE IF EXISTS dws.dws_finance_area_daily;
```

回滚后后端需恢复到从 `dws_finance_daily_summary` 取数的旧逻辑。

## 验证 SQL

```sql
-- 1. 验证表存在且有数据
SELECT COUNT(*), COUNT(DISTINCT area_code), MIN(stat_date), MAX(stat_date)
FROM dws.dws_finance_area_daily
WHERE site_id = 1;

-- 2. 验证每天恰好 9 行
SELECT stat_date, COUNT(*) AS row_count
FROM dws.dws_finance_area_daily
WHERE site_id = 1
GROUP BY stat_date
HAVING COUNT(*) != 9
ORDER BY stat_date;

-- 3. 验证收入恒等式
SELECT stat_date, area_code,
       gross_amount,
       (table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount) AS calc_gross,
       gross_amount - (table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount) AS diff
FROM dws.dws_finance_area_daily
WHERE site_id = 1
  AND gross_amount != (table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount);

-- 4. 验证优惠恒等式
SELECT stat_date, area_code,
       discount_total,
       (discount_groupbuy + discount_vip + discount_manual + discount_gift_card + discount_rounding + discount_other) AS calc_disc,
       discount_total - (discount_groupbuy + discount_vip + discount_manual + discount_gift_card + discount_rounding + discount_other) AS diff
FROM dws.dws_finance_area_daily
WHERE site_id = 1
  AND discount_total != (discount_groupbuy + discount_vip + discount_manual + discount_gift_card + discount_rounding + discount_other);

-- 5. 验证非 all 行现金流为零
SELECT stat_date, area_code, cash_inflow_total, cash_outflow_total
FROM dws.dws_finance_area_daily
WHERE site_id = 1
  AND area_code != 'all'
  AND (cash_inflow_total != 0 OR cash_outflow_total != 0);
```

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯（delete-before-insert） |
| 数据范围 | 2025-07-16 ~ 至今 |
| 依赖表 | dwd_settlement_head, dim_table, dws_finance_daily_summary |
| 回填脚本 | `scripts/ops/backfill_finance_area_daily.py` |
