【大数据_数仓架构-09_到底存储到数仓中的表有几种，分别给出实际使用场景，并举出一个实例；】

瀧爷1939

181人浏览 · 2026-06-11 11:15:44

瀧爷1939 · 2026-06-11 11:15:44 发布

● 数仓中的事实表与维度表全景

数仓里的表本质上分两大类：事实表（Fact Table）和维度表（Dimension Table）。
其中事实表又细分多种，维度表也有几种特殊形态。下面是生产中真正会用到的所有表类型。

一、事实表（4 种 + 1 种特殊）

事实表存储业务过程的度量，按"如何记录业务过程"分为以下几种。

事务事实表（Transaction Fact Table）⭐ 最常用

核心特征：

一行 = 一次业务事件发生
一旦写入不再修改（Insert Only）
数据按时间累积，无限增长

适用场景：

记录离散的、原子的业务事件
事件发生后状态不再变化
数仓中90%以上的事实表都是这种

典型应用：

下单、支付、退款、登录、点击、浏览、加购、收藏

实例：淘宝支付事实表

CREATE TABLE dwd_trade_order_pay_di (
order_id BIGINT,
user_id BIGINT,
item_id BIGINT,
pay_amount DECIMAL(16,2),
pay_method STRING,
pay_time TIMESTAMP,
– 维度退化
province_name STRING,
cate_name STRING
) PARTITIONED BY (dt STRING);

order_id | user_id | pay_amount | pay_time | dt
10001 | A | 99 | 2026-06-11 14:23:01 | 2026-06-11
10002 | B | 199 | 2026-06-11 14:25:33 | 2026-06-11
10003 | A | 299 | 2026-06-11 14:30:12 | 2026-06-11

特点：每笔支付一行，写入后永不修改。

周期快照事实表（Periodic Snapshot Fact Table）

核心特征：

一行 = 某个对象在某个时间点的状态快照
定期（每天/每周/每月）抽取一次
同一对象会在不同周期产生多行

适用场景：

关注状态随时间的变化趋势
需要回溯任意时间点的状态
业务系统数据频繁变化但需要历史快照

典型应用：

每日账户余额、每日库存、每日会员等级、每日股票持仓

实例：淘宝商家每日库存快照

CREATE TABLE dwd_item_inventory_snap_df (
item_id BIGINT,
sku_id BIGINT,
shop_id BIGINT,
warehouse_id BIGINT,
available_qty INT COMMENT ‘可售库存’,
locked_qty INT COMMENT ‘锁定库存’,
total_qty INT COMMENT ‘总库存’,
snapshot_time TIMESTAMP
) PARTITIONED BY (dt STRING);

dt | item_id | available_qty | locked_qty | total_qty
2026-06-09 | 88001 | 500 | 20 | 520
2026-06-10 | 88001 | 450 | 35 | 485 ← 库存变化
2026-06-11 | 88001 | 380 | 50 | 430 ← 库存变化

特点：每天给同一商品拍一张"快照"，可以看到任意时间点的库存。

累积快照事实表（Accumulating Snapshot Fact Table）⭐ 关键

核心特征：

一行 = 一个有明确生命周期的业务流程
行会被多次更新，每个生命阶段更新对应字段
一旦流程结束，这行就不再变化

适用场景：

跟踪有起止、有多个明确阶段的业务流程
关注流程各阶段间的时长
分析转化率、流程瓶颈

典型应用：

订单从下单到收货的完整链路
物流配送各节点
招聘流程、报销审批流程、客户投诉处理

实例：订单全生命周期累积快照

CREATE TABLE dwd_trade_order_lifecycle_di (
order_id BIGINT,
user_id BIGINT,
– 各节点时间
create_time TIMESTAMP,
pay_time TIMESTAMP,
ship_time TIMESTAMP,
delivery_time TIMESTAMP,
sign_time TIMESTAMP,
finish_time TIMESTAMP,
– 各阶段时长（派生度量）
create_to_pay_min INT, – 下单到支付分钟数
pay_to_ship_hour INT, – 支付到发货小时数
ship_to_sign_hour INT, – 发货到签收小时数
– 当前状态
current_status STRING
) PARTITIONED BY (dt STRING);

