罗罗编程学习记录
本页内容 展开查看

05.10.1 维度建模

所属:全栈开发面试题库 / 05 数据库与存储 / 05.10 数仓建模与 ETL

覆盖范围

  • 星型模型与雪花模型
  • 事实表类型:事务、周期快照、累积快照
  • 维度表与缓慢变化维 SCD
  • 一致性维度与总线矩阵
  • 范式建模 vs 维度建模

高频题速览

编号 题目 难度 频率
05.10.1-001 星型模型和雪花模型区别 基础 ★★★★★
05.10.1-002 事实表有哪几种类型 进阶 ★★★★
05.10.1-003 缓慢变化维 SCD 怎么处理 进阶 ★★★★★
05.10.1-004 一致性维度是什么 进阶 ★★★
05.10.1-005 范式建模和维度建模区别 基础 ★★★

05.10.1-001 星型模型和雪花模型有什么区别?

频率 ★★★★★ | 难度 基础

一句话结论

星型模型维度表反范式化(一张大宽维度表),事实表直接关联维度,JOIN 少、查询快;雪花模型维度表进一步范式化拆分,存储省但 JOIN 多;生产数仓优先星型。

常见误区

  • 误区:雪花更规范更好。正解:OLAP 查询星型 JOIN 少性能好,雪花是 OLTP 思维。

05.10.1-002 事实表有哪几种类型?

频率 ★★★★ | 难度 进阶

一句话结论

事务事实表(一笔业务一行,如订单)、周期快照事实表(按天/月对状态拍照,如每日库存)、累积快照事实表(业务全生命周期一行,如订单从下单到签收的各时间戳)。

常见误区

  • 误区:所有事实表都存明细。正解:周期快照和累积快照是不同用途。

05.10.1-003 缓慢变化维 SCD 怎么处理?

频率 ★★★★★ | 难度 进阶

一句话结论

SCD 处理维度属性随时间变化:SCD1 直接覆盖(不保留历史)、SCD2 新增一行并加生效/失效时间与版本号(保留完整历史)、SCD3 保留当前和上一个值;生产一般 SCD2。

代码 / 示例

-- SCD2:客户维度
customer_id  name   city    valid_from    valid_to    is_current
100         张三   北京   2023-01-01   2024-03-01  0
100         张三   上海   2024-03-01   9999-12-31  1

常见误区

  • 误区:SCD2 用 update 改旧行。正解:新增一行,旧行 valid_to 设为失效时间。
  • 误区:事实表关联维度用自然键。正解:用代理键(surrogate key)关联,SCD2 多版本时才能正确匹配当时的维度属性。

05.10.1-004 一致性维度是什么?

频率 ★★★ | 难度 进阶

一句话结论

一致性维度是跨业务过程复用的统一维度(如日期、客户、商品),保证不同报表按同一维度口径汇总;总线矩阵(Bus Matrix)定义一致性维度与各事实表的关系。


05.10.1-005 范式建模和维度建模有什么区别?

频率 ★★★ | 难度 基础

一句话结论

范式建模(3NF)面向数据集成,减少冗余、灵活,但查询需多表 JOIN;维度建模面向分析,星型/雪花,预聚合,查询快;数仓 ODS 贴源,DWD/DWS 维度建模。


05.10.1-006 事实表和维度表怎么区分?

频率 ★★★★ | 难度 基础

一句话结论

事实表存业务度量(数量、金额、次数),外键关联维度表,粒度细;维度表存描述性属性(客户名、商品类目、地区),相对宽;事实表通常大且长,维度表小且宽。

概念解释

  • 事实表:一行一条业务事件,外键多,数值列多。
  • 维度表:一行一个实体,描述列多。

常见误区

  • 误区:事实表就是明细表。正解:周期快照和累积快照也是事实表,只是粒度不同。

05.10.1-007 代理键有什么用?

频率 ★★★ | 难度 进阶

一句话结论

代理键是无业务含义的自增 ID(如 dim_user_id),替代自然键关联事实表;SCD2 多版本时,代理键能让事实记录精确匹配当时的维度版本。

常见误区

  • 误区:用业务主键关联就行。正解:SCD2 后自然键对应多行,必须用代理键匹配历史版本。

05.10.1-008 退化维度是什么?

频率 ★★★ | 难度 进阶

一句话结论

退化维度是没有维度表、直接放在事实表中的维度键(如订单号),用于分组和下钻,不需要单独建维度表。


05.10.1-009 多值维度怎么处理?

频率 ★★ | 难度 高级

一句话结论

一个事实对应多个维度值(如一个商品多个标签),用桥接表(bridge table)或维度表拆行,查询时聚合;不要在事实表存逗号分隔字符串。


05.10.1-010 维度建模为什么反范式化?

频率 ★★★ | 难度 基础

一句话结论

OLAP 查询频率高、并发低,反范式化减少 JOIN,用空间换时间;3NF 范式化是 OLTP 思维,不适合分析场景。

典型场景与代码示例

星型模型示例

事实表:fact_order(订单事实表)
  order_id, user_id, product_id, dept_id, amount, pay_time

维度表:dim_user
  user_id, user_name, city, age

维度表:dim_product
  product_id, product_name, category, price

查询:
SELECT u.city, p.category, SUM(f.amount)
FROM fact_order f
JOIN dim_user u ON f.user_id = u.user_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY u.city, p.category;

