poyee-data-warehouse/kb/33-tdm建模.md

TDM 建模

本数仓 TDM 层（主题域模型 / 画像层）的字段建模与设计约定。建模方法论见 20-数仓分层与建模.md §5；标签体系总览见 23-标签体系.md；命名规则见 21-命名规范.md；时间语义见 26-时间语义.md。

本文档按"表一节"组织，每节包含表用途 / 粒度 / 来源 / tag_code 清单 / 写入策略。

1. 通用约定

1.1 框架字段

所有 TDM 表必带 etl_time TIMESTAMP + 分区 dt STRING，STORED AS ORC。

1.2 EAV 长表 schema（kb/23 §2 严守 7 字段）

所有标签表共用 EAV 7 字段，不扩展：

字段	类型	说明
entity_id	BIGINT	实体 id（用户场景 = user_id）
tag_code	STRING	标签编码（维度全 encode 到此字段，命名约定见 §4）
tag_value	STRING	标签值（统一 STRING；数值标签如 "1500.00"，下游 CAST 处理）
tag_type	STRING	attr / stat / rule（预留枚举值 algo 给机器学习标签）
confidence	DECIMAL(5,4)	置信度（规则标签 1.0；模型标签按模型输出）
etl_time	TIMESTAMP	ETL 处理时间
dt	STRING	分区，按表语义取（见各表节）

收益：schema 不绑定标签维度组合；新增维度（如 channel / device 等）只需新 tag_code 命名，不需 ALTER。

代价：维度切片需 tag_code LIKE 'usr_pref_trade_%_amt_30d' 模式匹配；数值标签聚合 / 排序需 CAST(tag_value AS DECIMAL)。

1.3 表分类：_d 日更 / _o 一次性凝固

按更新周期拆表（业界主流 OneData / 字节 / 美团做法）：

后缀	写入策略	含哪些标签
_d	每天 INSERT OVERWRITE 单分区 dt=T-1	属性 snap + 偏好滚动窗口（30d）+ 偏好当年累计（y{当年}）
_o	一次性写入永远不动	偏好往年总额（y{往年}，跨年凝固时新建一张）

为什么分两类：

• y{往年} 一旦凝固永不变，与 _d 日更"每天重刷 T-1"语义冲突 — 混在 _d 表会让"永远不变"的标签被天天扫一遍重写
• 各自独立调度：_d 是日 DAG，_o 是手动 once（落 manual/backfill/，跑一次写完）

1.4 写入策略

表	dt 锚点	写入
_d	T-1（${dt}）	INSERT OVERWRITE TABLE ... PARTITION (dt='${dt}') 静态单分区
_o	凝固年 12-31（如 20251231）	INSERT OVERWRITE TABLE ... PARTITION (dt='20251231') 静态单分区

1.5 调度依赖

• _d sche：DS DEPENDENT 同 dt dim_usr_user_ful_d（属性源）+ 同 dt dws_usr_user_trade_1d（偏好源）
• _o：不挂日调度，手跑 once

---

2. tdm_usr_tag_d（用户标签 日更长表）

2.1 用途

承载 1 期用户标签的日更部分：7 个属性 + 16 品类 × 4 偏好窗口（30d 金额/次数 + y{当年} 金额/次数）。

2.2 粒度

每个 (entity_id, tag_code) 在 dt 分区内唯一。

2.3 来源

• 属性 7 个 ← dim_usr_user_ful_d WHERE dt='${dt}'
• 偏好 30d 金额/次数 ← dws_usr_user_trade_1d WHERE dt BETWEEN $[yyyyMMdd-30] AND ${dt} 滚动聚合
• 偏好 y{当年} 金额/次数 ← dws_usr_user_trade_1d WHERE dt BETWEEN '{当年}0101' AND ${dt} 滚动覆盖（1 期当年 = 2026）

2.4 tag_code 清单

属性类（tag_type=attr，7 个）