数据演变过程：

T2 - 06-09 支付后更新：
10001 | 06-09 14:00 | 06-09 14:05 | NULL | NULL | 已支付

T3 - 06-10 发货后更新：
10001 | 06-09 14:00 | 06-09 14:05 | 06-10 10:00 | NULL | 已发货

T4 - 06-12 签收后更新（流程结束）：
10001 | 06-09 14:00 | 06-09 14:05 | 06-10 10:00 | 06-12 16:30 | 已签收

特点：一个订单永远只有一行，随着流程推进原地更新。

为什么需要这种表？
直接查"平均配送时长"，如果用事务事实表要 JOIN 三张表（支付、发货、签收）—— 慢且复杂。累积快照表一张表就搞定，性能提升 10 倍以上。

无事实的事实表（Factless Fact Table）

核心特征：

表里没有数值度量，只有维度键
用来记录"事件发生了"这个事实本身

适用场景：

记录只关心是否发生的事件
统计覆盖、参与、出席等场景

典型应用：

用户参加活动、学生上课、员工打卡、商品上架记录

实例：用户参加双11活动事实表

CREATE TABLE dwd_mkt_activity_join_di (
user_id BIGINT,
activity_id BIGINT,
activity_name STRING,
join_channel STRING,
join_time TIMESTAMP
– 注意：没有金额、数量等度量
) PARTITIONED BY (dt STRING);

user_id | activity_id | activity_name | join_channel | join_time
A | 2026111 | “双11预热” | APP | 14:00
B | 2026111 | “双11预热” | H5 | 14:05
A | 2026112 | “猫超满减” | APP | 14:08

典型查询：
– 双11预热活动有多少人参与？
SELECT COUNT(DISTINCT user_id)
FROM dwd_mkt_activity_join_di
WHERE activity_id = 2026111;

– 没参加预热活动但买了东西的用户？（覆盖率分析）

特点：只关心有没有发生，不关心发生了多少。

汇总事实表（Aggregated Fact Table）—— DWS 层的本质

核心特征：

是事务/快照事实表按维度聚合后的结果
一行 = 一个对象在一个时间周期的汇总
严格说这是 DWS 层的概念

适用场景：

高频查询的聚合结果预计算
通用统计分析的"半成品"

实例：用户日交易汇总

CREATE TABLE dws_user_trade_1d (
user_id, dt,
pay_order_cnt, pay_amount,
refund_order_cnt, refund_amount
);

user_id | dt | pay_order_cnt | pay_amount
A | 2026-06-11 | 3 | 597
B | 2026-06-11 | 1 | 199

二、维度表（4 种）

维度表存储业务对象的属性，用于描述事实表中的"谁、什么、哪里、什么时候"。

普通维度表（Regular Dimension）

核心特征：

每个业务对象一行
属性变化时直接覆盖（不保留历史）

适用场景：

属性变化频繁但不需要追溯历史变化
维度属性即使变了也只关心"当前值"

典型应用：

商品名、店铺名、类目结构（一般用最新值）

实例：店铺维表

CREATE TABLE dim_shop_df (
shop_id BIGINT,
shop_name STRING,
shop_type STRING,
province STRING,
rating DECIMAL(3,2)
) PARTITIONED BY (dt STRING); – 每天全量覆盖

特点：今天店铺改名了，明天表里就是新名字，旧名字消失。

缓慢变化维 SCD（Slowly Changing Dimension）⭐ 重要

核心特征：

业务对象的属性会变化
需要记录历史变化用于历史分析

SCD Type 1：直接覆盖（=普通维度，不保留历史）

最简单，不展开。

SCD Type 2：拉链表（最常用）⭐⭐⭐

适用场景：

维度变化需要追溯历史
需要回到任意时间点查询当时的属性值

典型应用：

会员等级变化、商品价格变化、组织架构变化、用户标签变化

实例：会员等级拉链表

