# 需求文档：ETL DWS 层扩展 — 小程序数据支撑

## 简介

本 Spec 覆盖 P2 任务中 T4–T11 的 ETL DWS 层扩展，为小程序提供三类核心数据支撑：
1. 助教订单流水四项统计（`dws_assistant_order_contribution`）
2. 会员消费汇总扩展（充值窗口 + 次均消费）
3. 定档折算惩罚检测与计算

同时包含新表的 RLS 视图创建、FDW 映射同步，以及影子跑数验证。

> SPI 消费力指数（T1–T3）已在独立 Spec `.kiro/specs/spi-spending-power-index/` 中完成，本文档不再重复。

## 术语表

- **AssistantOrderContributionTask**：助教订单流水统计 ETL 任务，粒度 `(site_id, assistant_id, stat_date)`
- **MemberConsumptionTask**：会员消费汇总 ETL 任务，粒度 `(site_id, member_id, stat_date)`
- **AssistantDailyTask**：助教日度业绩明细 ETL 任务，粒度 `(site_id, assistant_id, stat_date)`
- **dws_assistant_order_contribution**：助教订单流水四项统计结果表
- **dws_member_consumption_summary**：会员消费汇总表（已有，需扩展字段）
- **dws_assistant_daily_detail**：助教日度业绩明细表（已有，需扩展字段）
- **order_gross_revenue**：订单总流水 — 助教参与订单的全部流水（台费 + 酒水食品 + 助教服务费）
- **order_net_revenue**：订单净流水 — 订单总流水 - 该订单所有助教的服务分成总额
- **time_weighted_revenue**：时效贡献流水 — 按助教个人服务时长折算的订单金额贡献
- **time_weighted_net_revenue**：时效净贡献 — 时效贡献流水 - 该助教个人的服务分成
- **penalty_minutes**：定档折算惩罚分钟数 — 因违规被扣减的定档业绩时长
- **per_hour_contribution**：单人每小时贡献流水 — 台费/房费每小时实收单价 ÷ 本次基础课助教人数
- **RLS 视图**：行级安全视图，位于 ETL 库 `app` schema，按 `site_id` 隔离数据
- **FDW 映射**：外部数据包装器映射，将 ETL 库表映射到业务库 `fdw_etl` schema
- **settle_type**：结算类型，1=台桌结账，3=商城订单，5=充值订单
- **BaseDwsTask**：DWS 层任务基类，提供 delete-before-insert 幂等机制
- **delete-before-insert**：幂等更新策略，先按条件删除旧记录再批量插入新记录

## 需求

### 需求 1：助教订单流水统计表创建（T4）

**用户故事：** 作为 ETL 开发者，我需要创建助教订单流水四项统计表，以便存储每名助教每日的订单流水贡献数据。

#### 验收标准

1. THE 开发者 SHALL 创建 `dws.dws_assistant_order_contribution` 表，主键为 `(site_id, assistant_id, stat_date)`
2. THE dws_assistant_order_contribution 表 SHALL 包含四项统计字段：`order_gross_revenue`（订单总流水）、`order_net_revenue`（订单净流水）、`time_weighted_revenue`（时效贡献流水）、`time_weighted_net_revenue`（时效净贡献），精度为 `NUMERIC(14,2)`
3. THE dws_assistant_order_contribution 表 SHALL 包含辅助字段：`order_count`（参与订单数）、`total_service_seconds`（总服务时长秒数）、`assistant_nickname`（助教昵称）
4. THE dws_assistant_order_contribution 表 SHALL 包含元数据字段：`tenant_id`、`created_at`、`updated_at`
5. THE 开发者 SHALL 编写迁移脚本 `db/etl_feiqiu/migrations/<日期>__create_dws_assistant_order_contribution.sql`，在测试库 `test_etl_feiqiu` 中执行建表
6. WHEN DDL 在测试库执行成功后，THE 开发者 SHALL 运行 `gen_consolidated_ddl.py` 导出最新 DDL

### 需求 2：助教订单流水四项统计计算（T5）

**用户故事：** 作为产品经理，我需要助教订单流水四项统计（订单总流水/订单净流水/时效贡献流水/时效净贡献），以便评估助教个人能力。

#### 验收标准

