# dws_finance_discount_detail 优惠明细表

> 生成时间：2026-02-03

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_finance_discount_detail |
| 主键 | id |
| 唯一键 | (site_id, stat_date, discount_type_code) |
| 数据来源 | dwd_settlement_head + dwd_groupbuy_redemption + dwd_member_balance_change |
| 更新频率 | 每日更新 |
| 说明 | 以"日期+优惠类型"为粒度，分析优惠构成 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | stat_date | DATE | NO | 统计日期 |
| 5 | discount_type_code | VARCHAR(30) | NO | 优惠类型代码 |
| 6 | discount_type_name | VARCHAR(50) | NO | 优惠类型名称 |
| 7 | discount_amount | NUMERIC(14,2) | NO | 优惠金额 |
| 8 | discount_ratio | NUMERIC(5,4) | NO | 优惠占比（占总优惠） |
| 9 | usage_count | INTEGER | NO | 使用次数 |
| 10 | affected_orders | INTEGER | NO | 影响订单数 |
| 11 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 12 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 优惠类型说明

| discount_type_code | discount_type_name | 数据来源 |
|--------------------|--------------------|----------|
| GROUPBUY | 团购优惠 | dwd_settlement_head.coupon_amount - 团购实付 |
| VIP | 会员折扣 | dwd_settlement_head.member_discount_amount |
| GIFT_CARD_TABLE | 台费卡抵扣 | dwd_member_balance_change（`card_type_id = 2791990152417157`） |
| GIFT_CARD_DRINK | 酒水卡抵扣 | dwd_member_balance_change（`card_type_id = 2794699703437125`） |
| GIFT_CARD_COUPON | 活动抵用券抵扣 | dwd_member_balance_change（`card_type_id = 2793266846533445`） |
| BIG_CUSTOMER | 大客户优惠 | dwd_settlement_head（big_customer_amount，从 adjust_amount 拆分） |
| OTHER | 其他优惠 | adjust_amount - big_customer_amount（其他无法归类的手动调整） |
| ROUNDING | 抹零 | dwd_settlement_head.rounding_amount |

## 数据来源

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

```sql
-- 从结账头表提取优惠汇总
SELECT 
    pay_time::DATE AS stat_date,
    COALESCE(SUM(coupon_amount), 0) AS coupon_amount_total,
    COALESCE(SUM(pl_coupon_sale_amount), 0) AS pl_coupon_sale_total,
    COUNT(CASE WHEN coupon_amount > 0 THEN 1 END) AS coupon_order_count,
    COALESCE(SUM(adjust_amount), 0) AS adjust_amount_total,
    COUNT(CASE WHEN adjust_amount != 0 THEN 1 END) AS adjust_order_count,
    COALESCE(SUM(member_discount_amount), 0) AS member_discount_total,
    COUNT(CASE WHEN member_discount_amount > 0 THEN 1 END) AS member_discount_order_count,
    COALESCE(SUM(rounding_amount), 0) AS rounding_amount_total,
    COUNT(CASE WHEN rounding_amount != 0 THEN 1 END) AS rounding_order_count
FROM dwd.dwd_settlement_head
WHERE site_id = :site_id
  AND settle_type IN (1, 3)
GROUP BY pay_time::DATE;
```

```sql
-- 赠送卡消费（按卡类型拆分）
SELECT
    change_time::DATE AS stat_date,
    card_type_id,
    COUNT(*) AS consume_count,
    SUM(ABS(change_amount)) AS consume_amount
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, card_type_id;
```

## 使用说明

**占比计算**
```sql
discount_ratio = discount_amount / SUM(discount_amount) OVER (PARTITION BY stat_date)
```

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯 |
| 数据范围 | 2025-07-16 ~ 至今 |
| 依赖表 | dwd_settlement_head, dwd_groupbuy_redemption, dwd_member_balance_change |
