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`

28-dim建模.md 13 KB تاريخچه خام