29-dws建模.md 6.2 KB
DWS 建模
本数仓 DWS 层（汇总层）的字段建模与设计约定。建模方法论（主题宽表 / 维度退化原则 / 业界 OneData 范式）见
20-数仓分层与建模.md§5；命名规则见21-命名规范.md§3.5；时间语义见26-时间语义.md。本文档按"主题宽表一节"组织，每节包含主题定义 / 粒度 / 来源 / dt 锚点 / 度量字段说明 / 字段表。
1. 通用约定
1.1 框架字段
所有 DWS 表必带 etl_time TIMESTAMP + 分区 dt STRING，STORED AS ORC。
1.2 单一职责：日聚合
DWS 只做 _1d 日聚合主题宽表，不爆窗口表（不建 _30d / _y{年份} 等多窗口物化）。跨周期窗口（30d / yYYYY 累计 / yYYYY 总额）由上层 TDM 跑批从 dws_1d 滚动聚合，TDM 按更新周期分 _d（日更）/ _o（一次性凝固）。
理由：
- DWS 职责单一保持稳定，避免跨周期物化的实体表爆炸
- 跨周期物化由 TDM 表的更新周期分类（d/o）天然解决
- 1 期数据量级 dws_1d 单年扫描 Spark 可接受
1.3 维度退化策略
业界主流（阿里 OneData）DWS 加强维度退化 + 宽表化以提升查询效率。本项目 1 期 scope 服务标签计算（仅用 user_id × category × dt），暂不冗余退化字段。后续触发条件评估扩展：
- 上层 BI 直接查 DWS 做透视分析（按非主键维度聚合，如 cert_city / merchant）
- 标签维度从 (user, category) 扩展到多维组合
- 跨域 join 频繁，DWS ← DIM 重复成本不可接受
补法：
- 简单 =
ALTER TABLE ADD COLUMN+ 重刷历史分区（DWS 聚合层重刷代价小） - 复杂 = 新建第二张主题宽表（业界 1-3 张 / 主题，如
dws_usr_user_demo_1d走人口学维度）
1.4 分区与写入
- 分区锚点：业务时间（与上游 DWD 对齐，如
DATE(payment_success_time)） - 写入策略：回算近 2 日（
dt IN (${dt}, ${pdt})），与 DWD N=2 对齐（DWD 漂移连锁补偿，参93-架构决策.mdADR-09） - 分区类型：动态分区
PARTITION (dt) - 重跑幂等：
INSERT OVERWRITE PARTITION (dt)项目默认 DYNAMIC mode（kb/26 §8）只覆盖 SELECT 出现的 dt，不动其他历史分区 - 调度依赖：DS DEPENDENT 同 dt DWD 跑完
2. dws_usr_user_trade_1d（用户 × 品类 × 日 交易主题宽表）
2.1 主题
用户 × 品类粒度的日交易聚合，承载偏好标签（金额 / 次数 / 多窗口）的聚合基础。
2.2 粒度
(user_id, category, dt) 在分区内唯一。category 取 dim_trd_card_group_ful_d.category（DIM 已清洗，叶子品类，权威源）。
2.3 来源
dwd_trd_order_pay_apd_d 单源（A3 锁定 1 期不做 refund，2 期接退款时再补 refund / net 度量）。
DWD 已完成维度退化（category 来自 dim_trd_card_group_ful_d，DIM 已归一脏数据），DWS 直引 DWD 的 category 字段，不再二次 join DIM。
2.4 dt 锚点
承袭 DWD：DATE(payment_success_time) 业务时间分区。
2.5 度量字段说明
按业界 DWS 宽表化原则保留多度量混存，下游 BI / TDM 不再写减法、不再 join 明细。
口径要点：
pay_amt_cny（Net Revenue）= 偏好标签金额口径。DWD 已内置mer_act%修正规则（mer_act% 时取point/100，否则取actual_payment），详见 kb/27 §2.5payable_amt_cny（GMV）= 业务通用 GMV 口径，与偏好标签解耦（业务侧 SQL 常用）pay_order_cnt = COUNT(DISTINCT order_id)= 偏好标签次数口径（A2 锁定，不用份数）purchase_cnt（份数）保留作 2 期备用 + 审计，1 期标签不用
砍掉的字段（违反"字段要全"原则但 1 期 scope 不扩张优先）：
card_price_cny/act_price_cny（单价 SUM 无业务意义）discount_amount_amt_cny（DWD 字段名未拍板，待业务侧澄清与merchant_discount语义区别）shipping_free_amt_cny（运费券 SUM 业务意义不明）discount_point/give_cnt（非偏好主流度量）
2.6 字段表
| 分组 | 字段 | 类型 | 来源 / 算法 | 说明 |
|---|---|---|---|---|
| 主键 | user_id | BIGINT | dwd | 用户 id |
| 主键 | category | STRING | dwd | 叶子品类（DIM 已清洗） |
| 度量-数量 | pay_order_cnt | BIGINT | COUNT(DISTINCT order_id) |
当日支付订单数（偏好次数口径） |
| 度量-数量 | purchase_cnt | BIGINT | SUM(purchase_cnt) |
当日支付份数（备用） |
| 度量-金额 | payable_amt_cny | DECIMAL(20,4) | SUM(payable_amt_cny) |
GMV |
| 度量-金额 | pay_amt_cny | DECIMAL(20,4) | SUM(pay_amt_cny) |
Net Revenue（偏好金额口径） |
| 度量-金额 | trade_amt_cny | DECIMAL(20,4) | SUM(trade_amt_cny) |
订单交易金额 |
| 度量-金额 | settle_amt_cny | DECIMAL(20,4) | SUM(settle_amt_cny) |
结算金额 |
| 度量-金额 | merchant_discount_amt_cny | DECIMAL(20,4) | SUM(merchant_discount_amt_cny) |
商家折扣 |
| 度量-金额 | platform_discount_amt_cny | DECIMAL(20,4) | SUM(platform_discount_amt_cny) |
平台券折扣 |
| 度量-金额 | member_discount_amt_cny | DECIMAL(20,4) | SUM(member_discount_amt_cny) |
会员折扣 |
| 度量-金额 | act_discount_amt_cny | DECIMAL(20,4) | SUM(act_discount_amt_cny) |
活动折扣 |
| 度量-金额 | point_deduct_amt_cny | DECIMAL(20,4) | SUM(point_deduct_amt_cny) |
积分抵扣金额 |
| 度量-金额 | shipping_amt_cny | DECIMAL(20,4) | SUM(shipping_amt_cny) |
运费 |
| 度量-积分 | point | BIGINT | SUM(point) |
当日消耗积分 |
| 框架 | etl_time | TIMESTAMP | 派生 | ETL 处理时间 |
| 分区 | dt | STRING | DATE(payment_success_time) |
业务时间分区 |
2.7 不带的字段说明
- 维度退化字段（用户域 sex_cert / cert_city / level，商家域 merchant_id / merchant_name 等）：1 期 scope 服务标签计算暂不冗余，触发条件见 §1.3
- lv1 大类
main_category（业务库原名first_sport）：业务侧 2026-05-08 拍板 1 期不引，2 期单独处理大类聚合时再补 - refund / net 度量：A3 锁定 1 期不做退款，2 期接 refund 时按业务诉求选择：
- 业界主流（OneData / 字节）：refund 度量聚到本表 + 加
net_amt_cny/net_order_cnt冗余列 - 替代方案：单独新建
dws_usr_user_refund_1d+ 上层 join
- 业界主流（OneData / 字节）：refund 度量聚到本表 + 加