# DIM 建模

> 本数仓 DIM 层（维度层）的字段建模与设计约定。建模方法论（拉链表 SCD2 vs 全量快照选型、拉链表分区特殊性）见 `20-数仓分层与建模.md` §5.5 / §7.3；命名规则见 `21-命名规范.md` §3.3。
>
> 本文档按"实体一节"组织，每节包含实体范围 / 关键设计 / 字段表。

## 1. 通用约定

### 1.1 框架字段

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

### 1.2 选型策略

默认 `_ful_d`（每日全量快照）。`_zip_d`（SCD2 拉链）按需评估。

**ful_d → zip_d 触发条件**（满足任一即评估迁 zip）：

- 出现"看历史属性变化"的标签 / 报表需求
- 全量快照存储 > 100 GB 或保留期 > 3 年
- 业务侧明确要回放某历史 dt 的实体状态

**双轨共存**：迁 zip 时不替换 ful_d，新建 `_zip_d` 并存：

| 表 | 用途 |
|---|---|
| `_ful_d` | 保留近 7 天热快照，下游 join 单分区即可 |
| `_zip_d` | 全量历史 SCD2 拉链，属性变化分析 / 历史回放 |

### 1.3 跑批策略

ODS 跨 dt 不去重 → 同 pk 多分区并存 → DIM 取每个 pk 的最新有效行。

- **初始化**：扫 ODS 全量分区 + `ROW_NUMBER OVER (PARTITION BY pk ORDER BY update_time DESC)` 取每 pk 最新行落 DIM 首日分区
- **日常增量**：用「昨日 DIM + 今日 ODS 增量」按 pk 合并去重（同 pk 取较新 `update_time`），避免每日全量扫 ODS

### 1.4 脏数据清洗位置

源端字段值脏数据（如同一概念多种写法 `mlb` / `MLB`）的归一清洗放 **DIM 层**完成；DWD 维度退化时直引 DIM 已清洗字段，下游 DWS / TDM 不再清洗。

---

## 2. dim_usr_user_ful_d（用户维度）

### 2.1 实体

`app_base_user` 主表 LEFT JOIN `app_user_cert_info`（按 `user_id`），用户主信息 + 证件实名信息合一。

### 2.2 性别 / 生日来源

只取 `cert_info.cert_sex` / `cert_birthday`，**实名才有标签**，未实名用户对应字段为空。

不冗余 `app_base_user.sex` / `birthday`：业务库里几乎全空 + 双源会引出收敛规则的复杂度。下游标签生成时 `is_cert=true` 才输出实名相关标签，否则空值。

### 2.3 字段表

| 分组 | 字段 | 类型 | 来源 | 说明 |
|---|---|---|---|---|
| 标识 | user_id | BIGINT | base_user.id | PK |
| 标识 | appid | STRING | base_user | 所属程序 |
| 标识 | username | STRING | base_user | 账号 |
| 标识 | code | STRING | base_user | 会员码 |
| 实名 | sex_cert | TINYINT | cert_info.cert_sex | 证件性别 |
| 实名 | birthday_cert | STRING | cert_info.cert_birthday | 证件生日 |
| 实名 | cert_province | STRING | cert_info | 证件所在省 |
| 实名 | cert_city | STRING | cert_info | 证件所在市 |
| 实名 | is_cert | BOOLEAN | 派生 | cert_info 命中即 true |
| 实名 | id_card | STRING | base_user.id_card | 身份证号（已 md5）|
| 实名 | face_verify | TINYINT | base_user | 人脸识别通过标志 |
| 实名 | cancel_verify_num | BIGINT | base_user | 重置实名次数 |
| 注册 | register_channel | STRING | base_user | 注册渠道 |
| 注册 | register_addr | STRING | base_user | 注册省区 |
| 注册 | register_ip_addr | STRING | base_user | 注册 IP |
| 注册 | reg_create_time | TIMESTAMP | base_user.create_time | 注册时间 |
| 登录 | login_addr | STRING | base_user | 上次登陆省区 |
| 登录 | login_ip_addr | STRING | base_user | 上次登陆 IP |
| 会员 | level | BIGINT | base_user.level | 会员等级 |
| 会员 | member_level | BIGINT | base_user.member_level | 会员等级（业务库重复字段，先全保留待澄清）|
| 会员 | member_name | STRING | base_user | 会员等级名称 |
| 会员 | growth_num | BIGINT | base_user | 成长值 |
| 会员 | current_month_growth | BIGINT | base_user | 当月成长值 |
| 会员 | member_keep_growth | BIGINT | base_user | 保级所需成长值 |
| 会员 | member_init_flag | TINYINT | base_user | 月初初始化标志 |
| 会员 | point | BIGINT | base_user | 积分 |
| 业务库冗余 | consume_amount_cny | DECIMAL(20,4) | base_user.consume_amount | 业务库后端自带消费总额，口径未对齐数仓；保留作审计对账 |
| 业务库冗余 | order_total_num | BIGINT | base_user | 业务库后端自带订单总数，同上 |
| 状态 | status | TINYINT | base_user | 用户状态 |
| 状态 | blacklist | TINYINT | base_user | 黑名单标记 |
| 偏好 | refuse_pick_up | TINYINT | base_user | 是否拒绝自提 |
| 偏好 | notify_flag | TINYINT | base_user | 推送是否接受 |
| 偏好 | open_invoice | TINYINT | base_user | 开票权限 |
| 偏好 | open_psd | TINYINT | base_user | 支付开关 |
| 限额 | daily_limit | BIGINT | base_user | 每日限额提醒 |
| 限额 | weekly_limit | BIGINT | base_user | 每周限额提醒 |
| 限额 | monthly_limit | BIGINT | base_user | 每月限额 |
| 时间 | last_update_time | TIMESTAMP | base_user.update_time | 最近更新时间 |
| 软删 | is_deleted | BOOLEAN | base_user | 软删归一 |
| 框架 | etl_time | TIMESTAMP | 派生 | ETL 处理时间 |
| 分区 | dt | STRING | 控制 | T-1，`yyyyMMdd` |

