# 维度建模实战：从 0 设计电商数仓
·
大数据开发核心技能：星型模型 vs 雪花模型、事实表设计、维度表设计、拉链表实现、电商数仓完整案例，从 0 到 1 搭建可落地的数仓模型
📌 前言
真实生产问题
问题场景：
某电商公司数据平台遇到的问题：
问题 1：需求来了不知道从哪张表开始
- 运营要"用户下单行为分析"，开发问：用订单表还是订单明细表？
- 产品要"商品销售排行"，开发问：要不要算退款订单？
- 老板要"每日 GMV"，三个报表数据不一致
问题 2：表越来越多，关系越来越乱
- 订单相关表 20+ 张：order_info、order_detail、order_pay、order_refund...
- 每张表都有 user_id、product_id，但不知道要不要关联用户表和商品表
- 新来的开发问：为什么这张表有 50 个字段？答：因为大家都要加字段
问题 3：查询慢，开发慢
- 一个简单的"用户订单查询"，要关联 5 张表
- 每次加新需求，都要重新设计表结构
- 开发周期：简单需求 3 天，复杂需求 2 周
维度建模解决：
- 统一数据视图：事实表 + 维度表，关系清晰
- 统一指标口径：GMV、订单数等只在一个地方定义
- 提高查询性能：星型模型减少关联，预聚合常用指标
- 降低开发成本：新需求基于现有模型快速开发
优化后效果：
- 需求响应速度：3 天 → 1 天
- 查询性能：复杂查询 30 秒 → 3 秒
- 表数量：50+ 张 → 15 张核心表
- 数据一致性：60% → 99%
🏗️ 维度建模核心原理
什么是维度建模？
定义：
维度建模 = 事实表 + 维度表
事实表（Fact Table）：
- 记录业务过程（发生了什么）
- 数值型度量（多少、多久）
- 外键关联维度表
维度表（Dimension Table）：
- 描述业务实体（谁、什么、哪里）
- 文本型描述（名称、类别、属性）
- 主键被事实表引用
形象理解：
事实表 = Excel 中的数据行（订单记录）
维度表 = Excel 中的下拉选项（用户列表、商品列表）
经典案例：
订单事实表（fact_order）：
| order_id | user_id | product_id | pay_amount | order_date |
|----------|---------|------------|------------|------------|
| 001 | U001 | P001 | 100.50 | 2026-03-24 |
| 002 | U002 | P002 | 200.00 | 2026-03-24 |
用户维度表（dim_user）：
| user_id | user_name | gender | age_group | city |
|---------|-----------|--------|-----------|----------|
| U001 | 张三 | 男 | 25-30 | 北京 |
| U002 | 李四 | 女 | 20-25 | 上海 |
商品维度表（dim_product）：
| product_id | product_name | category | brand | price |
|------------|--------------|----------|---------|-------|
| P001 | iPhone 15 | 手机 | Apple | 8999 |
| P002 | AirPods Pro | 配件 | Apple | 1899 |
查询：统计各城市 GMV
SELECT u.city, SUM(o.pay_amount) AS gmv
FROM fact_order o
JOIN dim_user u ON o.user_id = u.user_id
GROUP BY u.city;
结果：
| city | gmv |
|------|---------|
| 北京 | 100.50 |
| 上海 | 200.00 |
星型模型 vs 雪花模型
星型模型（Star Schema）⭐推荐
订单事实表
│
┌─────┼─────┐
│ │ │
用户表 商品表 时间表
特点：
- 事实表在中心，维度表围绕
- 维度表不规范化（冗余存储）
- 查询性能好（关联少）
- 存储空间稍大
用户维度表（冗余）：
| user_id | user_name | gender | age_group | city | province | region |
|---------|-----------|--------|-----------|------|----------|--------|
| U001 | 张三 | 男 | 25-30 | 北京 | 北京 | 华北 |
适用场景：
✓ 90% 的数仓场景
✓ 查询性能优先
✓ 维度层级不多
雪花模型（Snowflake Schema）
订单事实表
│
┌─────┼─────┐
│ │ │
用户表 商品表 时间表
│ │
城市表 类目表
│
省份表
特点：
- 维度表规范化（减少冗余）
- 查询需要更多关联
- 存储空间小
- 维护复杂
用户维度表（规范化）：
| user_id | user_name | gender | age_group | city_id |
|---------|-----------|--------|-----------|---------|
| U001 | 张三 | 男 | 25-30 | 1101 |
城市维度表：
| city_id | city_name | province_id |
|---------|-----------|-------------|
| 1101 | 北京 | 11 |
适用场景：
✓ 维度层级很深（5 层+）
✓ 存储空间敏感
✓ 维度属性频繁变化
对比总结：
| 特性 | 星型模型 | 雪花模型 |
|---|---|---|
| 表数量 | 少 | 多 |
| 关联复杂度 | 低 | 高 |
| 查询性能 | 高 | 低 |
| 存储冗余 | 大 | 小 |
| 维护成本 | 低 | 高 |
| 推荐使用 | ⭐⭐⭐⭐⭐ | ⭐⭐ |
建议：优先使用星型模型，除非有明确的存储或维护需求。
事实表设计深度解析
事实表三要素：
1. 维度外键（Dimension Keys）
- 关联维度表的主键
- 命名规范：dim_{维度}_id 或 {维度}_id
- 示例：user_id, product_id, order_date
2. 度量值（Measures）
- 数值型指标，可聚合
- 类型：
* 可加性：pay_amount（可跨维度相加）
* 半可加性：balance（只能跨某些维度相加）
* 不可加性：ratio（不能相加，要重新计算）
3. 退化维度（Degenerate Dimensions）
- 存储在事实表中的维度属性
- 特点：没有独立维度表
- 示例：order_no（订单号）、invoice_no（发票号）
事实表类型详解：
1. 事务事实表（Transaction Fact Table）
定义：记录某个时刻发生的事件
特点：每行代表一个事件，不可更新，只插入
案例：订单事实表
CREATE TABLE fact_order_transaction (
-- 维度外键
order_id BIGINT COMMENT '订单 ID',
user_id BIGINT COMMENT '用户 ID',
product_id BIGINT COMMENT '商品 ID',
order_date_id INT COMMENT '订单日期',
-- 退化维度
order_no STRING COMMENT '订单编号',
-- 度量值
pay_amount DECIMAL(18,2) COMMENT '支付金额',
product_qty INT COMMENT '商品数量',
discount_amount DECIMAL(18,2) COMMENT '折扣金额'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
数据示例：
| order_id | user_id | product_id | order_date_id | order_no | pay_amount | product_qty |
|----------|---------|------------|---------------|----------|------------|-------------|
| 1001 | 5001 | 3001 | 20260324 | ORD001 | 100.50 | 2 |
| 1002 | 5002 | 3002 | 20260324 | ORD002 | 200.00 | 1 |
适用场景：
✓ 订单、支付、登录等一次性事件
✓ 需要详细明细的场景
2. 周期快照事实表（Periodic Snapshot Fact Table）
定义：按固定时间间隔记录状态
特点：定期全量更新，记录某个时间点的状态
案例：用户账户日快照表
CREATE TABLE fact_user_account_daily (
-- 维度外键
user_id BIGINT COMMENT '用户 ID',
stat_date_id INT COMMENT '统计日期',
-- 度量值（账户状态）
balance DECIMAL(18,2) COMMENT '账户余额',
points INT COMMENT '积分',
coupon_count INT COMMENT '优惠券数量',
order_count_30d INT COMMENT '30 日订单数',
gmv_30d DECIMAL(18,2) COMMENT '30 日 GMV'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
数据示例：
| user_id | stat_date_id | balance | points | coupon_count | order_count_30d | gmv_30d |
|---------|--------------|---------|--------|--------------|-----------------|---------|
| 5001 | 20260324 | 500.00 | 1200 | 5 | 10 | 2000.00 |
| 5001 | 20260325 | 450.00 | 1250 | 4 | 11 | 2100.00 |
| 5002 | 20260324 | 1000.00 | 500 | 2 | 5 | 800.00 |
适用场景：
✓ 账户余额、库存状态
✓ 用户行为统计（日活、周活）
✓ 需要趋势分析的场景
3. 累积快照事实表（Accumulating Snapshot Fact Table）
定义：记录具有生命周期的业务流程
特点：一行数据会多次更新，记录流程进度
案例：订单履约事实表
CREATE TABLE fact_order_fulfillment (
-- 维度外键
order_id BIGINT COMMENT '订单 ID',
user_id BIGINT COMMENT '用户 ID',
product_id BIGINT COMMENT '商品 ID',
-- 时间戳（流程节点）
order_time TIMESTAMP COMMENT '下单时间',
pay_time TIMESTAMP COMMENT '支付时间',
ship_time TIMESTAMP COMMENT '发货时间',
confirm_time TIMESTAMP COMMENT '确认收货时间',
-- 度量值
order_amount DECIMAL(18,2) COMMENT '订单金额',
pay_amount DECIMAL(18,2) COMMENT '支付金额'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
数据示例（一行数据随时间更新）：
下单时：
| order_id | order_time | pay_time | ship_time | confirm_time |
|----------|------------|----------|-----------|--------------|
| 1001 | 10:00:00 | NULL | NULL | NULL |
支付后：
| order_id | order_time | pay_time | ship_time | confirm_time |
|----------|------------|----------|-----------|--------------|
| 1001 | 10:00:00 | 10:05:00 | NULL | NULL |
发货后：
| order_id | order_time | pay_time | ship_time | confirm_time |
|----------|------------|----------|-----------|--------------|
| 1001 | 10:00:00 | 10:05:00 | 14:30:00 | NULL |
完成：
| order_id | order_time | pay_time | ship_time | confirm_time |
|----------|------------|----------|-----------|--------------|
| 1001 | 10:00:00 | 10:05:00 | 14:30:00 | 16:00:00 |
适用场景：
✓ 订单履约流程
✓ 贷款审批流程
✓ 工单处理流程
维度表设计深度解析
维度表设计原则：
1. 丰富的描述性属性
- 用户维度：不只是 user_id，还要有性别、年龄段、城市、会员等级
- 商品维度：不只是 product_id，还要有类目、品牌、价格带、供应商
2. 层次结构扁平化
- 把省市区放在一行，不要拆成三张表
- 用户维度：province | city | district
3. 使用有意义的代理键
- 不用业务主键（如订单号），用自增 ID
- 好处：处理缓慢变化维、合并多源数据
4. 包含所有可能值
- 添加"未知"、"其他"选项
- 避免事实表关联时数据丢失
维度表类型详解：
1. 基础维度表
案例：商品维度表
CREATE TABLE dim_product (
product_id BIGINT COMMENT '商品 ID（代理键）',
product_name STRING COMMENT '商品名称',
category_level1 STRING COMMENT '一级类目',
category_level2 STRING COMMENT '二级类目',
category_level3 STRING COMMENT '三级类目',
brand STRING COMMENT '品牌',
price_band STRING COMMENT '价格带', -- 0-100, 100-500, 500+
supplier_id BIGINT COMMENT '供应商 ID',
is_active BOOLEAN COMMENT '是否在售',
create_time TIMESTAMP COMMENT '上架时间',
update_time TIMESTAMP COMMENT '更新时间'
)
STORED AS ORC;
数据示例：
| product_id | product_name | category_level1 | category_level2 | category_level3 | brand | price_band |
|------------|--------------|-----------------|-----------------|-----------------|--------|------------|
| 3001 | iPhone 15 | 数码 | 手机 | 智能手机 | Apple | 5000+ |
| 3002 | AirPods Pro | 数码 | 配件 | 耳机 | Apple | 1000-5000 |
| 3003 | 小米手环 8 | 数码 | 配件 | 智能手环 | 小米 | 100-500 |
设计要点：
✓ 类目分三级，满足不同粒度分析
✓ 价格带预计算，避免每次查询计算
✓ 包含 is_active 标记，方便过滤下架商品
2. 时间维度表
为什么需要时间维度表？
- 日期计算复杂（工作日、节假日、季度）
- 时间维度分析频繁（同比、环比、累计）
- 预计算提高效率
CREATE TABLE dim_date (
date_id INT COMMENT '日期 ID（YYYYMMDD）',
full_date DATE COMMENT '完整日期',
day_of_week INT COMMENT '星期几（1-7）',
day_name STRING COMMENT '星期名称',
day_of_month INT COMMENT '月份第几天',
day_of_year INT COMMENT '年第几天',
week_of_year INT COMMENT '年第几周',
month INT COMMENT '月份',
month_name STRING COMMENT '月份名称',
quarter INT COMMENT '季度',
year INT COMMENT '年份',
is_weekend BOOLEAN COMMENT '是否周末',
is_holiday BOOLEAN COMMENT '是否节假日',
holiday_name STRING COMMENT '节假日名称'
)
STORED AS ORC;
数据示例：
| date_id | full_date | day_of_week | day_name | month | quarter | year | is_weekend | is_holiday | holiday_name |
|---------|-----------|-------------|----------|-------|---------|------|------------|------------|--------------|
| 20260101| 2026-01-01| 4 | 星期三 | 1 | 1 | 2026 | false | true | 元旦 |
| 20260102| 2026-01-02| 5 | 星期五 | 1 | 1 | 2026 | false | false | NULL |
| 20260103| 2026-01-03| 6 | 星期六 | 1 | 1 | 2026 | true | false | NULL |
生成脚本（Python）：
from datetime import datetime, timedelta
def generate_date_dim(start_year, end_year):
dates = []
for year in range(start_year, end_year + 1):
for month in range(1, 13):
for day in range(1, 32):
try:
date = datetime(year, month, day)
date_id = int(date.strftime('%Y%m%d'))
day_of_week = date.isoweekday()
dates.append({
'date_id': date_id,
'full_date': date,
'day_of_week': day_of_week,
'day_name': ['一','二','三','四','五','六','日'][day_of_week-1],
'month': month,
'quarter': (month - 1) // 3 + 1,
'year': year,
'is_weekend': day_of_week > 5,
})
except:
pass
return dates
3. 用户维度表（含拉链表）
缓慢变化维（SCD）问题：
用户属性会变化：
- 2024 年：北京，单身
- 2025 年：上海，已婚
如何处理？三种方案：
方案 1：SCD Type 1（覆盖）
- 直接更新，不保留历史
- 适用：错误修正、不重要属性
方案 2：SCD Type 2（拉链表）⭐推荐
- 保留历史，每行有生效时间
- 适用：重要属性（城市、会员等级）
方案 3：SCD Type 3（新增列）
- 添加"上次值"列
- 适用：只保留最近一次变化
拉链表实战：
CREATE TABLE dim_user_scd (
user_id BIGINT COMMENT '用户 ID',
user_name STRING COMMENT '用户名称',
gender STRING COMMENT '性别',
age_group STRING COMMENT '年龄段',
city STRING COMMENT '城市',
member_level STRING COMMENT '会员等级',
start_date DATE COMMENT '生效开始日期',
end_date DATE COMMENT '生效结束日期',
is_current BOOLEAN COMMENT '是否当前版本'
)
STORED AS ORC;
数据示例：
| user_id | city | member_level | start_date | end_date | is_current |
|---------|------|--------------|------------|------------|------------|
| 5001 | 北京 | 普通会员 | 2024-01-01 | 2024-12-31 | false |
| 5001 | 上海 | 黄金会员 | 2025-01-01 | 2099-12-31 | true |
查询历史状态（2024 年用户在哪个城市）：
SELECT city FROM dim_user_scd
WHERE user_id = 5001
AND '2024-06-01' BETWEEN start_date AND end_date;
查询当前状态：
SELECT city FROM dim_user_scd
WHERE user_id = 5001 AND is_current = true;
🏭 电商数仓完整案例
业务背景
公司规模：
- 日均订单：50 万单
- SKU 数量：10 万 +
- 注册用户：1000 万 +
- 数据源：MySQL（订单/用户/商品）+ 日志（点击/浏览）
分析需求：
1. 销售分析：GMV、订单数、客单价（按时间/类目/地区）
2. 用户分析：新增用户、活跃用户、留存率、复购率
3. 商品分析：销量排行、库存周转、毛利率
4. 渠道分析：各渠道获客成本、ROI
数仓架构设计
数据流向：
业务系统（MySQL）
↓ ETL
ODS 层（原始数据）
↓ 清洗 + 维度关联
DWD 层（明细事实表）
↓ 轻度聚合
DWS 层（聚合事实表）
↓ 应用指标
ADS 层（应用数据表）
维度表（DIM 层）：
- dim_user（用户维度）
- dim_product（商品维度）
- dim_date（时间维度）
- dim_city（地区维度）
事实表（FACT 层）：
- fact_order_transaction（订单事务事实表）
- fact_order_daily_snapshot（订单日快照事实表）
- fact_user_daily_snapshot（用户日快照事实表）
- fact_product_daily_snapshot（商品日快照事实表）
表结构设计
1. 用户维度表
CREATE TABLE dim_user (
user_id BIGINT COMMENT '用户 ID',
user_name STRING COMMENT '用户名称',
gender STRING COMMENT '性别',
age_group STRING COMMENT '年龄段',
city STRING COMMENT '城市',
province STRING COMMENT '省份',
region STRING COMMENT '大区',
member_level STRING COMMENT '会员等级',
register_date DATE COMMENT '注册日期',
is_active BOOLEAN COMMENT '是否活跃',
create_time TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP COMMENT '更新时间'
)
STORED AS ORC;
-- 数据加载（从 ODS 层）
INSERT OVERWRITE TABLE dim_user
SELECT
u.user_id,
u.user_name,
CASE WHEN u.gender = 1 THEN '男' WHEN u.gender = 2 THEN '女' ELSE '未知' END AS gender,
CASE
WHEN age < 18 THEN '18 岁以下'
WHEN age < 25 THEN '18-25 岁'
WHEN age < 30 THEN '25-30 岁'
WHEN age < 40 THEN '30-40 岁'
ELSE '40 岁以上'
END AS age_group,
c.city_name AS city,
c.province_name AS province,
CASE
WHEN c.province_name IN ('北京','上海','广东','浙江') THEN '一线城市'
WHEN c.province_name IN ('江苏','福建','山东') THEN '新一线城市'
ELSE '其他城市'
END AS region,
CASE
WHEN total_gmv >= 10000 THEN '钻石会员'
WHEN total_gmv >= 5000 THEN '黄金会员'
WHEN total_gmv >= 1000 THEN '白银会员'
ELSE '普通会员'
END AS member_level,
u.register_date,
u.is_active,
u.create_time,
u.update_time
FROM ods_user_info u
LEFT JOIN ods_city c ON u.city_id = c.city_id
LEFT JOIN (
SELECT user_id, SUM(pay_amount) AS total_gmv
FROM ods_order_info
GROUP BY user_id
) o ON u.user_id = o.user_id;
2. 商品维度表
CREATE TABLE dim_product (
product_id BIGINT COMMENT '商品 ID',
product_name STRING COMMENT '商品名称',
category_level1 STRING COMMENT '一级类目',
category_level2 STRING COMMENT '二级类目',
category_level3 STRING COMMENT '三级类目',
brand STRING COMMENT '品牌',
price_band STRING COMMENT '价格带',
supplier_id BIGINT COMMENT '供应商 ID',
supplier_name STRING COMMENT '供应商名称',
is_active BOOLEAN COMMENT '是否在售',
create_time TIMESTAMP COMMENT '上架时间'
)
STORED AS ORC;
-- 数据加载
INSERT OVERWRITE TABLE dim_product
SELECT
p.product_id,
p.product_name,
c1.cat_name AS category_level1,
c2.cat_name AS category_level2,
c3.cat_name AS category_level3,
p.brand,
CASE
WHEN p.price < 100 THEN '0-100 元'
WHEN p.price < 500 THEN '100-500 元'
WHEN p.price < 1000 THEN '500-1000 元'
WHEN p.price < 5000 THEN '1000-5000 元'
ELSE '5000 元以上'
END AS price_band,
s.supplier_id,
s.supplier_name,
p.is_active,
p.create_time
FROM ods_product_info p
LEFT JOIN ods_category c3 ON p.category_id = c3.cat_id
LEFT JOIN ods_category c2 ON c3.parent_id = c2.cat_id
LEFT JOIN ods_category c1 ON c2.parent_id = c1.cat_id
LEFT JOIN ods_supplier s ON p.supplier_id = s.supplier_id;
3. 订单事务事实表
CREATE TABLE fact_order_transaction (
order_id BIGINT COMMENT '订单 ID',
order_no STRING COMMENT '订单编号',
user_id BIGINT COMMENT '用户 ID',
product_id BIGINT COMMENT '商品 ID',
order_date_id INT COMMENT '订单日期 ID',
pay_date_id INT COMMENT '支付日期 ID',
-- 度量值
product_qty INT COMMENT '商品数量',
original_amount DECIMAL(18,2) COMMENT '原始金额',
discount_amount DECIMAL(18,2) COMMENT '折扣金额',
pay_amount DECIMAL(18,2) COMMENT '实付金额',
cost_amount DECIMAL(18,2) COMMENT '成本金额',
profit_amount DECIMAL(18,2) COMMENT '利润金额'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
-- 数据加载（日增量）
INSERT OVERWRITE TABLE fact_order_transaction PARTITION (dt='2026-03-24')
SELECT
o.order_id,
o.order_no,
o.user_id,
od.product_id,
TO_DATE(o.create_time, 'yyyyMMdd') AS order_date_id,
TO_DATE(o.pay_time, 'yyyyMMdd') AS pay_date_id,
od.product_qty,
od.product_qty * od.unit_price AS original_amount,
od.discount_amount,
o.pay_amount * (od.product_qty * od.unit_price / o.total_amount) AS pay_amount,
od.product_qty * p.cost_price AS cost_amount,
o.pay_amount * (od.product_qty * od.unit_price / o.total_amount) - od.product_qty * p.cost_price AS profit_amount
FROM ods_order_info o
JOIN ods_order_detail od ON o.order_id = od.order_id
JOIN ods_product_info p ON od.product_id = p.product_id
WHERE o.dt = '2026-03-24'
AND o.order_status >= 2; -- 已支付订单
4. 订单日快照事实表
CREATE TABLE fact_order_daily_snapshot (
stat_date_id INT COMMENT '统计日期 ID',
user_id BIGINT COMMENT '用户 ID',
product_id BIGINT COMMENT '商品 ID',
city_id BIGINT COMMENT '城市 ID',
category_id BIGINT COMMENT '类目 ID',
-- 度量值（当日）
order_count_1d INT COMMENT '1 日订单数',
gmv_1d DECIMAL(18,2) COMMENT '1 日 GMV',
pay_amount_1d DECIMAL(18,2) COMMENT '1 日支付金额',
profit_1d DECIMAL(18,2) COMMENT '1 日利润',
-- 度量值（累计）
order_count_td INT COMMENT '累计订单数',
gmv_td DECIMAL(18,2) COMMENT '累计 GMV'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
-- 数据加载
INSERT OVERWRITE TABLE fact_order_daily_snapshot PARTITION (dt='2026-03-24')
SELECT
20260324 AS stat_date_id,
user_id,
product_id,
city_id,
category_id,
SUM(CASE WHEN order_date_id = 20260324 THEN 1 ELSE 0 END) AS order_count_1d,
SUM(CASE WHEN order_date_id = 20260324 THEN pay_amount ELSE 0 END) AS gmv_1d,
SUM(CASE WHEN order_date_id = 20260324 THEN pay_amount ELSE 0 END) AS pay_amount_1d,
SUM(CASE WHEN order_date_id = 20260324 THEN profit_amount ELSE 0 END) AS profit_1d,
SUM(1) AS order_count_td,
SUM(pay_amount) AS gmv_td
FROM fact_order_transaction
WHERE dt <= '2026-03-24'
GROUP BY user_id, product_id, city_id, category_id;
📊 典型分析场景
场景 1：销售分析
需求：统计各类目每日 GMV 趋势
SELECT
d.full_date AS stat_date,
p.category_level1,
p.category_level2,
COUNT(DISTINCT f.order_id) AS order_count,
SUM(f.pay_amount) AS gmv,
SUM(f.profit_amount) AS profit,
SUM(f.pay_amount) / COUNT(DISTINCT f.order_id) AS avg_order_value
FROM fact_order_transaction f
JOIN dim_date d ON f.order_date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.full_date BETWEEN '2026-03-01' AND '2026-03-24'
GROUP BY d.full_date, p.category_level1, p.category_level2
ORDER BY d.full_date, gmv DESC;
结果示例：
| stat_date | category_level1 | category_level2 | order_count | gmv | profit | avg_order_value |
|---|---|---|---|---|---|---|
| 2026-03-24 | 数码 | 手机 | 1200 | 5000000 | 500000 | 4166.67 |
| 2026-03-24 | 数码 | 配件 | 3000 | 800000 | 120000 | 266.67 |
| 2026-03-24 | 服装 | 男装 | 2000 | 400000 | 80000 | 200.00 |
场景 2：用户分析
需求：用户留存率分析（次日留存、7 日留存、30 日留存）
WITH user_first_order AS (
-- 每个用户的首单日期
SELECT
user_id,
MIN(order_date_id) AS first_order_date
FROM fact_order_transaction
GROUP BY user_id
),
user_retention AS (
-- 用户在后续日期的下单情况
SELECT
ufo.first_order_date,
f.order_date_id,
f.order_date_id - ufo.first_order_date AS days_diff,
COUNT(DISTINCT f.user_id) AS user_count
FROM user_first_order ufo
JOIN fact_order_transaction f ON ufo.user_id = f.user_id
WHERE f.order_date_id >= ufo.first_order_date
GROUP BY ufo.first_order_date, f.order_date_id
)
SELECT
first_order_date,
SUM(CASE WHEN days_diff = 0 THEN user_count ELSE 0 END) AS day0_users,
SUM(CASE WHEN days_diff = 1 THEN user_count ELSE 0 END) AS day1_users,
SUM(CASE WHEN days_diff = 7 THEN user_count ELSE 0 END) AS day7_users,
SUM(CASE WHEN days_diff = 30 THEN user_count ELSE 0 END) AS day30_users,
ROUND(SUM(CASE WHEN days_diff = 1 THEN user_count ELSE 0 END) * 100.0 /
SUM(CASE WHEN days_diff = 0 THEN user_count ELSE 0 END), 2) AS day1_retention_rate,
ROUND(SUM(CASE WHEN days_diff = 7 THEN user_count ELSE 0 END) * 100.0 /
SUM(CASE WHEN days_diff = 0 THEN user_count ELSE 0 END), 2) AS day7_retention_rate,
ROUND(SUM(CASE WHEN days_diff = 30 THEN user_count ELSE 0 END) * 100.0 /
SUM(CASE WHEN days_diff = 0 THEN user_count ELSE 0 END), 2) AS day30_retention_rate
FROM user_retention
GROUP BY first_order_date
ORDER BY first_order_date;
结果示例：
| first_order_date | day0_users | day1_users | day7_users | day30_users | day1_retention | day7_retention | day30_retention |
|---|---|---|---|---|---|---|---|
| 2026-02-01 | 1000 | 450 | 280 | 150 | 45.0% | 28.0% | 15.0% |
| 2026-02-08 | 1200 | 550 | 320 | 180 | 45.8% | 26.7% | 15.0% |
| 2026-02-15 | 1100 | 500 | 300 | 165 | 45.5% | 27.3% | 15.0% |
场景 3：商品分析
需求：商品销量排行（按 GMV、销量、利润）
SELECT
p.product_id,
p.product_name,
p.category_level2,
p.brand,
COUNT(DISTINCT f.order_id) AS order_count,
SUM(f.product_qty) AS total_qty,
SUM(f.pay_amount) AS gmv,
SUM(f.profit_amount) AS total_profit,
SUM(f.pay_amount) / SUM(f.product_qty) AS avg_price,
SUM(f.profit_amount) / SUM(f.pay_amount) * 100 AS profit_rate
FROM fact_order_transaction f
JOIN dim_product p ON f.product_id = p.product_id
WHERE f.order_date_id BETWEEN 20260301 AND 20260324
GROUP BY p.product_id, p.product_name, p.category_level2, p.brand
ORDER BY gmv DESC
LIMIT 100;
场景 4：地区分析
需求：各地区销售情况（按省份、城市）
SELECT
u.province,
u.city,
COUNT(DISTINCT f.user_id) AS user_count,
COUNT(DISTINCT f.order_id) AS order_count,
SUM(f.pay_amount) AS gmv,
SUM(f.pay_amount) / COUNT(DISTINCT f.user_id) AS arpu,
SUM(f.pay_amount) / COUNT(DISTINCT f.order_id) AS aoov
FROM fact_order_transaction f
JOIN dim_user u ON f.user_id = u.user_id
WHERE f.order_date_id BETWEEN 20260301 AND 20260324
GROUP BY u.province, u.city
ORDER BY gmv DESC
LIMIT 50;
结果示例：
| province | city | user_count | order_count | gmv | arpu | aov |
|---|---|---|---|---|---|---|
| 广东 | 深圳 | 50000 | 120000 | 25000000 | 500.00 | 208.33 |
| 广东 | 广州 | 45000 | 100000 | 20000000 | 444.44 | 200.00 |
| 北京 | 北京 | 40000 | 90000 | 18000000 | 450.00 | 200.00 |
| 上海 | 上海 | 38000 | 85000 | 17000000 | 447.37 | 200.00 |
| 浙江 | 杭州 | 30000 | 70000 | 14000000 | 466.67 | 200.00 |
⚠️ 常见坑点与解决方案
坑点 1：维度爆炸
问题：
用户维度有 50 个字段，商品维度有 80 个字段
事实表关联后，数据量膨胀 10 倍
查询慢，存储浪费
解决：
方案 1：维度拆分
- 把常用字段放主维度表
- 不常用字段放扩展维度表
方案 2：退化维度
- 订单号、发票号直接放事实表
- 避免不必要的关联
方案 3：预聚合
- 常用查询提前聚合到 DWS 层
- 减少实时计算量
坑点 2：数据不一致
问题：
订单表 GMV = 100 万
财务报表 GMV = 98 万
差异 2 万，不知道哪里错了
解决：
方案 1：统一口径
- GMV 定义：已支付订单金额（不含退款）
- 只在 DWS 层计算一次，所有报表复用
方案 2：对账机制
- 每日对比 ODS 层和 DWD 层数据量
- 差异超过阈值告警
方案 3：数据血缘
- 记录指标计算过程
- 问题可追溯
对账 SQL 示例：
-- ODS 层订单金额
SELECT SUM(pay_amount) AS gmv_ods
FROM ods_order_info
WHERE dt = '2026-03-24' AND order_status >= 2;
-- DWD 层订单金额
SELECT SUM(pay_amount) AS gmv_dwd
FROM fact_order_transaction
WHERE dt = '2026-03-24';
-- 对比
SELECT
gmv_ods,
gmv_dwd,
ABS(gmv_ods - gmv_dwd) AS diff,
ROUND(ABS(gmv_ods - gmv_dwd) * 100.0 / gmv_ods, 4) AS diff_rate
FROM (
SELECT
(SELECT SUM(pay_amount) FROM ods_order_info WHERE dt = '2026-03-24' AND order_status >= 2) AS gmv_ods,
(SELECT SUM(pay_amount) FROM fact_order_transaction WHERE dt = '2026-03-24') AS gmv_dwd
) t;
坑点 3：缓慢变化维处理不当
问题：
用户 2024 年在北京，2025 年搬到上海
分析"2024 年各地区销售情况"时
用户应该算北京还是上海？
解决：
使用拉链表（SCD Type 2）
查询时根据分析需求选择：
- 分析历史：使用当时的维度（2024 年用北京）
- 分析当前：使用最新维度（现在用上海）
查询示例：
-- 分析 2024 年销售（使用历史维度）
SELECT
u.city,
SUM(f.pay_amount) AS gmv
FROM fact_order_transaction f
JOIN dim_user_scd u ON f.user_id = u.user_id
WHERE f.order_date_id BETWEEN 20240101 AND 20241231
AND u.start_date <= '2024-12-31'
AND u.end_date >= '2024-01-01'
GROUP BY u.city;
-- 分析当前用户分布（使用最新维度）
SELECT
u.city,
COUNT(DISTINCT f.user_id) AS user_count
FROM fact_order_transaction f
JOIN dim_user_scd u ON f.user_id = u.user_id
WHERE u.is_current = true
GROUP BY u.city;
📋 最佳实践清单
维度设计
- 使用代理键而非业务主键
- 包含丰富的描述性属性
- 层次结构扁平化（省市区一行）
- 添加"未知"选项处理空值
- 时间维度预计算（工作日、节假日）
事实表设计
- 明确事实表类型（事务/快照/累积）
- 度量值要有明确含义和计算逻辑
- 退化维度减少不必要关联
- 分区字段设计合理（按天/小时）
数据质量
- 建立对账机制（ODS vs DWD）
- 统一指标口径（只在一个地方计算）
- 监控数据波动（日环比、周同比）
- 记录数据血缘（指标来源可追溯）
性能优化
- 使用 ORC/Parquet 列式存储
- 合理分区（查询时分区裁剪）
- 常用查询预聚合到 DWS 层
- 定期清理小文件
📌 总结
核心要点
| 概念 | 要点 | 推荐使用 |
|---|---|---|
| 模型选择 | 星型 vs 雪花 | 星型⭐⭐⭐⭐⭐ |
| 事实表类型 | 事务/快照/累积 | 根据业务场景 |
| 维度处理 | SCD Type 1/2/3 | 拉链表⭐⭐⭐⭐⭐ |
| 存储格式 | ORC/Parquet | ORC+Snappy |
实践原则
1. 业务驱动
从分析需求出发，而不是从数据源出发
2. 统一口径
核心指标只在一个地方计算
3. 适度冗余
用存储换性能，维度表不要过度规范化
4. 持续迭代
根据使用反馈调整模型设计
下一步
完成维度建模后：
1. 开发 ETL 任务（数据同步、清洗、加载）
2. 建立数据质量监控（对账、告警）
3. 开发 DWS 层（常用指标预聚合）
4. 开发 ADS 层（面向应用的指标表）
5. 接入 BI 工具（报表、大屏、分析）
💡 维度建模是数仓的基础，建议从第一个项目就开始规范设计！
👋 感谢阅读！
🔗 系列文章
- [01-SQL 窗口函数从入门到精通](./01-SQL 窗口函数从入门到精通.md)
- [02-Spark 性能优化 10 个技巧](./02-Spark 性能优化 10 个技巧.md)
- 03-数据仓库分层设计指南
- 04-维度建模实战（本文）
- [下一篇：Flink 实时数仓实战](./05-Flink 实时数仓实战.md)
电商企业物流数字化转型必备！快递鸟 API 接口，72 小时快速完成物流系统集成。全流程实战1V1指导，营造开放的API技术生态圈。
更多推荐
14
0- 0
已为社区贡献1条内容
所有评论(0)