维度建模

本章目标：能把一个业务过程拆成事实表和维度表，定对粒度，画出星型模型，并用 SQL 建出订单、明细、支付三张事实表。 前置知识：数据仓库入门、SQL 核心语法 预计用时：100 分钟

维度建模是数仓最常用的建模方法。它不追求把数据拆到最规范，而是追求业务方看得懂、查询 JOIN 少、指标不容易算错。

1. 为什么不能直接照着源表建模

源表是给业务系统用的，不是给分析用的。orders 和 order_items 分两张表，products 里有品类，sellers 在另一张表。业务方要问“health_beauty 品类每个月的 GMV”，得自己 JOIN 四张表，还要记得排除取消订单。

维度建模把这些固定的关联和口径提前组织好，让分析变成简单的“事实表按维度分组、对度量求和”。

2. 事实表与维度表

2.1 两组关键词

事实表存“发生了什么”，维度表存“从什么角度看”。判断口诀：能加总的数字放事实表，用来分组和筛选的文字放维度表。

2.2 事实表的三种度量

• 可加度量：可以按任意维度加总，例如 item_amount、quantity。
• 半可加度量：某些维度能加，某些不能。例如账户余额可以按用户加，但不能把 12 个月的余额直接相加。
• 不可加度量：加总没有业务意义，例如单价、转化率。单价应按“金额 / 数量”重新计算，不能直接 AVG(price)。

客单价、复购率、留存率都是不可加指标，必须从分子分母重新算，不能对现成比率求平均。

2.3 事实表的外键

事实表通过外键关联维度表：

fact_order_item
  order_id      → 退化维度，直接留在事实表
  date_key      → dim_date
  customer_sk   → dim_customer
  product_sk    → dim_product
  seller_sk     → dim_seller

外键建议使用数仓生成的代理键（Surrogate Key），例如 customer_sk，而不是业务系统的 customer_id。代理键是整数、稳定、不与业务耦合；业务键（自然键）可能被复用或修改，不适合做长期关联。

维度表还应准备一行“未知成员”，代理键为 -1，当事实表找不到对应维度时使用，避免 JOIN 丢数据。

3. 粒度：建模里最重要的一步

3.1 声明粒度

粒度回答一个问题：事实表的一行代表什么？

粒度必须在建表前写进设计文档，不能等 SQL 写完再猜。

3.2 粒度定错会怎样

假设订单 O500000 买了 3 件商品，有一笔支付 1,000 元。把明细和支付直接按 order_id JOIN：

明细 3 行 × 支付 1 行 = 3 行
每行都带着 pay_amount = 1000
SUM(pay_amount) = 3000

支付金额被放大了 3 倍。如果一笔订单有多条支付记录，明细和支付还会形成笛卡尔积，放大倍数更不可控。这叫 fan-out（数据膨胀），是报表数字对不上的第一大原因。

注意：判断能不能 JOIN，先看两张表的粒度。粒度不同，不能直接相加或相除，必须先聚合到同一粒度。

4. 星型、雪花与星座模型

4.1 星型模型

              ┌──────────────┐
              │  dim_product │
              └──────┬───────┘
                     │
┌──────────────┐   ┌─┴───────────────┐   ┌──────────────┐
│  dim_customer├───┤ fact_order_item ├───┤  dim_seller  │
└──────────────┘   └─┬───────────────┘   └──────────────┘
                     │
              ┌──────┴───────┐
              │   dim_date   │
              └──────────────┘

事实表在中间，维度表在四周，像星星。维度表只和事实表关联，维度之间不互相 JOIN。数仓默认选星型，因为 JOIN 层数少、查询快、业务方容易理解。

4.2 雪花模型

雪花模型把维度表继续规范化。例如 dim_product 只存品类 ID，再关联一张 dim_category 拿品类名称。

fact_order_item → dim_product → dim_category

规范化更彻底，节省一点存储，但每多一层 JOIN 就多一次 shuffle 和性能开销。数仓通常不这么做，除非品类属性很多、需要单独维护。

4.3 星座模型

多个事实表共享同一批维度表，叫星座模型（Fact Constellation）：

        dim_date    dim_customer    dim_product
            │            │              │
   ┌────────┴────────────┴──────────────┴────────┐
   │  fact_order_item   fact_order   fact_payment │
   └──────────────────────────────────────────────┘

项目的订单、支付、登录三个业务过程共用日期和用户维度，就是星座模型。共享的维度必须保证同名、同义、同口径，这叫一致性维度。

4.4 三种模型对比

5. 总线矩阵、一致性维度与特殊维度

5.1 总线矩阵

总线矩阵用行表示业务过程，用列表示维度，标记哪些过程会用到哪些维度：

它的作用是在建模前统一全公司的维度，避免订单事实用 dim_user、支付事实用 dim_customer，最后两个用户数对不上。

5.2 一致性维度

