进阶维度建模

本章目标：能区分事务事实表、周期快照事实表和累积快照事实表，处理多值维度与迟到维度，并为多个业务过程画出总线矩阵。 前置知识：维度建模、缓慢变化维与拉链表 预计用时：120 分钟

基础维度建模解决“一次下单发生了什么”。真实数仓还要回答：月底库存是多少、订单从支付到签收用了几天、一个订单用了多张优惠券该怎么归因。这些问题对应不同的事实表类型。

1. 为什么一种事实表不够

同样是“订单”，三类问题需要的表结构完全不同：

如果全部塞进一张订单表，会出现字段大量为空、口径混乱、更新频繁的问题。事实表类型要跟业务问题的形态匹配，不是所有事实都建成一种。

2. 三类事实表

2.1 事务事实表

记录“发生了一次业务动作”，只追加、不更新。粒度通常是一次下单、一次支付、一次点击。特点是写入后基本不变、行数随业务量增长，适合做交易额、订单量、点击量等可加指标。

2.2 周期快照事实表

在固定周期（每天、每月）记录业务实体的状态。粒度是一个实体在一个周期末的一条状态。

每个周期给每个实体一行，行数 = 实体数 × 周期数。可以按周期加总，但不能跨周期直接加库存；适合库存、余额、账户数、会员数。

2.3 累积快照事实表

记录一个有明确起止点的业务流程，随着流程推进不断更新同一行。粒度是一个业务实体（例如一个订单）。

一行贯穿整个生命周期，里程碑字段随流程更新，例如支付时间、发货时间、签收时间；适合计算各阶段耗时、转化漏斗、履约 SLA。

2.4 三类对比

3. 动手：建出三类事实表

在项目根目录打开 DuckDB：

duckdb

3.1 准备源数据

CREATE OR REPLACE VIEW orders AS
SELECT * FROM read_csv_auto('data/sample/orders_demo.csv', header = true);

CREATE OR REPLACE VIEW order_items AS
SELECT * FROM read_csv_auto('data/sample/order_items_demo.csv', header = true);

3.2 事务事实表

CREATE OR REPLACE TABLE fact_order_item AS
SELECT
    i.order_id,
    CAST(o.order_date AS DATE) AS order_date,
    o.city,
    i.product_id,
    i.category,
    CAST(i.price AS DECIMAL(12, 2)) AS item_amount,
    CAST(i.freight AS DECIMAL(12, 2)) AS freight_amount
FROM order_items i
JOIN orders o USING (order_id)
WHERE o.status NOT IN ('canceled', 'unavailable');

SELECT COUNT(*) AS rows, SUM(item_amount) AS item_gmv
FROM fact_order_item;

fact_order_item 一行是一件商品，粒度和 order_items 一致。取消和不可用订单被排除，金额只算一次。

3.3 周期快照事实表

月末库存不是“发生了一次库存变化”，而是“月底那一刻库存是多少”。建一张快照表：

CREATE OR REPLACE TABLE fact_inventory_snapshot (
    snapshot_date DATE,
    product_id VARCHAR,
    stock_qty INTEGER
);

INSERT INTO fact_inventory_snapshot VALUES
    ('2024-01-31', 'P01', 120),
    ('2024-01-31', 'P02', 80),
    ('2024-02-29', 'P01', 90),
    ('2024-02-29', 'P02', 100);

SELECT
    product_id,
    snapshot_date,
    stock_qty,
    stock_qty - LAG(stock_qty) OVER (
        PARTITION BY product_id ORDER BY snapshot_date
    ) AS qty_delta
FROM fact_inventory_snapshot
ORDER BY product_id, snapshot_date;

qty_delta 是相对上一个快照的变化量。注意：库存快照可以按商品求平均、看趋势，但不能把两个月库存直接相加，相加没有业务意义。

3.4 累积快照事实表

累积快照一行对应一个订单，里程碑字段随流程更新：

CREATE OR REPLACE TABLE fact_order_fulfillment AS
SELECT * FROM (
    VALUES
        ('1001', DATE '2024-01-01', DATE '2024-01-01', DATE '2024-01-02', DATE '2024-01-04'),
        ('1003', DATE '2024-01-02', DATE '2024-01-02', DATE '2024-01-03', DATE '2024-01-05'),
        ('1006', DATE '2024-01-05', DATE '2024-01-05', DATE '2024-01-06', DATE '2024-01-08')
) AS t(order_id, order_date, paid_at, shipped_at, delivered_at);

SELECT
    order_id,
    shipped_at - paid_at AS ship_days,
    delivered_at - paid_at AS deliver_days
FROM fact_order_fulfillment
ORDER BY order_id;

订单 1001 的 ship_days = 1、deliver_days = 3。如果订单还没发货，shipped_at 为空，计算耗时时要用 WHERE shipped_at IS NOT NULL 过滤，否则结果里会出现空值。

注意：累积快照需要更新已有行。Hive 表不支持行级更新，常见做法是按天分区重写，或者用支持主键更新的 OLAP/湖仓表。

4. 多值维度与桥接表

一个订单可能使用多张优惠券。如果直接在事实表里加 coupon1、coupon2、coupon3，维度数量一多就不可维护；如果建一张 order_id → coupon 的明细表，又会在 JOIN 时把订单金额放大。

4.1 建桥接表

CREATE OR REPLACE TABLE bridge_order_coupon (
    order_id VARCHAR,
    coupon VARCHAR
);

INSERT INTO bridge_order_coupon VALUES
    ('1001', 'NEW10'),
    ('1001', 'FREESHIP'),
    ('1003', 'NEW10'),
    ('1004', 'FREESHIP'),
    ('1006', 'NEW10'),
    ('1006', 'VIP');

