数据仓库核心：维度表与事实表设计完全指南
数据仓库核心：维度表与事实表设计完全指南
-
- 1. 维度表与事实表概述
-
- 1.1 什么是维度表？
- 1.2 什么是事实表？
- 1.3 两者关系图
- 2. 维度表设计
-
- 2.1 维度表设计流程图
- 2.2 维度表设计原则
-
- 原则1：使用代理键
- 原则2：维度属性应详细丰富
- 原则3：处理缓慢变化维（SCD）
- 原则4：建立维度层次关系
- 原则5：维度表应小而窄
- 2.3 维度表设计检查清单
- 3. 事实表设计
-
- 3.1 事实表设计流程图
- 3.2 事实表设计原则
-
- 原则1：明确声明粒度
- 原则2：使用外键关联维度表
- 原则3：区分事实度量类型
- 原则4：处理退化维度
- 原则5：事实表应深而窄
- 3.3 事实表类型详解
- 3.4 事实表设计检查清单
- 4. 维度表与事实表关系设计
-
- 4.1 星型模型 vs 雪花模型
- 4.2 设计选择建议
- 5. 实战案例：电商数仓维度与事实表设计
-
- 5.1 业务需求
- 5.2 维度表设计
- 5.3 事实表设计
- 5.4 查询示例
- 6. 常见设计问题与解决方案
- 7. 结语
|
🌺The Begin🌺点点关注，收藏不迷路🌺
|
在数据仓库的维度建模中，维度表和事实表是最核心的两类表结构。它们如同数据仓库的骨架和血肉，决定了数据分析的能力上限。本文将深入剖析维度表和事实表的设计方法、核心原则以及最佳实践，帮助读者构建健壮、高效的数据模型。
1. 维度表与事实表概述
1.1 什么是维度表？
维度表是描述业务实体属性的表，它提供了观察业务事件的"视角"或"上下文"。维度表回答的是"谁、什么、哪里、何时"等问题。
维度表示例：
- 客户维度：客户ID、姓名、地址、等级
- 产品维度：产品ID、名称、品类、品牌
- 时间维度：日期、年、季、月、周、日
1.2 什么是事实表？
事实表是存储业务事件度量值的表，它记录了"发生了什么"。事实表通常包含数值型的可加度量。
事实表示例：
- 销售事实：订单ID、销售额、数量、折扣
- 库存事实：产品ID、库存量、库存金额
1.3 两者关系图
渲染错误: Mermaid 渲染失败: Parse error on line 12: … string level } DIM_PRODU ———————-^ Expecting 'ATTRIBUTE_WORD', got 'BLOCK_STOP'
2. 维度表设计
2.1 维度表设计流程图
缓慢变化维
属性设计
开始维度表设计
识别业务实体
确定维度属性
选择代理键策略
设计缓慢变化维
建立层次关系
优化属性结构
验证与评审
完成设计
业务主键
描述属性
层次属性
派生属性
Type1: 覆盖
Type2: 新增行
Type3: 新增列
2.2 维度表设计原则
原则1：使用代理键
代理键是无业务含义的整数型主键，与业务主键分离。
-- ✅ 推荐：使用代理键
CREATE TABLE dim_customer (
customer_sk BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '代理键',
customer_id BIGINT NOT NULL COMMENT '业务主键',
customer_name VARCHAR(100),
-- 其他属性
UNIQUE KEY uk_customer_id (customer_id)
);
-- ❌ 不推荐：直接使用业务主键
CREATE TABLE dim_customer (
customer_id BIGINT PRIMARY KEY, -- 业务主键可能变更
customer_name VARCHAR(100)
);
代理键的优势：
- 隔离业务系统的键变更
- 支持缓慢变化维（SCD Type 2）
- 提升Join性能（整数比字符串快）
- 隐藏业务敏感信息
原则2：维度属性应详细丰富
-- ✅ 推荐：丰富的维度属性
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
year INT,
quarter INT,
month INT,
month_name VARCHAR(20),
week_of_year INT,
day_of_month INT,
day_of_week INT,
day_name VARCHAR(10),
is_weekend BOOLEAN,
is_holiday BOOLEAN,
holiday_name VARCHAR(50)
);
-- ❌ 不推荐：属性太少
CREATE TABLE dim_date_bad (
date_key INT PRIMARY KEY,
full_date DATE,
year INT,
month INT
);
原则3：处理缓慢变化维（SCD）
-- SCD Type 2：完整历史追踪
CREATE TABLE dim_customer_scd2 (
customer_sk BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT NOT NULL,
customer_name VARCHAR(100),
city VARCHAR(50),
level TINYINT,
-- 版本控制字段
version_no INT DEFAULT 1,
effective_from DATE NOT NULL,
effective_to DATE DEFAULT '9999-12-31',
is_current TINYINT DEFAULT 1,
-- 变更追踪
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_time TIMESTAMP
);
-- 查询当前数据
SELECT * FROM dim_customer_scd2 WHERE is_current = 1;
-- 查询历史数据
SELECT * FROM dim_customer_scd2
WHERE customer_id = 1001
AND '2023-06-01' BETWEEN effective_from AND effective_to;
原则4：建立维度层次关系
-- 地理维度层次：国家 -> 省份 -> 城市 -> 区县
CREATE TABLE dim_location (
location_sk BIGINT AUTO_INCREMENT PRIMARY KEY,
location_id BIGINT,
-- 层次属性
country VARCHAR(50),
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
-- 便于聚合的冗余字段
country_code VARCHAR(10),
province_code VARCHAR(10),
city_code VARCHAR(10)
);
-- 产品维度层次：品类 -> 子品类 -> 产品
CREATE TABLE dim_product (
product_sk BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT,
product_name VARCHAR(200),
-- 层次属性
category_id INT,
category_name VARCHAR(100),
subcategory_id INT,
subcategory_name VARCHAR(100),
-- 其他属性
brand VARCHAR(100),
unit_price DECIMAL(10,2)
);
原则5：维度表应小而窄
-- ✅ 推荐：拆分为多个维度
-- 客户基本信息维度
CREATE TABLE dim_customer_base (
customer_sk BIGINT PRIMARY KEY,
customer_id BIGINT,
customer_name VARCHAR(100),
register_date DATE
);
-- 客户联系方式维度
CREATE TABLE dim_customer_contact (
customer_sk BIGINT PRIMARY KEY,
phone VARCHAR(20),
email VARCHAR(100),
address VARCHAR(200)
);
-- ❌ 不推荐：单表过大
CREATE TABLE dim_customer_bad (
customer_sk BIGINT PRIMARY KEY,
-- 100+ 个字段
);
2.3 维度表设计检查清单
## 基础设计
□ 是否使用了代理键？
□ 业务主键是否保留并建立唯一索引？
□ 字段是否有完整的注释？
□ 数据类型选择是否合理？
## 属性设计
□ 维度属性是否足够详细？
□ 层次关系是否清晰？
□ 是否有冗余的派生属性？
□ 是否避免了过多的字段？
## 变更处理
□ 是否确定了SCD策略？
□ SCD Type2是否包含版本控制字段？
□ 时间字段是否精确到日/时分秒？
## 性能优化
□ 是否需要分桶/分区？
□ 是否建立了合适的索引？
□ 是否需要物化视图？
3. 事实表设计
3.1 事实表设计流程图
度量类型
粒度声明
开始事实表设计
识别业务过程
声明事实粒度
确定维度外键
定义事实度量
处理退化维度
设计事实表类型
验证与评审
完成设计
事务粒度
周期快照
累积快照
可加性
半可加性
不可加性
3.2 事实表设计原则
原则1：明确声明粒度
粒度是事实表中每一行数据的业务含义，是最重要的设计决策。
-- ✅ 事务粒度：每笔交易一条记录
CREATE TABLE fact_transaction (
transaction_id BIGINT PRIMARY KEY,
date_key INT,
customer_sk BIGINT,
product_sk BIGINT,
amount DECIMAL(10,2),
quantity INT
);
-- ✅ 周期快照粒度：每天每个产品一条记录
CREATE TABLE fact_daily_inventory (
date_key INT,
product_sk BIGINT,
inventory_quantity INT,
inventory_amount DECIMAL(15,2),
PRIMARY KEY (date_key, product_sk)
);
-- ✅ 累积快照粒度：每个订单生命周期一条记录
CREATE TABLE fact_order_lifecycle (
order_id BIGINT PRIMARY KEY,
order_date_key INT,
payment_date_key INT,
shipment_date_key INT,
delivery_date_key INT,
order_amount DECIMAL(10,2),
order_status VARCHAR(20)
);
原则2：使用外键关联维度表
-- ✅ 推荐：使用代理键作为外键
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL, -- 关联 dim_date
customer_sk BIGINT NOT NULL, -- 关联 dim_customer
product_sk BIGINT NOT NULL, -- 关联 dim_product
store_sk INT NOT NULL, -- 关联 dim_store
quantity INT,
amount DECIMAL(10,2),
FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (customer_sk) REFERENCES dim_customer(customer_sk),
FOREIGN KEY (product_sk) REFERENCES dim_product(product_sk)
);
-- ❌ 不推荐：直接存储维度属性
CREATE TABLE fact_sales_bad (
sale_id BIGINT PRIMARY KEY,
sale_date DATE, -- 应该用外键
customer_name VARCHAR(100), -- 应该用外键
product_name VARCHAR(200), -- 应该用外键
quantity INT,
amount DECIMAL(10,2)
);
原则3：区分事实度量类型
-- 事实表度量类型示例
CREATE TABLE fact_sales_complete (
sale_id BIGINT PRIMARY KEY,
-- 维度外键
date_key INT,
customer_sk BIGINT,
product_sk BIGINT,
-- 可加性度量：可跨任意维度求和
quantity INT COMMENT '销售数量-可加',
amount DECIMAL(10,2) COMMENT '销售金额-可加',
-- 半可加性度量：只能跨部分维度求和
inventory_count INT COMMENT '库存数量-可跨产品求和，不可跨时间求和',
-- 不可加性度量：不能求和
unit_price DECIMAL(10,2) COMMENT '单价-不可加',
discount_rate DECIMAL(5,2) COMMENT '折扣率-不可加'
);
原则4：处理退化维度
退化维度是存储在事实表中的维度属性，没有独立的维度表。
-- ✅ 退化维度示例：订单号
CREATE TABLE fact_orders (
order_id BIGINT PRIMARY KEY, -- 退化维度
order_number VARCHAR(50), -- 退化维度
date_key INT,
customer_sk BIGINT,
amount DECIMAL(10,2)
);
-- 订单号不需要单独的维度表，因为：
-- 1. 每个订单号只出现一次
-- 2. 订单号没有其他属性
-- 3. 查询时直接使用即可
原则5：事实表应深而窄
-- ✅ 推荐：行数多，列数少
CREATE TABLE fact_sales_good (
sale_id BIGINT PRIMARY KEY,
date_key INT,
customer_sk BIGINT,
product_sk BIGINT,
store_sk INT,
quantity INT,
amount DECIMAL(10,2)
);
-- 7列，但可能有数十亿行
-- ❌ 不推荐：列数过多
CREATE TABLE fact_sales_bad (
sale_id BIGINT PRIMARY KEY,
date_key INT,
customer_sk BIGINT,
product_sk BIGINT,
store_sk INT,
quantity INT,
amount DECIMAL(10,2),
discount_1 DECIMAL(10,2),
discount_2 DECIMAL(10,2),
fee_1 DECIMAL(10,2),
fee_2 DECIMAL(10,2),
tax_rate DECIMAL(5,2),
-- ... 50+ 列
);
3.3 事实表类型详解
| 类型 | 粒度 | 更新方式 | 适用场景 | 示例 |
|---|---|---|---|---|
| 事务事实表 | 每笔交易 | 增量插入 | 交易记录、日志 | 订单、支付、访问 |
| 周期快照事实表 | 固定周期 | 全量/增量 | 状态监控 | 库存、余额 |
| 累积快照事实表 | 业务流程 | 更新 | 流程追踪 | 订单履行、工单处理 |
-- 事务事实表
CREATE TABLE fact_transaction (
trans_id BIGINT PRIMARY KEY,
date_key INT,
customer_sk BIGINT,
amount DECIMAL(10,2)
);
-- 周期快照事实表（每日库存）
CREATE TABLE fact_daily_inventory (
date_key INT,
product_sk BIGINT,
inventory_qty INT,
PRIMARY KEY (date_key, product_sk)
);
-- 累积快照事实表（订单生命周期）
CREATE TABLE fact_order_lifecycle (
order_id BIGINT PRIMARY KEY,
order_date_key INT,
payment_date_key INT,
ship_date_key INT,
deliver_date_key INT,
order_amount DECIMAL(10,2),
order_status VARCHAR(20)
);
3.4 事实表设计检查清单
## 粒度设计
□ 是否明确了每一行数据的业务含义？
□ 粒度是否满足所有分析需求？
□ 是否避免了混合粒度？
## 外键设计
□ 是否使用了维度表的代理键？
□ 外键是否设置了 NOT NULL？
□ 是否需要外键约束？
## 度量设计
□ 度量字段的数据类型是否合适？
□ 是否区分了可加/半可加/不可加度量？
□ 单位是否统一？
## 性能设计
□ 是否需要分区？
□ 是否需要分桶？
□ 是否建立了合适的索引？
4. 维度表与事实表关系设计
4.1 星型模型 vs 雪花模型
雪花模型
事实表
主维度表
子维度表1
子维度表2
星型模型
事实表
维度表1
维度表2
维度表3
4.2 设计选择建议
| 场景 | 推荐模型 | 原因 |
|---|---|---|
| BI报表、数据集市 | 星型模型 | 查询简单，性能好 |
| 维度层次复杂 | 雪花模型 | 节省存储，层次清晰 |
| 大数据量 | 星型模型 | 减少Join，提升性能 |
| 存储成本敏感 | 雪花模型 | 减少冗余 |
| 实时数仓 | 星型模型 | 查询快速 |
5. 实战案例：电商数仓维度与事实表设计
5.1 业务需求
电商平台需要分析：
- 每日销售额、订单量
- 各品类销售排行
- 用户消费分析
- 地域销售分布
5.2 维度表设计
-- 1. 时间维度
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE NOT NULL,
year INT,
quarter INT,
month INT,
month_name VARCHAR(20),
week INT,
day_of_week INT,
day_name VARCHAR(10),
is_weekend BOOLEAN,
is_holiday BOOLEAN
);
-- 2. 客户维度（SCD Type 2）
CREATE TABLE dim_customer (
customer_sk BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT NOT NULL,
customer_name VARCHAR(100),
phone VARCHAR(20),
city VARCHAR(50),
province VARCHAR(50),
level TINYINT COMMENT '1-普通,2-白银,3-黄金,4-钻石',
register_date DATE,
-- SCD字段
version_no INT DEFAULT 1,
effective_from DATE,
effective_to DATE DEFAULT '9999-12-31',
is_current TINYINT DEFAULT 1,
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer_id (customer_id),
INDEX idx_is_current (is_current)
);
-- 3. 产品维度
CREATE TABLE dim_product (
product_sk BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT NOT NULL,
product_name VARCHAR(200),
category_id INT,
category_name VARCHAR(100),
subcategory_id INT,
subcategory_name VARCHAR(100),
brand VARCHAR(100),
unit_price DECIMAL(10,2),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_brand (brand)
);
-- 4. 门店维度
CREATE TABLE dim_store (
store_sk INT AUTO_INCREMENT PRIMARY KEY,
store_id INT NOT NULL,
store_name VARCHAR(100),
city VARCHAR(50),
province VARCHAR(50),
region VARCHAR(20) COMMENT '华东/华南/华北/西部',
store_type VARCHAR(20) COMMENT '旗舰店/专卖店/加盟店'
);
5.3 事实表设计
-- 1. 订单事务事实表
CREATE TABLE fact_orders (
order_id BIGINT PRIMARY KEY,
order_number VARCHAR(50),
-- 维度外键
order_date_key INT NOT NULL,
customer_sk BIGINT NOT NULL,
store_sk INT NOT NULL,
-- 事实度量
order_amount DECIMAL(10,2) COMMENT '订单金额',
discount_amount DECIMAL(10,2) COMMENT '优惠金额',
actual_amount DECIMAL(10,2) COMMENT '实付金额',
order_count INT DEFAULT 1 COMMENT '订单计数',
-- 退化维度
order_status TINYINT COMMENT '订单状态',
payment_method TINYINT COMMENT '支付方式',
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (customer_sk) REFERENCES dim_customer(customer_sk),
FOREIGN KEY (store_sk) REFERENCES dim_store(store_sk)
) PARTITION BY RANGE (order_date_key);
-- 2. 订单明细事实表
CREATE TABLE fact_order_items (
item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
-- 维度外键
product_sk BIGINT NOT NULL,
-- 事实度量
quantity INT,
unit_price DECIMAL(10,2),
subtotal DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES fact_orders(order_id),
FOREIGN KEY (product_sk) REFERENCES dim_product(product_sk)
);
-- 3. 每日销售汇总事实表（周期快照）
CREATE TABLE fact_daily_sales (
date_key INT NOT NULL,
product_sk BIGINT NOT NULL,
store_sk INT NOT NULL,
-- 汇总度量
total_quantity INT,
total_amount DECIMAL(15,2),
order_count INT,
customer_count INT,
PRIMARY KEY (date_key, product_sk, store_sk),
FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (product_sk) REFERENCES dim_product(product_sk),
FOREIGN KEY (store_sk) REFERENCES dim_store(store_sk)
);
5.4 查询示例
-- 查询：2024年1月各品类销售额排行
SELECT
p.category_name,
SUM(f.actual_amount) as total_sales,
COUNT(DISTINCT f.order_id) as order_count,
SUM(f.order_count) as order_count_alt
FROM fact_orders f
JOIN dim_date d ON f.order_date_key = d.date_key
JOIN dim_customer c ON f.customer_sk = c.customer_sk
JOIN fact_order_items i ON f.order_id = i.order_id
JOIN dim_product p ON i.product_sk = p.product_sk
WHERE d.year = 2024
AND d.month = 1
AND c.is_current = 1
GROUP BY p.category_name
ORDER BY total_sales DESC;
-- 查询：各省份销售分布
SELECT
s.province,
SUM(f.actual_amount) as total_sales,
COUNT(DISTINCT f.customer_sk) as customer_count
FROM fact_orders f
JOIN dim_date d ON f.order_date_key = d.date_key
JOIN dim_store s ON f.store_sk = s.store_sk
WHERE d.year = 2024
GROUP BY s.province
ORDER BY total_sales DESC;
6. 常见设计问题与解决方案
| 问题 | 表现 | 解决方案 |
|---|---|---|
| 维度表过于宽泛 | 单表100+字段 | 拆分为多个子维度 |
| 事实表粒度过粗 | 无法下钻分析 | 保持原子粒度 |
| 缺少代理键 | 业务主键变更困难 | 添加代理键 |
| 混合粒度 | 同一表不同粒度 | 拆分为多张事实表 |
| SCD处理不当 | 历史数据被覆盖 | 使用SCD Type 2 |
| 外键缺失 | 关联查询性能差 | 建立外键索引 |
7. 结语
维度表和事实表是数据仓库维度建模的核心。掌握它们的设计方法，是构建高质量数据仓库的基础。
设计核心要点：
| 表类型 | 核心原则 | 关键特征 |
|---|---|---|
| 维度表 | 丰富、稳定、可追溯 | 代理键、SCD、层次属性 |
| 事实表 | 明确、可加、深而窄 | 外键、度量、明确粒度 |
设计口诀：
- 维度表：代理键做主键，属性要丰富，变化要追溯
- 事实表：粒度要声明，外键连维度，度量要可加
- 关系：星型是首选，雪花是备选，性能是目标
|
🌺The End🌺点点关注，收藏不迷路🌺
|
© 版权声明
文章版权归作者所有，未经允许请勿转载。