---

## 3. dim_trd_card_group_ful_d（拼团维度）

### 3.1 实体

`card_group_info`（拼团信息），承载"商家上架的拼团活动"实体。订单 → 品类的归属通过本表 `sport` 字段取得。

### 3.2 sport 脏数据清洗

**业务依据**：拼团可上单个产品，也可组合多个产品（可能跨类）；以商家设置的拼团大类（`cgi.sport`）为准聚合最贴近业务侧分析口径，因此 `sport` 取自拼团表本身，不通过底层产品表（panini / checklist）反查。

**清洗规则**（DIM 内置，下游直引）：

| 原值 | 清洗后 |
|---|---|
| `mlb` | `MLB` |
| `Boxing` | `UFC` |
| `other` | NULL（聚合时 `WHERE sport IS NOT NULL` 过滤）|
| 其他 | 原值保留 |

### 3.3 字段表

| 分组 | 字段 | 类型 | 来源 | 说明 |
|---|---|---|---|---|
| 标识 | group_info_id | BIGINT | id | PK |
| 标识 | code | STRING | | 编码 |
| 标识 | appid | STRING | | 所属程序 |
| 标识 | name | STRING | | 拼团名称 |
| 标识 | display_name | STRING | | 系列别名（显示名）|
| 标识 | group_show_name | STRING | | 精美卡片名称 |
| 商家关联 | merchant_id | BIGINT | | 商家 id |
| 商家关联 | mer_name | STRING | | 商户名称（冗余）|
| 商品关联 | list_id | BIGINT | | 商家上线 checklist id |
| 商品关联 | list_code | STRING | | checklist code |
| 商品关联 | panini_list_id | BIGINT | | 帕尼尼 list id（业务库快照冗余）|
| 分类 | sport | STRING | | 运动类型（含 §3.2 脏数据清洗）|
| 分类 | year | STRING | | 年份（赛季）|
| 分类 | manufacturer | STRING | | 厂商 |
| 分类 | sets | STRING | | 系列 |
| 销售配置 | type | STRING | | 组团方式 |
| 销售配置 | sub_type | STRING | | 组队方式子方式 |
| 销售配置 | random_type | STRING | | 随机方式 |
| 销售配置 | specs | STRING | | 商品规格 |
| 销售配置 | total_price_cny | DECIMAL(20,4) | total_price | 商品总价 |
| 销售配置 | unit_price_cny | DECIMAL(20,4) | unit_price | 单份售价 |
| 销售配置 | act_price_cny | DECIMAL(20,4) | act_price | 私域分享优惠价 |
| 销售配置 | copies | BIGINT | | 商品份数 |
| 销售配置 | total_num | BIGINT | | 总数量 |
| 销售配置 | weight | BIGINT | | 重量 |
| 销售配置 | order_quota_min | BIGINT | | 每笔订单最少购买 |
| 销售配置 | order_quota_max | BIGINT | | 每笔订单最多购买 |
| 销售配置 | user_quota_max | BIGINT | | 用户最多购买 |
| 销售配置 | min_card_num | BIGINT | | 免运费达标数量 |
| 销售配置 | mix_copies | TINYINT | | 组合加倍 |
| 销售配置 | change_type | STRING | | 买对玩法改变后组队方式 |
| 销售状态 | status | BIGINT | | 状态 |
| 销售状态 | sold_copies | BIGINT | | 售出份数 |
| 销售状态 | real_sold_num | BIGINT | | 实际销售数量 |
| 销售状态 | sold_end_time | TIMESTAMP | | 售卖结束时间 |
| 销售状态 | finished_time | TIMESTAMP | | 结束时间 |
| 销售状态 | release_time | STRING | | 发布时间（源是字符串）|
| 销售状态 | start_time | TIMESTAMP | | 计划开售时间 |
| 销售状态 | cycle | STRING | | 销售周期 |
| 团生命周期 | group_full_time | TIMESTAMP | | 组齐时间 |
| 团生命周期 | review_hold_time | TIMESTAMP | | 组队提交审核时间 |
| 团生命周期 | review_approval_time | TIMESTAMP | | 组队审核通过时间 |
| 团生命周期 | review_num | BIGINT | | 审核驳回次数 |
| 团生命周期 | confirm_send_time | TIMESTAMP | | 确认发货日期 |
| 团生命周期 | close_payment_time | TIMESTAMP | | 打款日期 |
| 团生命周期 | close_payment_status | TINYINT | | 结算状态 |
| 团生命周期 | group_sets_no | BIGINT | | 同商家同系列序号 |
| 直播 | live_create_time | TIMESTAMP | | 直播创建时间 |
| 直播 | live_start_time | TIMESTAMP | | 直播开播时间 |
| 直播 | live_end_time | TIMESTAMP | | 直播结束时间 |
| 报告 | report_start_time | TIMESTAMP | | 报告开始时间 |
| 报告 | report_end_time | TIMESTAMP | | 报告结束时间 |
| 报告 | report_review_num | BIGINT | | 报告审核次数 |
| 报告 | report_review_first_time | TIMESTAMP | | 报告首次审核时间 |
| 报告 | report_review_end_time | TIMESTAMP | | 报告末次审核时间 |
| 营销 | act_id | BIGINT | | 关联活动 id |
| 营销 | act_type | STRING | | 活动标签 |
| 营销 | act_point_type | STRING | | 额外积分类型 |
| 营销 | free_flag | TINYINT | | 免单标记 |
| 营销 | exclusive | TINYINT | | 是否专属支付通道 |
| 营销 | has_bg | TINYINT | | 是否有背景图 |
| 营销 | team_first | TINYINT | | 包队优先玩法 |
| 营销 | use_member_discount | TINYINT | | 会员折扣参与方式 |
| 营销 | use_coupon | TINYINT | | 是否可用优惠券 |
| 营销 | user_level | TINYINT | | 用户可看等级 |
| 营销 | merchant_open | TINYINT | | 是否支持商家代开卡密 |
| 营销 | merchant_sort | BIGINT | | 商家自定义排序 |
| 营销 | custom | TINYINT | | 是否自定义 |
| 营销 | gift_card_id | BIGINT | | 关联精美卡片 id |
| 营销 | open_card | TINYINT | | 开卡动作 |
| 营销 | goods_type | TINYINT | | 卡密类型 |
| 积分 | point_rate | TINYINT | | 积分转换比例 |
| 积分 | point_max | BIGINT | | 积分兑换最大值 |
| 积分 | point_min | BIGINT | | 积分兑换最小值 |
| 积分 | point_type | STRING | | 兑换积分类型 |
| 营销支付 | payment_method | TINYINT | | 打款方式 |
| 营销支付 | payment_total_price_cny | DECIMAL(20,4) | payment_total_price | 打款总金额 |
| 营销支付 | payment_commission_cny | DECIMAL(20,4) | payment_commission | 佣金金额 |
| 营销支付 | payment_finished_price_cny | DECIMAL(20,4) | payment_finished_price | 已打款金额 |
| 营销支付 | payment_remain_price_cny | DECIMAL(20,4) | payment_remain_price | 剩余打款金额 |
| 营销支付 | payment_online_price_cny | DECIMAL(20,4) | payment_online_price | 线上打款金额 |
| 营销支付 | commission_rate | STRING | | 佣金比例（百分比）|
| 业务标记 | hot_type | STRING | | 查询 type |
| 业务标记 | report_flag | TINYINT | | 强制使用新版报告 |
| 业务标记 | banner_end_time | TIMESTAMP | | banner 结束时间 |
| 风控 | waring_type | STRING | | 风险异常类型 |
| 风控 | compensation_status | TINYINT | | 赔付状态 |
| 业务标记 | reviewmsg | STRING | | 审核描述 |
| 业务标记 | review_account | STRING | | 审核账号 |
| 业务标记 | lock | BOOLEAN | | 锁定状态 |
| 业务标记 | marketing_info | STRING | | 营销信息 |
| 业务标记 | msg | STRING | | 详情描述 |
| 业务标记 | title | STRING | | 商品子标题 |
| 业务标记 | remark | STRING | | 备注 |
| 时间 | create_time | TIMESTAMP | | 创建时间 |
| 时间 | last_update_time | TIMESTAMP | update_time | 最近更新时间 |
| 时间 | del_time | TIMESTAMP | | 删除时间 |
| 软删 | is_deleted | BOOLEAN | | 软删归一 |
| 框架 | etl_time | TIMESTAMP | 派生 | ETL 处理时间 |
| 分区 | dt | STRING | 控制 | T-1，`yyyyMMdd` |