订单 1001 有 2 张券，直接和订单事实表 JOIN 会让 1001 的金额出现 2 行，SUM 后翻倍。这叫多值维度导致的 fan-out。

4.2 用分摊法避免重复计算

一种常见做法是把订单金额平均分摊到参与该订单的每张券上：

WITH valid_orders AS (
    SELECT order_id, amount
    FROM orders
    WHERE status NOT IN ('canceled', 'unavailable')
),
allocated AS (
    SELECT
        v.order_id,
        v.amount / COUNT(b.coupon) AS allocated_amount
    FROM valid_orders v
    JOIN bridge_order_coupon b ON b.order_id = v.order_id
    GROUP BY v.order_id, v.amount
)
SELECT
    b.coupon,
    COUNT(DISTINCT a.order_id) AS orders,
    SUM(a.allocated_amount) AS allocated_gmv
FROM allocated a
JOIN bridge_order_coupon b USING (order_id)
GROUP BY b.coupon
ORDER BY allocated_gmv DESC;

分摊后的总金额仍然等于有效订单总金额，不会因为多张券而重复计算。代价是“每张券贡献多少”变成了人为规则，业务方必须认可这个口径。

4.3 其他处理方式

• 只保留主券：在桥接表里加 is_primary，分析时只取主券。口径简单，但会丢掉次要券信息。
• 预聚合：先按 order_id 聚合，再按券归因。适合券数量较少的场景。
• 事实表冗余：把主要维度直接写在事实表里，桥接表只做辅助查询。查询快，但更新麻烦。

没有一种方式永远正确，关键是把口径写进指标字典，不要让每个报表各算一遍。

5. 一致性维度、总线矩阵与迟到维度

5.1 一致性维度

多个事实表共享的维度必须同名、同义、同口径。例如订单事实表和支付事实表都用 dim_date，日期维度的 date_key、节假日标记、财年定义必须完全一致。否则两个报表按“月份”汇总会得到不同结果。

5.2 总线矩阵

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

                日期  用户  商品  商家  优惠券
下单             ✓     ✓     ✓     ✓
支付             ✓     ✓                 ✓
发货             ✓           ✓     ✓
评价             ✓     ✓     ✓

矩阵的作用是提前发现“同一个维度在不同过程里定义不一致”的问题。建模评审时先看矩阵，再看每张表的粒度。

5.3 迟到维度

事实表先到、维度数据后到，叫迟到维度。例如订单先落库，商品品类第二天才补全。处理方式：

• 维度表先插入“未知成员”，代理键为 -1，事实表先关联未知成员。
• 维度到达后更新维度表，再重跑受影响分区的事实表。
• 查询时用 COALESCE(dim.category, '未知') 兜底，避免 JOIN 丢行。

注意：不要用 INNER JOIN 关联维度表。维度缺失时 INNER JOIN 会直接丢掉事实行，金额就少了。

6. 常见坑

6.1 把快照当流水、累积快照不更新

把每天的库存快照当成“库存变化流水”去 SUM，会把库存重复累加；快照只能看期末值、平均值和趋势。累积快照只在订单创建时插入一行、后续不更新，就退化成了事务表，算不出履约时长。

6.2 多值维度直接 JOIN

桥接表和事实表直接 JOIN 后 SUM 金额，是最常见的数据膨胀来源。先确认粒度，再决定分摊、主券还是预聚合。

6.3 维度不一致、事实表塞描述字段

订单表用 city 表示收货城市，支付表用 city 表示账单城市，同名不同义，汇总时看起来能 JOIN，实际口径已经错了。把商品名称、用户地址、品类描述全塞进事实表，表会越来越宽，更新一次维度要改所有历史分区；描述字段放维度表，事实表只留外键和度量。

7. 在线练习

8. 面试延伸

面试题：事务事实表、周期快照、累积快照怎么选？

是什么：按业务问题的形态选。一次动作选事务事实表，周期末状态选周期快照，有生命周期的流程选累积快照。

解决什么问题：避免把所有问题塞进一张表，导致字段空、更新频繁、口径混乱。

我的实操边界：本章用 DuckDB 建了三类表并跑通查询，未在生产调度中维护累积快照。

面试题：多值维度怎么建模？

是什么：用桥接表记录实体和多个维度值的对应关系，再按业务规则分摊或选主值。

解决什么问题：直接 JOIN 会 fan-out，金额和数量被放大。

我的实操边界：本章演示了平均分摊，真实业务可能按券面额、按优先级分摊，需要业务确认。

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

是什么：总线矩阵列出业务过程和维度的使用关系；一致性维度是多个事实表共享、定义完全一致的维度。

解决什么问题：让不同数据集市能对上数，避免同名维度不同口径。

我的实操边界：本章画了小型总线矩阵，未参与企业级建模评审。

面试题：迟到维度怎么处理？

是什么：事实先到、维度后到。先挂未知成员，维度到达后更新并重跑受影响分区。

解决什么问题：避免 INNER JOIN 丢事实行，或者维度字段长期为空。

我的实操边界：本章说明了处理思路，未实现自动化重跑。

9. 本章验收

• 能用自己的话区分事务事实表、周期快照事实表和累积快照事实表。
• 能独立建出 fact_inventory_snapshot 并算出相邻快照的库存变化。
• 能建出 fact_order_fulfillment 并计算支付到签收的天数。
• 能解释桥接表 JOIN 后金额翻倍的原因，并写出一种分摊方案。
• 能画出一个包含 4 个业务过程和 5 个维度的总线矩阵。
• 能说出迟到维度的两种处理方式及各自代价。