CREATE TABLE dim_member_user_scd2 (
user_id BIGINT,
nick_name STRING,
member_level STRING,
city STRING,
start_date STRING COMMENT ‘该状态生效起始日’,
end_date STRING COMMENT ‘该状态失效日 9999-12-31表示当前有效’,
is_current INT COMMENT ‘1当前有效 0历史’
);

user_id | nick_name | member_level | start_date | end_date | is_current
A | 小明 | 普通会员 | 2025-01-01 | 2025-06-30 | 0
A | 小明 | 黄金会员 | 2025-07-01 | 2026-02-15 | 0
A | 小明 | 钻石会员 | 2026-02-16 | 9999-12-31 | 1

典型查询：
– 2025-08-01 这天，用户A是什么等级？
SELECT member_level FROM dim_member_user_scd2
WHERE user_id = ‘A’
AND ‘2025-08-01’ BETWEEN start_date AND end_date;
– 结果：黄金会员

为什么不用 SCD1 覆盖？如果直接覆盖，那么 2025 年的订单 join 维表时，A 会被错误地标记为"钻石会员"，导致历史分析失真。

SCD Type 3：增加历史列（用得少）

只保留前一个状态，加 previous_level 字段。生产中很少用，了解即可。

日期维度表（Date Dimension）⭐ 必备

核心特征：

静态表，提前生成 N 年的日期
包含所有日历相关属性

适用场景：

几乎所有需要按时间分析的场景
节假日分析、同比环比

实例：

CREATE TABLE dim_date (
dt STRING, – ‘2026-06-11’
year INT,
quarter INT,
month INT,
week_of_year INT,
day_of_week INT,
is_weekend INT,
is_holiday INT,
holiday_name STRING, – ‘端午节’
is_double11 INT,
is_618 INT,
workday_seq INT – 当年第N个工作日
);

dt | year | month | is_weekend | is_holiday | holiday_name | is_double11
2026-06-11 | 2026 | 6 | 0 | 0 | NULL | 0
2026-06-13 | 2026 | 6 | 1 | 0 | NULL | 0
2026-06-19 | 2026 | 6 | 0 | 1 | ‘端午’ | 0
2026-11-11 | 2026 | 11 | 0 | 0 | NULL | 1

为什么需要？业务方常问"双11当天 vs 平日的对比"、“周末销售占比”，有日期维表一行 JOIN 就出来了。

杂项维度表（Junk Dimension）

核心特征：

把一堆低基数、关系不大的标志位/枚举值合并成一张维度表
避免事实表里有几十个标志字段

适用场景：

事实表里有很多 yes/no、状态枚举
这些字段单独建维表太碎，留在事实表又太多

实例：订单杂项维度

CREATE TABLE dim_order_junk (
junk_key BIGINT,
order_source STRING, – APP/H5/PC/小程序
pay_method STRING, – 支付宝/微信/信用卡
is_gift INT, – 是否礼品
is_invoice INT, – 是否开发票
delivery_type STRING – 普通/次日达/当日达
);

junk_key | order_source | pay_method | is_gift | delivery_type
1 | APP | 支付宝 | 0 | 次日达
2 | APP | 支付宝 | 0 | 当日达
3 | H5 | 微信 | 1 | 普通
… (所有组合的笛卡尔积)

事实表里只存 junk_key 一列，节省空间且查询整洁。

桥接表（Bridge Table）—— 解决多值维度

核心特征：

处理多对多关系
一个事实对应多个维度值

适用场景：

一笔订单关联多个商品标签
一篇文章有多个作者
一个用户有多个标签

实例：用户-标签桥接表

– 用户标签桥接（一个用户多个标签）
CREATE TABLE bridge_user_tag (
user_id BIGINT,
tag_id BIGINT,
weight DECIMAL(5,2) – 标签权重
);

user_id | tag_id | weight
A | 101 | 0.8 ← “美妆爱好者”
A | 205 | 0.6 ← “母婴”
A | 308 | 0.9 ← “高净值”
B | 101 | 0.7

三、总结对比表

