# 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 标签时启用