SCD2 示例

dim_user 表:
surrogate_key | user_id | city | valid_from | valid_to | is_current
1             | 100     | 北京 | 2023-01-01 | 2024-03-01 | 0
2             | 100     | 上海 | 2024-03-01 | 9999-12-31 | 1

事实表关联:
fact_order.user_key = 1  (下单时用户在北京)

事实表类型

事务事实表:一笔订单一行
  order_id, user_id, amount, pay_time

周期快照事实表:每日库存拍照
  dt, product_id, stock_count

累积快照事实表:订单全生命周期一行
  order_id, created_at, paid_at, shipped_at, delivered_at

面试高频追问

问:星型模型和雪花模型生产用哪个?

答:生产数仓优先星型,反范式化减少 JOIN;雪花是 OLTP 思维,OLAP 不适合。

问:SCD2 为什么用代理键?

答:SCD2 后自然键对应多行,事实表用代理键才能精确匹配当时的维度版本。

问:事实表和维度表怎么区分?

答:事实表存业务度量(数量、金额),外键多;维度表存描述性属性(客户名、类目),相对宽。

问:维度建模为什么反范式化?

答:OLAP 查询频率高、并发低,反范式化减少 JOIN,用空间换时间。

核心要点速记

模型 特点 适用
星型 维度反范式、JOIN 少 生产数仓
雪花 维度范式化、JOIN 多 不推荐
SCD 处理 适用
SCD1 直接覆盖 不保留历史
SCD2 新增行+版本 保留历史(常用)
SCD3 保留当前+上一值 简单对比

一句话记忆:生产用星型+SCD2;代理键匹配历史版本。

相关概念辨析

星型 vs 雪花模型

维度 星型模型 雪花模型
维度表 一张 多张拆分
JOIN 少 多
查询性能 快 慢
空间 大 小
适合 查询为主 空间敏感

事实表 vs 维度表

  • 事实表:业务度量,数字,可加。
  • 维度表:业务描述,文字,不可加。
  • 选择:业务过程建模用事实表;描述性属性用维度表。

05.10.1-009 SCD2 怎么实现?

频率 ★★★★ | 难度 进阶

一句话结论

SCD2(缓慢变化维类型2)保留历史,每行加 effective_from/effective_to/is_current;更新时关闭旧行(is_current=0),插入新行(is_current=1)。

概念解释

  • SCD1:覆盖历史,不保留变化。
  • SCD2:保留历史,加有效时间范围。
  • SCD3:只保留前值和当前值。

原理流程

  1. 用户地址变化:旧行设置 effective_to='2026-01-01', is_current=0。
  2. 插入新行:effective_from='2026-01-01', is_current=1。
  3. 查询历史:WHERE effective_from <= date AND effective_to > date。

代码 / 示例

-- 用户维度表 SCD2
user_sk | user_id | address | effective_from | effective_to | is_current
1       | 1001    | 北京     | 2025-01-01     | 2026-01-01   | 0
2       | 1001    | 上海     | 2026-01-01     | 9999-12-31   | 1

常见误区

  • 误区:SCD2 数据量不大。正解:用户频繁变化时,SCD2 行数膨胀。

追问延伸

  • 代理键:user_sk 是自增代理键,不是 user_id。
  • 拉链表:SCD2 也叫拉链表。

05.10.1-010 事实表粒度怎么确定?

频率 ★★★ | 难度 进阶

一句话结论

事实表粒度 = 业务过程的一次事件;粒度越细越好(原子事实表),上层汇总由 DWS 层完成。

概念解释

  • 粒度:事实表一行代表什么业务事件。
  • 原子粒度:一行 = 一次下单/支付/发货。
  • 汇总粒度:一行 = 一天一个部门的汇总。

原理流程

  1. 业务过程:用户下单。
  2. 原子事实表:一行 = 一个订单的一个商品行。
  3. DWS 汇总:按天+部门汇总订单数和金额。

常见误区

  • 误区:事实表直接做汇总。正解:DWD 明细要原子,DWS 才汇总。

追问延伸

  • 粒度选择:原子事实表最灵活,上层可任意汇总。
  • 过早汇总:DWD 做汇总会丢失细节,无法重新分析。

05.10.1-011 一致性维度怎么构建?

频率 ★★ | 难度 高级

一句话结论

一致性维度:多个事实表共享同一维度表(如日期、用户、商品),避免重复定义和口径不一致;维度表在 DWD 层统一构建。

概念解释

  • 一致性维度:全公司统一的维度表,所有事实表 JOIN 同一张。
  • 维度一致性:同一用户在订单事实表和浏览事实表中属性一致。
  • 构建:DWD 层统一处理维度,下游事实表引用。

原理流程

  1. DWD 层构建用户维度表:user_dim。
  2. 订单事实表 JOIN user_dim。
  3. 浏览事实表也 JOIN 同一个 user_dim。

常见误区

  • 误区:每个事实表自己处理维度。正解:维度要统一,否则用户属性不一致。

追问延伸

  • 维度一致性:日期维度全公司统一。
  • 一致性维度是数据仓库的核心原则。

待整理清单

  • [ ] 总线矩阵设计
  • [ ] 桥接表与多值维度
  • [ ] 大数据下维度建模的实践(宽表代替雪花)