1. THE AssistantOrderContributionTask SHALL 从 `dwd.dwd_settlement_head`、`dwd.dwd_table_fee_log`、`dwd.dwd_assistant_service_log` 提取订单、台费和助教服务数据
2. WHEN 计算 order_gross_revenue 时，THE AssistantOrderContributionTask SHALL 将助教参与订单的全部流水（台费 + 酒水食品 + 所有助教服务费）累加，每个参与助教获得相同的订单总流水值
3. WHEN 计算 order_net_revenue 时，THE AssistantOrderContributionTask SHALL 从订单总流水中减去该订单所有助教的服务分成总额，每个参与助教获得相同的订单净流水值
4. WHEN 计算 time_weighted_revenue 时，THE AssistantOrderContributionTask SHALL 按以下步骤折算个人贡献：
   - 确定每张台桌的有效计费时长：取 MAX(该台桌所有助教服务时长之和, 台桌使用时长)
   - 按助教在该台桌的服务时长占比分摊台费
   - 助教个人服务费直接计入
   - 酒水食品按助教个人服务总时长占所有助教服务总时长的比例均分
5. WHEN 计算 time_weighted_net_revenue 时，THE AssistantOrderContributionTask SHALL 从该助教的时效贡献流水中减去该助教个人的服务分成
6. WHEN 助教为超休/打赏课类型时，THE AssistantOrderContributionTask SHALL 将四项统计均设为该助教个人的服务流水和分成（不参与订单级分摊）
7. THE AssistantOrderContributionTask SHALL 以任务代码 `DWS_ASSISTANT_ORDER_CONTRIBUTION` 注册到 task_registry
8. THE AssistantOrderContributionTask SHALL 采用 delete-before-insert 策略按日期窗口幂等更新

### 需求 3：会员消费汇总扩展（T6）

**用户故事：** 作为产品经理，我需要客户 30/60/90 天充值次数和金额、次均消费，以便在客户看板中展示。

#### 验收标准

1. THE 开发者 SHALL 在 `dws.dws_member_consumption_summary` 表中新增以下字段：`recharge_count_30d`、`recharge_count_60d`、`recharge_count_90d`（充值次数，INTEGER）、`recharge_amount_30d`、`recharge_amount_60d`、`recharge_amount_90d`（充值金额，NUMERIC(14,2)）、`avg_ticket_amount`（次均消费额度，NUMERIC(14,2)）
2. THE 开发者 SHALL 编写 ALTER TABLE 迁移脚本在测试库 `test_etl_feiqiu` 中执行字段扩展
3. THE 充值数据 SHALL 从 `dwd.dwd_recharge_order` 提取，按 `member_id` 和时间窗口聚合
4. THE avg_ticket_amount SHALL 按公式 `total_consume_amount / MAX(total_visit_count, 1)` 计算

### 需求 4：会员消费汇总任务修改（T7）

**用户故事：** 作为 ETL 开发者，我需要修改 MemberConsumptionTask 以填充新增的充值窗口和次均消费字段。

#### 验收标准

1. THE MemberConsumptionTask SHALL 在 extract 阶段新增充值订单提取逻辑，从 `dwd.dwd_recharge_order` 按 30/60/90 天窗口聚合充值次数和金额
2. THE MemberConsumptionTask SHALL 在 transform 阶段将充值统计和次均消费填充到输出记录中
3. WHEN 会员无充值记录时，THE MemberConsumptionTask SHALL 将充值次数设为 0、充值金额设为 0.00
4. WHEN 会员无消费记录时，THE MemberConsumptionTask SHALL 将 avg_ticket_amount 设为 0.00

### 需求 5：助教日度明细表扩展 — 定档折算惩罚字段（T8）

**用户故事：** 作为 ETL 开发者，我需要在助教日度明细表中新增定档折算惩罚相关字段，以便存储惩罚检测和计算结果。

#### 验收标准

1. THE 开发者 SHALL 在 `dws.dws_assistant_daily_detail` 表中新增以下字段：`penalty_minutes` (NUMERIC(10,2))、`penalty_reason` (TEXT)、`is_exempt` (BOOLEAN DEFAULT FALSE)、`per_hour_contribution` (NUMERIC(14,2))
2. THE 开发者 SHALL 编写 ALTER TABLE 迁移脚本在测试库 `test_etl_feiqiu` 中执行字段扩展
3. WHEN 助教当日无惩罚时，THE penalty_minutes SHALL 为 0，penalty_reason SHALL 为 NULL

### 需求 6：定档折算惩罚检测与计算逻辑（T9）

**用户故事：** 作为产品经理，我需要定档折算惩罚数据，以便在绩效页面展示折算详情，防止助教利用低价订单冲档位。

#### 验收标准