一致性维度指多个事实表共用同一张维度表，或者至少共用同一套键、同一套属性定义。

项目的 dim_customer 同时被下单事实和支付事实使用。只要 customer_sk 一致，“下单用户数”和“支付用户数”才能放在同一张报表里比较。

一致性维度不等于“表名一样”。如果两套系统各自维护一份用户表，城市字段一个用“深圳”、一个用“深圳市”，那仍然不是一致性维度。

5.3 退化维度

退化维度是留在事实表里、没有单独维度表的维度，通常是订单号、明细序号这类高基数、无描述属性的字段。

fact_order_item(order_id, item_seq, ...)

order_id 用来定位订单、去重、追溯，但不需要建一张只有订单号的维度表。把它放在事实表里就是退化维度。

5.4 杂项维度

杂项维度（Junk Dimension）把多个低基数、互不相关的标志位组合成一张小维度表。例如订单状态、是否取消、是否送达：

dim_order_flag
  order_flag_sk | order_status | is_canceled | is_delivered
  1             | delivered    | 0           | 1
  2             | shipped      | 0           | 0
  3             | canceled     | 1           | 0

好处是事实表只存一个 order_flag_sk，不用存一堆文本标志；查询时 JOIN 一次即可。低基数标志位才适合，不要把所有订单属性都塞进去。

6. 订单、明细、支付三个场景

6.1 明细事实表：fact_order_item

• 粒度：一个订单里的一件商品，即 order_id + item_seq。
• 维度：日期、用户、商品、商家、订单状态。
• 度量：单价、数量、运费、商品金额、含运费金额。
• 退化维度：order_id、item_seq。

freight 是明细行上的运费，直接 SUM(freight) 就是总运费。不要再从订单表加一遍，否则重复。

6.2 订单事实表：fact_order

• 粒度：一个订单。
• 维度：日期、用户、订单状态。
• 度量：商品金额、含运费金额。
• 退化维度：order_id。

订单事实由明细汇总而来。它可以回答“多少订单、客单价多少”，但不能回答“哪个品类卖得好”，因为订单粒度里没有商品维度。

6.3 支付事实表：fact_payment

• 粒度：一次支付，即 order_id + pay_seq。
• 维度：支付日期、用户、支付方式。
• 度量：支付金额、分期数。
• 退化维度：order_id、pay_seq。

支付金额和订单金额不是同一个概念。支付表包含取消订单的支付记录，也可能存在部分支付、多次支付。不要默认“支付金额 = GMV”。

6.4 三张表怎么选

注意：下单用户数和支付用户数可能不同，这是业务事实，不是数据错误。做报表时必须写清楚口径。

7. 建表 SQL

7.1 维度表

DuckDB / 通用 SQL 风格：

CREATE TABLE dim_date (
    date_key     INTEGER PRIMARY KEY,
    full_date    DATE,
    year         INTEGER,
    month        INTEGER,
    day          INTEGER,
    weekday      INTEGER,
    is_weekend   INTEGER
);

CREATE TABLE dim_customer (
    customer_sk       BIGINT PRIMARY KEY,
    customer_id       STRING,
    customer_unique_id STRING,
    city              STRING,
    province          STRING,
    customer_level    STRING,
    start_date        DATE,
    end_date          DATE
);

CREATE TABLE dim_product (
    product_sk    BIGINT PRIMARY KEY,
    product_id    STRING,
    product_name  STRING,
    category      STRING,
    std_price     DECIMAL(12, 2),
    weight_g      INTEGER
);

CREATE TABLE dim_seller (
    seller_sk     BIGINT PRIMARY KEY,
    seller_id     STRING,
    seller_name   STRING,
    seller_city   STRING,
    seller_level  STRING
);

dim_customer 带 start_date / end_date，是为了保存会员等级变化历史，下一章会讲拉链表。当前项目只存最新等级，所以这两个字段暂时都填同一个有效期。

7.2 事实表

CREATE TABLE fact_order_item (
    order_id                 STRING,
    item_seq                 INTEGER,
    date_key                 INTEGER,
    customer_sk              BIGINT,
    product_sk               BIGINT,
    seller_sk                BIGINT,
    order_status             STRING,
    price                    DECIMAL(12, 2),
    quantity                 INTEGER,
    freight                  DECIMAL(12, 2),
    item_amount              DECIMAL(14, 2),
    item_amount_with_freight DECIMAL(14, 2)
);

CREATE TABLE fact_order (
    order_id       STRING PRIMARY KEY,
    date_key       INTEGER,
    customer_sk    BIGINT,
    order_status   STRING,
    goods_amount   DECIMAL(14, 2),
    order_amount   DECIMAL(14, 2)
);

CREATE TABLE fact_payment (
    order_id       STRING,
    pay_seq        INTEGER,
    date_key       INTEGER,
    customer_sk    BIGINT,
    pay_type       STRING,
    installments   INTEGER,
    pay_amount     DECIMAL(14, 2),
    PRIMARY KEY (order_id, pay_seq)
);

