28-dim建模.md 13 KB
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 category 字段（命名 + 脏数据清洗）
字段命名：业务库原字段 card_group_info.sport → 数仓字段名 category（lv2 叶子品类）。按 kb/21 §1 一词一义原则，业务库 sport 实际已多义（16 个值里含"影视收藏 / 综合收藏 / 综合体育"等非 sport 语义），数仓字段改用中性的 category。lv1 大类（业务库 first_sport，1 期不引）未来引入时命名为 main_category。
业务依据（取自拼团粒度而非产品粒度）：拼团可上单个产品，也可组合多个产品（可能跨类）；以商家设置的拼团大类（cgi.sport）为准聚合最贴近业务侧分析口径，因此 category 取自拼团表本身，不通过底层产品表（panini / checklist）反查。
清洗规则（DIM 内置，下游直引 category）：
| 原值（cgi.sport） | 清洗后（category） |
|---|---|
mlb |
MLB |
Boxing |
UFC |
other |
NULL（聚合时 WHERE category 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（业务库快照冗余） | |
| 分类 | category | STRING | cgi.sport | 品类（lv2，含 §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 |