1. THE AssistantDailyTask SHALL 检测规则 2 违规：在指定区域（大厅 A/B/C/S/TV 和麻将房 M1–M7）内，同一台桌同一时间段超过 2 名助教挂台（课程时间段有重叠即算）
2. WHEN 检测到违规时，THE AssistantDailyTask SHALL 计算单人每小时贡献流水：台费/房费每小时实收单价 ÷ 本次基础课助教人数
3. WHEN per_hour_contribution >= 24 元时，THE AssistantDailyTask SHALL 按满额计入定档业绩时长（penalty_minutes = 0）
4. WHEN per_hour_contribution < 24 元时，THE AssistantDailyTask SHALL 按比例折算：`penalty_minutes = 实际服务分钟数 × (1 - per_hour_contribution / 24)`
5. WHEN 订单标记为 is_exempt = TRUE 时，THE AssistantDailyTask SHALL 跳过惩罚计算，penalty_minutes 设为 0
6. THE 定档折算惩罚 SHALL 仅影响定档业绩时长统计，不影响实际工资时长
7. THE AssistantDailyTask SHALL 每日自动计算惩罚，计算频率与现有日度任务一致

### 需求 7：RLS 视图创建（T10）

**用户故事：** 作为 ETL 开发者，我需要为新表创建 RLS 视图，以便通过 FDW 安全地向业务库暴露数据。

#### 验收标准

1. THE 开发者 SHALL 在 ETL 库 `app` schema 中为 `dws_assistant_order_contribution` 创建 RLS 视图，按 `site_id` 过滤
2. THE 开发者 SHALL 更新已有 RLS 视图以包含 `dws_member_consumption_summary` 和 `dws_assistant_daily_detail` 的新增字段
3. THE RLS 视图 SHALL 使用 `current_setting('app.current_site_id')::INTEGER` 进行行级过滤

### 需求 8：FDW 映射同步（T10）

**用户故事：** 作为后端开发者，我需要在业务库中通过 FDW 访问新建和扩展的 ETL 表，以便小程序后端读取数据。

#### 验收标准

1. THE 开发者 SHALL 在 `test_zqyy_app.fdw_etl` schema 中创建 `dws_assistant_order_contribution` 的外部表映射
2. THE 开发者 SHALL 更新 `dws_member_consumption_summary` 和 `dws_assistant_daily_detail` 的 FDW 映射以包含新增字段
3. THE FDW 映射 SHALL 通过 `app` schema 的 RLS 视图访问数据，而非直接访问 `dws` schema

### 需求 9：影子跑数验证（T11）

**用户故事：** 作为 ETL 开发者，我需要通过影子跑数验证新增统计的正确性，以便确保数据质量。

#### 验收标准

1. THE 开发者 SHALL 编写验证脚本，对照 PRD 示例数据验算四项统计的计算结果
2. THE 验证脚本 SHALL 检查 `dws_assistant_order_contribution` 中四项统计数值的一致性：order_gross_revenue 各助教相等、order_net_revenue 各助教相等、time_weighted_revenue 各助教之和加上误差容忍度等于订单总流水
3. THE 验证脚本 SHALL 检查 `dws_member_consumption_summary` 新增字段有值且充值金额与 `dwd_recharge_order` 源数据一致
4. THE 验证脚本 SHALL 检查定档折算惩罚字段在符合惩罚条件的记录上正确填充

### 需求 10：算法正确性测试

**用户故事：** 作为 ETL 开发者，我需要通过属性测试（hypothesis）验证四项统计和惩罚计算的正确性。

#### 验收标准

1. THE 属性测试 SHALL 验证：对于任意订单，所有参与助教的 order_gross_revenue 值相等
2. THE 属性测试 SHALL 验证：对于任意订单，所有参与助教的 order_net_revenue 值相等
3. THE 属性测试 SHALL 验证：对于任意订单，所有参与助教的 time_weighted_revenue 之和应在订单总流水的合理误差范围内（±0.01 元）
4. THE 属性测试 SHALL 验证：对于任意助教，time_weighted_net_revenue = time_weighted_revenue - 该助教个人服务分成
5. THE 属性测试 SHALL 验证：对于任意 per_hour_contribution >= 24 的记录，penalty_minutes 为 0
6. THE 属性测试 SHALL 验证：对于任意 per_hour_contribution < 24 且 per_hour_contribution >= 0 的记录，penalty_minutes = 实际分钟数 × (1 - per_hour_contribution / 24)
7. THE 属性测试 SHALL 验证：对于任意会员，avg_ticket_amount = total_consume_amount / MAX(total_visit_count, 1)