注意：Hive 传统表不支持 PRIMARY KEY 约束，主键唯一靠 ETL 去重和事后校验保证。Hive 建表要把 STRING 保留，DECIMAL(12,2) 可直接用，并在末尾加 PARTITIONED BY (dt STRING) STORED AS PARQUET。

7.3 从 DWD 装载事实表

INSERT INTO fact_order_item
SELECT
    d.order_id,
    d.item_seq,
    CAST(STRFTIME(d.order_date, '%Y%m%d') AS INTEGER) AS date_key,
    c.customer_sk,
    p.product_sk,
    s.seller_sk,
    d.order_status,
    d.price,
    d.quantity,
    d.freight,
    d.item_amount,
    d.item_amount_with_freight
FROM dwd_order_detail d
LEFT JOIN dim_customer c ON d.customer_id = c.customer_id
                       AND c.end_date = DATE '9999-12-31'
LEFT JOIN dim_product p ON d.product_id = p.product_id
LEFT JOIN dim_seller s ON d.seller_id = s.seller_id;

先 TRUNCATE 或按分区覆盖，才能保证重跑幂等。

注意：Hive 没有 STRFTIME，日期转 yyyyMMdd 用 date_format(order_date, 'yyyyMMdd') 再 CAST(... AS INT)；拉链维度关联条件也要按订单日期匹配，见 缓慢变化维。

8. 常见坑

1. 粒度没声明就开写：两张不同粒度的表直接 JOIN，金额翻倍。
2. 用自然键做长期关联：业务键变更后历史数据关联不上，应该用代理键。
3. 维度表没有未知成员：事实表 JOIN 不到维度时整行丢失，指标偏小。
4. 过度雪花化：每个属性都拆一张表，查询 JOIN 五六层。
5. 把不可加指标存成事实：比率字段直接加总或求平均，结果没有意义。
6. 三张事实表混着 JOIN：订单、明细、支付粒度不同，必须各自聚合到同一粒度再比较。
7. 忘记日期维度：只存 order_date 字符串，做不了财年、周末、节假日分析。

9. 在线练习

自测：

• 能说出订单表、订单明细表、支付表各是什么粒度，主键分别是什么。
• 能解释星型模型和雪花模型的取舍，并说明为什么品类常常直接退化进明细。
• 能举一个 fan-out 的例子，并写出“先聚合再 JOIN”的改法。
• 能解释复购为什么必须用稳定的用户标识，而不是每次下单生成的订单级客户 ID。

10. 面试延伸

面试题：为什么数仓优先选星型模型，而不是雪花模型？

是什么：星型模型是事实表加一层维度表，雪花模型是把维度表继续规范化拆成多层。

解决什么问题：星型 JOIN 少、查询快、业务方容易理解，适合以分析为主的数仓。雪花节省存储，但多一层 JOIN 就多一次 shuffle，查询复杂度也更高。维度的少量冗余在数仓里可以用空间换时间。

我的实操边界：项目里用的是星型模型，商品品类直接冗余在 dim_product。我理解雪花模型的规范化优势，但没有在生产环境做过两种模型的性能对比。

面试题：粒度定错会有什么后果？

是什么：粒度是事实表一行代表的业务含义。粒度错了，后续所有聚合都建立在错误的分母上。

解决什么问题：粒度不一致会导致 JOIN 后数据膨胀，金额和订单数成倍放大；也会导致比率指标的分母错误。比如把明细和支付直接按订单号关联，支付金额会按商品行数重复。

我的实操边界：我在项目里遇到过明细和订单混算导致 GMV 翻倍的问题，解决方法是先声明粒度，再让每张事实表只回答自己粒度内的问题，跨粒度先聚合再关联。

面试题：什么是一致性维度和总线矩阵？

是什么：一致性维度是多个事实表共用同一套维度定义；总线矩阵是业务过程和维度的对照表。

解决什么问题：没有一致性维度，订单报表的用户数和支付报表的用户数会对不上；总线矩阵在建模前统一维度，避免各做各的。企业级数仓通常先画总线矩阵，再逐个实现数据集市。

我的实操边界：项目里订单和支付共用 dim_customer 和 dim_date，我画过简化版总线矩阵；没有参与过公司级总线矩阵的制定。

11. 本章验收

• 能用自己的话区分事实表、维度表和度量
• 能对 8 张源表分别声明粒度
• 能解释明细与支付直接 JOIN 为什么会放大金额，并写出先聚合再关联的 SQL
• 能画出星型模型，并说出星型、雪花、星座各自适用场景
• 能解释退化维度和杂项维度，并各举一个例子
• 能独立写出 fact_order_item、fact_order、fact_payment 的建表 SQL
• 能回答“为什么选星型”“粒度定错会怎样”