# dws_finance_daily_summary 财务日度汇总表

> 生成时间：2026-02-03

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_finance_daily_summary |
| 主键 | id |
| 唯一键 | (site_id, stat_date) |
| 数据来源 | dwd_settlement_head + 多个DWD事实表 |
| 更新频率 | 每小时更新当日数据 |
| 说明 | 以"日期"为粒度，汇总当日财务数据 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | stat_date | DATE | NO | 统计日期 |
| 5 | gross_amount | NUMERIC(14,2) | NO | 发生额合计（= 四项正价之和：table_fee + goods + assistant_pd + assistant_cx，不含 electricity_money，不使用 `consume_money`） |
| 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 | discount_total | NUMERIC(14,2) | NO | 优惠合计 |
| 11 | discount_groupbuy | NUMERIC(14,2) | NO | 团购优惠 |
| 12 | discount_vip | NUMERIC(14,2) | NO | 会员折扣 |
| 13 | discount_gift_card | NUMERIC(14,2) | NO | 赠送卡抵扣（余额变动） |
| 14 | discount_manual | NUMERIC(14,2) | NO | 大客户优惠（从 adjust_amount 中按配置拆出） |
| 15 | discount_rounding | NUMERIC(14,2) | NO | 抹零 |
| 16 | discount_other | NUMERIC(14,2) | NO | 其他优惠（adjust_amount - 大客户优惠） |
| 17 | confirmed_income | NUMERIC(14,2) | NO | 确认收入 = 发生额 - 优惠 |
| 18 | cash_inflow_total | NUMERIC(14,2) | NO | 现金流入合计 |
| 19 | cash_pay_amount | NUMERIC(14,2) | NO | 收银实付 |
| 20 | groupbuy_pay_amount | NUMERIC(14,2) | NO | 团购支付金额 |
| 21 | platform_settlement_amount | NUMERIC(14,2) | NO | 平台回款金额（导入） |
| 22 | platform_fee_amount | NUMERIC(14,2) | NO | 平台佣金+服务费（导入） |
| 23 | recharge_cash_inflow | NUMERIC(14,2) | NO | 充值现金流入 |
| 24 | card_consume_total | NUMERIC(14,2) | NO | 卡消费合计 |
| 25 | recharge_card_consume | NUMERIC(14,2) | NO | 现金充值卡消费（= `recharge_card_amount`，仅现金充值卡支付部分，不含赠送卡） |
| 26 | gift_card_consume | NUMERIC(14,2) | NO | 赠送卡消费 |
| 27 | cash_outflow_total | NUMERIC(14,2) | NO | 现金流出合计 |
| 28 | cash_balance_change | NUMERIC(14,2) | NO | 现金余额变动 |
| 29 | recharge_count | INTEGER | NO | 充值笔数 |
| 30 | recharge_total | NUMERIC(14,2) | NO | 充值总额（含赠送） |
| 31 | recharge_cash | NUMERIC(14,2) | NO | 充值现金部分 |
| 32 | recharge_gift | NUMERIC(14,2) | NO | 充值赠送部分 |
| 33 | first_recharge_count | INTEGER | NO | 首充笔数 |
| 34 | first_recharge_amount | NUMERIC(14,2) | NO | 首充金额 |
| 35 | renewal_count | INTEGER | NO | 续充笔数 |
| 36 | renewal_amount | NUMERIC(14,2) | NO | 续充金额 |
| 37 | order_count | INTEGER | NO | 结账单数 |
| 38 | member_order_count | INTEGER | NO | 会员订单数 |
| 39 | guest_order_count | INTEGER | NO | 散客订单数 |
| 40 | avg_order_amount | NUMERIC(12,2) | NO | 平均客单价 |
| 41 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 42 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 数据来源

> ⚠️ **consume_money 口径警告**：飞球上游 `consume_money` 在不同时期存在三种口径（A/B/C），DWS 层不应直接使用。
> 应使用 `items_sum = table_charge_money + goods_money + assistant_pd_money + assistant_cx_money + electricity_money` 作为全时期一致的消费项目合计。
> 详见 [consume_money 口径详解](../../../../docs/reports/DWD-DOC/consume/consume-money-caliber.md)。

### 结账汇总：dwd_settlement_head

> ⚠️ 以下示例 SQL 使用 `DATE(pay_time)` 简化展示。实际代码使用 `biz_date_sql_expr(pay_time, cutoff_hour)` 进行营业日归属（跨日订单归前一天）。

```sql
SELECT 
    DATE(pay_time) AS stat_date,
    COUNT(*) AS order_count,
    SUM(table_charge_money) AS table_fee_amount,
    SUM(goods_money) AS goods_amount,
    SUM(assistant_pd_money) AS assistant_pd_amount,
    SUM(assistant_cx_money) AS assistant_cx_amount,
    SUM(pay_amount) AS cash_pay_amount,
    SUM(balance_amount) AS balance_pay_amount,
    SUM(recharge_card_amount) AS card_pay_amount,
    SUM(coupon_amount) AS coupon_amount,
    SUM(member_discount_amount) AS member_discount_amount,
    SUM(adjust_amount) AS adjust_amount,
    SUM(rounding_amount) AS rounding_amount,
    SUM(pl_coupon_sale_amount) AS pl_coupon_sale_amount
FROM dwd.dwd_settlement_head
WHERE site_id = :site_id
  AND settle_type IN (1, 3)  -- 仅台桌结账+商城订单，排除退货(6)/退款(7)
GROUP BY DATE(pay_time);
```