tag_code	描述	来源 / 算法	tag_value 格式
usr_level	用户等级	dim_usr_user.member_level	数字字符串（如 "3"）
usr_is_cert	实名认证情况	dim_usr_user.is_cert	"true" / "false"
usr_sex	性别	dim_usr_user.sex_cert（未实名 NULL）	"M" / "F"
usr_city	城市	dim_usr_user.cert_city（未实名 NULL）	城市名
usr_register_time	注册时间	dim_usr_user.reg_create_time	yyyyMMdd
usr_birth_month	生日年月	dim_usr_user.birthday_cert 派生（未实名 NULL）	yyyyMM
usr_generation	出生世代	dim_usr_user.birthday_cert 派生（按 §6 切片）	世代名（"90后" / "00后" 等）

偏好类（tag_type=stat，16 品类 × 4 窗口 = 64 个）

tag_code 模板	描述	来源
usr_pref_trade_{category}_amt_30d	某品类近 30 天金额	SUM(pay_amt_cny) WHERE dt IN [T-30, T-1]
usr_pref_trade_{category}_cnt_30d	某品类近 30 天次数	SUM(pay_order_cnt) 同口径
usr_pref_trade_{category}_amt_y2026	某品类 26 年累计金额	SUM(pay_amt_cny) WHERE dt IN [20260101, T-1]（每天滚动覆盖）
usr_pref_trade_{category}_cnt_y2026	某品类 26 年累计次数	SUM(pay_order_cnt) 同口径

{category} 是 16 品类占位（kb/28 §3.2 DIM 已清洗）：Basketball / Soccer / MLB / TCG / PTCG / 影视收藏 / 综合体育 / 综合收藏 / NFL / NHL / UFC / WWE / Tennis / Esports / F1 / Golf。

金额口径：SUM(pay_amt_cny) = Net Revenue 偏好金额（A3 锁 1 期不扣减退款；GMV = payable_amt_cny，偏好 amt 用 pay_amt_cny，见 kb/27 §2.5 / kb/29 §2.5）。

次数口径：SUM(pay_order_cnt) = COUNT(DISTINCT order_id)（A2 锁定，1 期不用份数）。

2.5 字段表

按 EAV 7 字段（同 §1.2），dt = ${dt} = T-1。

2.6 行数估算

• 属性：30 万用户 × 7 = 210 万行
• 偏好：30 万用户 × ~5 活跃品类 × 4 窗口 = 600 万行
• 合计 ~ 800 万行 / 分区，ORC 压缩后 < 200 MB

---

3. tdm_usr_tag_o（往年总额标签 一次性凝固）

3.1 用途

承载历年（往年）总额标签长表。1 期落地 25 年凝固（16 品类 × 金额 / 次数 = 32 个 tag_code），开发完一次性跑入永远不动；后续每年 1-1 凝固上一年累计新落 dt 分区，不分新表。

3.2 粒度

每个 (entity_id, tag_code) 在 dt 分区内唯一。每个 dt 分区对应一个凝固年（25 年 → dt='20251231'，26 年 → dt='20261231'，以此类推）。

3.3 来源

dws_usr_user_trade_1d WHERE dt BETWEEN '{凝固年}0101' AND '{凝固年}1231' 一次性聚合。1 期凝固 25 年用 [20250101, 20251231]。

3.4 tag_code 清单（tag_type=stat，每凝固年 32 个）

tag_code 模板	描述	来源
usr_pref_trade_{category}_amt_y{凝固年}	某品类该年总额	SUM(pay_amt_cny) WHERE dt IN [该年 01-01, 12-31]
usr_pref_trade_{category}_cnt_y{凝固年}	某品类该年总次数	SUM(pay_order_cnt) 同口径

{category} 同 §2.4 16 品类。1 期 {凝固年}=2025，落 usr_pref_trade_basketball_amt_y2025 等 32 个 tag_code。

3.5 dt 锚点 + 写入

