# dws_order_summary 订单汇总宽表

> 生成时间：2026-02-13

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_order_summary |
| 主键 | (site_id, order_settle_id) |
| 数据来源 | dwd_settlement_head + 关联明细表 |
| 更新频率 | 每小时增量更新 |
| 说明 | 以订单为粒度的汇总宽表，整合台费、助教、商品、团购、优惠、支付、流水等维度 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | site_id | BIGINT | NO | 门店ID（联合主键） |
| 2 | order_settle_id | BIGINT | NO | 结算单ID（联合主键） |
| 3 | order_trade_no | VARCHAR | YES | 订单交易号 |
| 4 | order_date | DATE | NO | 订单日期（优先 pay_time，其次 create_time） |
| 5 | tenant_id | BIGINT | NO | 租户ID |
| 6 | member_id | BIGINT | YES | 会员ID（NULL 或 0 为散客） |
| 7 | member_flag | BOOLEAN | NO | 是否会员订单（来源：`is_bind_member`） |
| 8 | recharge_order_flag | BOOLEAN | NO | 充值订单标记（`consume_money = 0` 且实付>0，此处 consume_money 仅用于零值判断，不参与金额计算） |
| 9 | item_count | INTEGER | NO | 订单项数 |
| 10 | total_item_quantity | INTEGER | NO | 订单项总数量 |
| 11 | table_fee_amount | NUMERIC | NO | 台费金额 |
| 12 | assistant_service_amount | NUMERIC | NO | 助教服务金额（= `assistant_pd_money` + `assistant_cx_money`） |
| 13 | goods_amount | NUMERIC | NO | 商品金额 |
| 14 | group_amount | NUMERIC | NO | 团购金额 |
| 15 | total_coupon_deduction | NUMERIC | NO | 优惠券抵扣总额 |
| 16 | member_discount_amount | NUMERIC | NO | 会员折扣金额 |
| 17 | manual_discount_amount | NUMERIC | NO | 手动折扣金额 |
| 18 | order_original_amount | NUMERIC | NO | 原价估算（= `total_paid_amount + total_coupon_deduction + member_discount_amount + manual_discount_amount`） |
| 19 | order_final_amount | NUMERIC | NO | 最终应付金额 |
| 20 | stored_card_deduct | NUMERIC | NO | 储值卡抵扣金额（= `balance_amount`，即 `recharge_card_amount + gift_card_amount`） |
| 21 | external_paid_amount | NUMERIC | NO | 外部支付金额（实付-卡类抵扣） |
| 22 | total_paid_amount | NUMERIC | NO | 总实付金额 |
| 23 | book_table_flow | NUMERIC | NO | 台费流水 |
| 24 | book_assistant_flow | NUMERIC | NO | 助教流水 |
| 25 | book_goods_flow | NUMERIC | NO | 商品流水 |
| 26 | book_group_flow | NUMERIC | NO | 团购流水 |
| 27 | book_order_flow | NUMERIC | NO | 订单总流水（台费+助教+商品+团购） |
| 28 | order_effective_consume_cash | NUMERIC | NO | 有效消费现金（= `GREATEST(total_paid_amount - stored_card_deduct, 0)`，即外部支付金额） |
| 29 | order_effective_recharge_cash | NUMERIC | NO | 有效充值现金（当前硬编码为 0，占位字段，待后续实现） |
| 30 | order_effective_flow | NUMERIC | NO | 有效流水（当前 = `total_paid_amount`） |
| 31 | refund_amount | NUMERIC | NO | 退款金额 |
| 32 | net_income | NUMERIC | NO | 净收入（实付-退款） |
| 33 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 34 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 业务口径

> ⚠️ 本表金额字段基于 `items_sum` 各分项（`table_charge_money + goods_money + assistant_pd_money + assistant_cx_money + electricity_money`），
> 不使用 `consume_money`（存在三种历史口径混合）。

- order_date 优先取 pay_time，其次 create_time
- recharge_order_flag：`consume_money = 0` 且实付>0 时标记为充值订单（此处 consume_money 仅用于零值判断，不参与金额计算）
- stored_card_deduct = `balance_amount`（恒等式：`balance_amount = recharge_card_amount + gift_card_amount`，三者不可相加）
- order_original_amount = `total_paid_amount + total_coupon_deduction + member_discount_amount + manual_discount_amount`（实付 + 团购抵扣 + 会员折扣 + 手动调整）
- external_paid_amount = total_paid_amount - stored_card_deduct（外部支付 = 实付 - 储值卡抵扣）
- book_order_flow = 台费 + 助教 + 商品 + 团购
- order_effective_recharge_cash：当前硬编码为 0，占位字段
- order_effective_consume_cash = `GREATEST(total_paid_amount - stored_card_deduct, 0)`（与 external_paid_amount 同值）
- order_effective_flow = `total_paid_amount`（当前实现）
- net_income = total_paid_amount - refund_amount

## 使用说明

```sql
-- 按日统计订单概况
SELECT 
    order_date,
    COUNT(*) AS order_count,
    SUM(order_final_amount) AS total_amount,
    SUM(net_income) AS net_income,
    SUM(CASE WHEN member_flag THEN 1 ELSE 0 END) AS member_orders
FROM dws.dws_order_summary
WHERE site_id = :site_id
GROUP BY order_date
ORDER BY order_date DESC;
```

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯 |
| 依赖表 | dwd_settlement_head, dwd_table_fee_log, dwd_assistant_service_log, dwd_store_goods_sale, dwd_groupbuy_redemption, dwd_refund, dwd_refund_ex |