### 团购核销：dwd_groupbuy_redemption（按结账日对齐）
```sql
SELECT 
    sh.pay_time::DATE AS stat_date,
    COUNT(CASE WHEN sh.coupon_amount > 0 THEN 1 END) AS groupbuy_count,
    SUM(
        CASE 
            WHEN sh.coupon_amount > 0 THEN
                CASE 
                    WHEN sh.pl_coupon_sale_amount > 0 THEN sh.pl_coupon_sale_amount
                    ELSE COALESCE(gr.ledger_unit_price, 0)
                END
            ELSE 0
        END
    ) AS groupbuy_pay_total
FROM dwd.dwd_settlement_head sh
LEFT JOIN dwd.dwd_groupbuy_redemption gr
    ON gr.order_settle_id = sh.order_settle_id
WHERE sh.site_id = :site_id
GROUP BY sh.pay_time::DATE;
```

### 充值订单：dwd_recharge_order
```sql
SELECT 
    DATE(pay_time) AS stat_date,
    COUNT(*) AS recharge_count,
    SUM(pay_amount) AS recharge_cash,
    SUM(point_amount) AS recharge_gift,
    SUM(CASE WHEN is_first = 1 THEN 1 ELSE 0 END) AS first_recharge_count
FROM dwd.dwd_recharge_order
GROUP BY DATE(pay_time);
```

### 赠送卡消费：dwd_member_balance_change（按余额变动）
```sql
SELECT 
    change_time::DATE AS stat_date,
    SUM(ABS(change_amount)) AS gift_card_consume
FROM dwd.dwd_member_balance_change
WHERE site_id = :site_id
  AND from_type = 1
  AND change_amount < 0
  AND COALESCE(is_delete, 0) = 0
  AND card_type_id IN (:gift_card_type_ids)
GROUP BY change_time::DATE;
```

## 使用说明

**计算公式**
```
-- gross_amount 基于 items_sum 各分项（全时期一致），不使用 consume_money
gross_amount = table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount
discount_total = discount_groupbuy + discount_vip + discount_gift_card + discount_manual + discount_rounding + discount_other
confirmed_income = gross_amount - discount_total
cash_inflow_total = cash_pay_amount + platform_inflow + recharge_cash_inflow
-- platform_inflow：优先取 platform_settlement_amount（平台回款），为 0 时取 groupbuy_pay_amount（团购支付）
-- 两者互斥，不可同时计入
```

> ⚠️ `discount_manual` 存储大客户优惠（从 adjust_amount 中按配置的会员ID/订单ID拆出），`discount_other` 存储其他手动调整（= adjust_amount - 大客户优惠）。两者互斥，之和 = adjust_amount。

**支付渠道恒等式**
```
-- 以下恒等式 100% 成立（DWD-DOC 校准确认）
balance_amount = recharge_card_amount + gift_card_amount   -- 储值卡 = 充值卡 + 礼品卡
pay_amount = point_amount + cash_amount                    -- 实付 = 积分 + 现金（互斥）
```

> ⚠️ `balance_amount`（储值卡支付）是独立支付渠道，`recharge_card_amount` 和 `gift_card_amount` 是其分账明细，不可与 `balance_amount` 重复计算。

**团购券三层价格体系**
```
顾客支付价（PCR.sale_price）→ 平台结算价（SH.pl_coupon_sale_amount）→ 门店抵扣价（SH.coupon_amount）
门店补贴 = coupon_amount - pl_coupon_sale_amount
```
- `pl_coupon_sale_amount = SUM(GR.ledger_unit_price)` ✅ 100%
- `coupon_amount = SUM(GR.ledger_amount)` ✅ 100%
- P1 期间（2025-07~10）`pl_coupon_sale_amount` 恒为 0

**F2 收支平衡公式（三期差异）**
```
P1/P2（< 2026-01-15 12:45:59）:
  consume = coupon + pay + balance - rounding + adjust + member_disc + prepay(ex)

B 类过渡期（2026-01-15 12:46~18:44，约 40 笔）:
  consume = 2*coupon + pay + balance - rounding + adjust + member_disc + prepay(ex)

P3（≥ 2026-01-15 18:45，当前生效）:
  consume = coupon + pl_coupon + pay + balance - rounding + adjust + member_disc + prepay(ex)
```
- 通过率：P1/P2 99.24% | B 95.00% | P3 99.87%
- 详见 [F2 收支平衡专项](../../../../docs/reports/DWD-DOC/05-f2-balance-audit.md)

**物化汇总层（可选）**
- L1~L4 物化视图：`mv_dws_finance_daily_summary_l1` / `l2` / `l3` / `l4`
- 刷新任务：`DWS_MV_REFRESH_FINANCE_DAILY`

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯 |
| 数据范围 | 2025-07-16 ~ 至今 |
| 依赖表 | dwd_settlement_head, dwd_groupbuy_redemption, dwd_recharge_order, dwd_member_balance_change, dws_finance_expense_summary, dws_platform_settlement |
| 注意 | platform_settlement需Excel导入 |