┌──────────────────┬────────────────────┬──────────────────────┬──────────────────────┐
│ 类型 │ 用途 │ 数据特征 │ 典型实例 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 事务事实表 │ 记录原子业务事件 │ 只增不改，按时间累积 │ 订单、支付、点击 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 周期快照事实表 │ 记录定期状态 │ 定期插入，看趋势 │ 每日库存、每日余额 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 累积快照事实表 │ 跟踪流程生命周期 │ 多次更新同一行 │ 订单全链路、招聘流程 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 无事实的事实表 │ 记录"发生过"的事件 │ 只有维度键 │ 活动参与、考勤打卡 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 汇总事实表 │ 预聚合结果（DWS） │ 按维度聚合 │ 用户日交易汇总 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 普通维度表 │ 维度属性，不留历史 │ 全量覆盖 │ 店铺维、商品维 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 缓慢变化维(SCD2) │ 维度属性，保留历史 │ 拉链表 │ 会员等级历史 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 日期维度表 │ 时间属性扩展 │ 静态预生成 │ 日期+节假日 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 杂项维度表 │ 合并低基数标志 │ 笛卡尔组合 │ 订单杂项 │
├──────────────────┼────────────────────┼──────────────────────┼──────────────────────┤
│ 桥接表 │ 多对多关系 │ 关联表 │ 用户-标签 │
└──────────────────┴────────────────────┴──────────────────────┴──────────────────────┘

四、决策树：我该用哪种表？

要存储的数据是什么？

├─ 业务对象的属性（用户、商品、店铺信息）
│ │
│ ├─ 属性会变,要历史吗?
│ │ ├─ 不要 → 普通维度表（SCD1）
│ │ └─ 要 → 拉链表（SCD2）
│ │
│ ├─ 是时间属性 → 日期维度表
│ ├─ 一堆低基数标志 → 杂项维度表
│ └─ 多对多关系 → 桥接表
│
└─ 业务过程的度量（订单金额、点击数）
│
├─ 一次性事件,不再变化 → 事务事实表 ⭐ 默认选这个
├─ 定期记录状态变化 → 周期快照事实表
├─ 有明确生命周期阶段 → 累积快照事实表
├─ 只关心是否发生 → 无事实的事实表
└─ 已经聚合好的指标 → 汇总事实表（DWS层）

五、实战中的占比

根据我的经验,在一个中大型电商数仓里:

┌────────────────────────┬──────┬────────────────┐
│ 表类型 │ 占比 │ 备注 │
├────────────────────────┼──────┼────────────────┤
│ 事务事实表 │ ~60% │ 绝对主力 │
├────────────────────────┼──────┼────────────────┤
│ 汇总事实表 (DWS) │ ~15% │ DWS 层的主体 │
├────────────────────────┼──────┼────────────────┤
│ 普通维度表 │ ~10% │ 常规维表 │
├────────────────────────┼──────┼────────────────┤
│ 拉链表 (SCD2) │ ~5% │ 核心维度都该用 │
├────────────────────────┼──────┼────────────────┤
│ 累积快照 │ ~3% │ 流程类业务必备 │
├────────────────────────┼──────┼────────────────┤
│ 周期快照 │ ~3% │ 库存、余额必备 │
├────────────────────────┼──────┼────────────────┤
│ 日期维 │ 几张 │ 必备 │
├────────────────────────┼──────┼────────────────┤
│ 无事实表 / 杂项 / 桥接 │ ~4% │ 特定场景 │
└────────────────────────┴──────┴────────────────┘

六、一句话总结

▎ 事实表存"发生了什么"，维度表存"它是什么"。
▎
▎ - 事实表 4 种：事务（一次性）、周期快照（看趋势）、累积快照（看流程）、无事实（看覆盖）
▎ - 维度表 4 类：普通（不留史）、拉链（留历史）、日期（特殊）、杂项/桥接（特殊关系）
▎
▎ 90% 的建模工作 = 事务事实表 + 拉链表，搞定这两个，数仓基础就稳了。

记忆口诀：