• 按凝固年份 dt='{yyyy}1231'（25 年 = dt='20251231'，26 年 = dt='20261231'），永久固定
• 每个 dt 分区跑一次写入后永远不动；新年凝固落新 dt 分区，旧分区不动
• 落 manual/backfill/{yyyymmdd}_tdm_usr_tag_o_y{yyyy}.sql 一次性 SQL，不挂日调度
• 业界 Kimball 周期快照事实表标准做法：单表 + insert-only 时间分区（粒度是周期不是事务，多年数据 WHERE dt IN (...) 切片即可）

3.6 行数估算

30 万用户 × ~5 活跃品类 × 2 度量 ≈ 300 万行 / 凝固年，ORC 压缩后 < 100 MB / 分区。N 年累积 N × 300 万行单表 OK。

---

4. tag_code 命名约定

类型	模板	示例
属性	usr_{属性名}	usr_sex / usr_city / usr_generation
偏好	usr_pref_{主题}_{category}_{metric}_{window}	usr_pref_trade_basketball_amt_30d / usr_pref_trade_mlb_cnt_y2025

所有维度（品类 / 窗口 / 度量）全部 encode 到 tag_code。下游按维度切片用 tag_code LIKE 'usr_pref_trade_%_amt_30d' 等模式匹配。

{window} 取值约定：

• snap（当前快照，T-1 的属性）
• 30d（近 30 天滚动 [T-30, T-1]）
• y{yyyy}（按年累计 / 总额；当年 = _d 表滚动，往年 = _o 表凝固）

---

5. 跨年扩张规则

每年 1-1 凝固上一年累计为 tdm_usr_tag_o 新 dt 分区（不新建表）：

时点	动作
2027-01-01	tdm_usr_tag_o 新落 dt='20261231' 分区（凝固 26 年累计）；tdm_usr_tag_d 移除 y2026 偏好 tag_code + 新增 y2027 tag_code
2028-01-01	tdm_usr_tag_o 新落 dt='20271231' 分区；同理

_o 表数据永久保留。下游想看历年总额按 WHERE dt IN ('20251231', '20261231', ...) 或 dt='{凝固年}1231' 查 tdm_usr_tag_o 一张表。

---

6. 属性细节默认口径（待业务回头校准）

按 tdm.md 7 个属性，业务侧未给精确口径前先用合理默认（源字段已在 kb/28 §2 确认）；调字段不动 schema（EAV 收益）。

tag_code	默认口径	待业务确认
usr_level	member_level	业务库另有 level 字段，两字段语义区别待澄清；1 期取 member_level
usr_is_cert	is_cert 布尔 → "true" / "false"	是否区分"未实名 / 审核中 / 已实名"3 态？1 期 2 态
usr_sex	sex_cert（M / F），未实名 NULL	未实名是否回退取自填 sex？1 期 cert 一源
usr_city	cert_city，未实名 NULL	未实名是否走 login_addr / register_addr？1 期 cert_city 一源
usr_register_time	reg_create_time → DATE_FORMAT 'yyyyMMdd'	是否要月 / 周粒度？1 期日粒度
usr_birth_month	birthday_cert → DATE_FORMAT 'yyyyMM'，未实名 NULL	是否只取月（MM）？1 期 yyyyMM 留年信息
usr_generation	birthday_cert 派生 10 年切片：60 前 / 60 后 / 70 后 / 80 后 / 85 后 / 90 后 / 95 后 / 00 后 / 05 后 / 10 后	切片粒度（5 年 / 10 年）？切片名（中文"90后" / 英文"1990s"）？1 期中文 N 后

---

7. 不在 1 期 scope

• TDM 宽表 tdm_usr_profile_ful_d：1 期标签未完善（业界规范长表稳定后再做宽表，见 kb/23 §3.2），2 期再评估
• TDM 人群包 tdm_usr_crowd_ful_d：1 期不做圈选
• 商品标签 / 店铺标签：tdm.md mermaid 图的 C / D 分支，1 期 scope 外
• algo 类标签：tag_type 预留枚举值，1 期无实际数据；将来接 RFM / 用户生命周期预测等 ML 标签时启用