一、多对多关系下“星型模型”的限制

1、无法处理类目层级过深

结构限制：星型模型的维度表是扁平的单表，一条商品记录只能对应一个类目值（或固定数量的几个字段）。

使用星型模型抽象只能将这一串抽象为一个类目，（如：“食品/饮料/碳酸饮料/可乐”）但这就会导致后面想统计其中任意一个层级时很困难

1
2
3
4
5
食品(level1)
 └ 饮料/(level2)
    └ 碳酸饮料(level3)
       └ 可乐(level4)
          └ 可口可乐 330ml(level5)

如果用星型模型来设计：

商品维度表通常是扁平化的，每个商品只有一条记录：

1
2
3
4
5
6
7
-- 星型模型的商品维度表（简化版）
CREATE TABLE dim_product_star (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    -- 类目信息被“拍平”存储在这里
    category_full_path VARCHAR(200)  -- 例如："食品/乳制品/牛奶/纯牛奶"
);

致命问题出现了：

这个表结构只能存储一个类目路径！
如果硬要存多个，只能这样设计：

1
2
3
4
5
6
7
8
9
-- 糟糕的设计：添加多个类目字段
CREATE TABLE dim_product_star_bad (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    main_category_path VARCHAR(200),    -- 主类目
    promo_category_path VARCHAR(200),   -- 促销类目
    scene_category_path VARCHAR(200)    -- 场景类目
    -- 如果还有第4个类目怎么办？再加字段？
);

这种设计的弊端：

不灵活：如果商品需要第4个类目，必须修改表结构（ALTER TABLE）。

查询困难：要找出所有属于“健康饮品”的商品，需要这样写：

1
2
3
4
SELECT * FROM dim_product_star_bad
WHERE main_category_path LIKE '%健康饮品%'
OR promo_category_path LIKE '%健康饮品%'
OR scene_category_path LIKE '%健康饮品%';

性能极差，且难以维护。

数据冗余：类目路径字符串被大量重复存储。

2、无法表达多对多的关系

在电商平台中，商品分类是复杂，这个商品可能同时属于以下类目：

主类目：食品 -> 乳制品 -> 牛奶 -> 纯牛奶
促销类目：食品 -> 饮料 -> 健康饮品（平台做活动时把它归到这里）
场景类目：早餐食材（早餐专题活动）

类目复杂
- 类目层数不固定
- 层数不固定
- 类目关系会动态变化（今天在A类目，明天可能加到B类目）
冗余巨大
- “食品/饮料/碳酸饮料” 重复出现几百万次
类目改名要更新全表
- 比如“碳酸饮料”改名为“气泡饮料”
- 数百万行要 update ❌

3、多类目统计困难

假设我们有销售事实表：

1
2
3
4
5
CREATE TABLE fct_sales (
    sale_id INT,
    product_id INT,  -- 关联商品
    sale_amount DECIMAL(10,2)
);

需求：统计每个类目的总销售额。

在星型模型中，由于一个商品只关联一个主类目（即使它实际属于多个类目），你只能：

要么只按主类目统计，忽略其他类目 → 数据不准确
要么把销售额重复计算到多个类目 → 数据虚高

示例代码（错误统计）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 错误方法1：只统计主类目（漏掉了促销类目和场景类目的贡献）
SELECT 
    p.main_category_path AS category,
    SUM(s.sale_amount) AS total_sales
FROM fct_sales s
JOIN dim_product_star_bad p ON s.product_id = p.product_id
GROUP BY p.main_category_path;

-- 错误方法2：重复计算（同一笔销售额被算了3次）
SELECT 
    '主类目' AS category_type,
    p.main_category_path AS category,
    SUM(s.sale_amount) AS total_sales
FROM fct_sales s
JOIN dim_product_star_bad p ON s.product_id = p.product_id
GROUP BY p.main_category_path

UNION ALL

SELECT 
    '促销类目' AS category_type,
    p.promo_category_path AS category,
    SUM(s.sale_amount) AS total_sales
FROM fct_sales s
JOIN dim_product_star_bad p ON s.product_id = p.product_id
WHERE p.promo_category_path IS NOT NULL
GROUP BY p.promo_category_path

-- 还要UNION场景类目...

结果：要么少算，要么多算，永远无法得到准确数据！

⚠️ 问题总结：

结构限制：星型模型的维度表是扁平的单表，一条商品记录只能对应一个类目值（或固定数量的几个字段）。
关系限制：它天然假设事实表（销售记录）和维度表（商品）是一对多关系，然后通过商品维度表连接到类目维度表。这种链式关系无法表达“一个商品→多个类目”。
业务现实：在真实电商中，商品分类是复杂的：
- 一个商品可以属于多个类目
- 不同类目可能有不同权重
- 类目关系会动态变化（今天在A类目，明天可能加到B类目）
数据准确性：星型模型要么丢失关系（只存一个主类目），要么重复计算（存多个字段但无法合理分摊指标）

二、解决方案：雪花模型 + 桥接表

现在看雪花模型如何优雅地解决这个问题：

初级解决方案

步骤1：规范化设计

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 1. 类目维度表（支持无限层级）
CREATE TABLE dim_category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50),
    parent_id INT,  -- 父类目ID，形成树形结构
    level INT
);

-- 2. 商品维度表（只存商品基本信息）
CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
    -- 注意：这里没有类目字段！
);

-- 3. 商品-类目桥接表（核心！处理多对多）
CREATE TABLE bridge_product_category (
    product_id INT,
    category_id INT,
    relation_type VARCHAR(20),  -- 关系类型：'main'主类目, 'promo'促销, 'scene'场景
    weight DECIMAL(3,2) DEFAULT 1.0,  -- 权重，用于分摊销售额
    PRIMARY KEY (product_id, category_id, relation_type)
);

步骤2：插入数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 插入类目数据
INSERT INTO dim_category VALUES 
(1, '食品', NULL, 1),
(2, '乳制品', 1, 2),
(3, '牛奶', 2, 3),
(4, '纯牛奶', 3, 4),
(5, '饮料', 1, 2),
(6, '健康饮品', 5, 3),
(7, '早餐食材', NULL, 1);  -- 注意：早餐食材可能是一级类目

-- 插入商品数据
INSERT INTO dim_product VALUES (1001, '蒙牛纯牛奶250ml', 3.5);

-- 插入桥接关系：一个商品关联多个类目
INSERT INTO bridge_product_category VALUES 
(1001, 4, 'main', 0.6),     -- 主类目：纯牛奶，权重60%
(1001, 6, 'promo', 0.3),    -- 促销类目：健康饮品，权重30%
(1001, 7, 'scene', 0.1);    -- 场景类目：早餐食材，权重10%
-- 权重总和为1.0，表示销售额将按此比例分摊

步骤3：准确统计各类目的销售额

1
2
3
4
5
6
7
8
9
-- 正确统计：按权重分摊销售额
SELECT 
    c.category_name,
    SUM(s.sale_amount * b.weight) AS allocated_sales
FROM fct_sales s
JOIN bridge_product_category b ON s.product_id = b.product_id
JOIN dim_category c ON b.category_id = c.category_id
GROUP BY c.category_name
ORDER BY allocated_sales DESC;

结果示例：

类目名称	分摊后销售额
纯牛奶	6000元（10000元×0.6）
健康饮品	3000元（10000元×0.3）
早餐食材	1000元（10000元×0.1）

总销售额仍然是10000元，但被合理地分摊到了各个类目！

拓展SCD概念

SCD（Slowly Changing Dimensions，缓慢变化维）是指数据仓库中维度表的数据会随时间缓慢变化的维度。这里的"缓慢"是相对于事实表而言的——事实表数据变化速度快（如交易记录），而维度表数据变化较慢（如用户信息、产品信息）。

典型示例：用户维度表中，用户的出生日期、住址、手机号等信息可能随时间发生变化

出现原因

业务数据的自然演变：

业务数据更新：用户修改个人信息（如搬家后更新地址）
数据修正：发现历史数据错误需要更正（如身份证号录入错误）
状态变更：产品价格调整、员工职位变动等
属性扩展：业务需求变化导致维度属性增加

如果不妥善处理这些变化，数据分析会出现以下问题：

历史事实与当前维度不匹配
无法追踪历史变化趋势
跨时间分析结果失真

应对措施

场景	推荐方案	说明
错误数据修正	Type 1	直接覆盖错误值
用户地址追踪	Type 2（拉链表）	完整历史分析
产品价格变化	Type 2或Type 3	根据分析需求选择
员工职位变动	Type 2	支持晋升路径分析

桥接表的时间化扩展

问题：今天在A类目，明天可能加到B类目

标准的桥接表需要增加时间有效性字段来处理关系的动态变化：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE TABLE bridge_product_category (
    product_id INT,
    category_id INT,
    relation_type VARCHAR(20),  -- 关系类型：'main'主类目, 'promo'促销
    weight DECIMAL(3,2) DEFAULT 1.0,  -- 权重

    -- 增加时间有效性字段！
    start_date DATE,      -- 关系生效日期
    end_date DATE,        -- 关系失效日期（'9999-12-31'表示当前有效）

    -- 记录变化时间戳
    created_at TIMESTAMP,
    updated_at TIMESTAMP,

    PRIMARY KEY (product_id, category_id, start_date)
);

实际案例：跟踪商品类目的历史变化

假设"蒙牛纯牛奶250ml"的类目关系变化如下：

2024-01-01 到 2024-03-31：属于纯牛奶类目
2024-04-01 开始：被重新分类到低温牛奶类目
2024-05-15 到 2024-06-15：添加了夏季饮品的促销类目

步骤1：初始化数据（2024-01-01）

1
2
3
4
-- 商品属于"纯牛奶"类目
INSERT INTO bridge_product_category VALUES 
(1001, 4, 'main', 1.0, '2024-01-01', '9999-12-31', '2024-01-01 00:00:00', '2024-01-01 00:00:00');
-- 商品ID=1001, 类目ID=4(纯牛奶)

步骤2：类目变更（2024-04-01）

注意：我们不能直接更新原有记录，而是需要：

关闭原有关系的有效期
添加新的关系记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 1. 关闭旧的"纯牛奶"关系
UPDATE bridge_product_category 
SET end_date = '2024-03-31',
    updated_at = '2024-04-01 10:00:00'
WHERE product_id = 1001 
  AND category_id = 4 
  AND end_date = '9999-12-31';

-- 2. 创建新的"低温牛奶"关系
INSERT INTO bridge_product_category VALUES 
(1001, 8, 'main', 1.0, '2024-04-01', '9999-12-31', '2024-04-01 10:00:00', '2024-04-01 10:00:00');
-- 新类目ID=8(低温牛奶)

步骤3：添加促销类目（2024-05-15）

1
2
3
4
-- 添加夏季促销类目（只有一个月有效期）
INSERT INTO bridge_product_category VALUES 
(1001, 9, 'promo', 0.3, '2024-05-15', '2024-06-15', '2024-05-15 09:00:00', '2024-05-15 09:00:00');
-- 类目ID=9(夏季饮品)

步骤4：查看时间线上的关系变化

现在桥接表的状态：

product_id	category_id	relation_type	weight	start_date	end_date
1001	4	main	1.0	2024-01-01	2024-03-31
1001	8	main	1.0	2024-04-01	9999-12-31
1001	9	promo	0.3	2024-05-15	2024-06-15

这才是桥接表的强大之处！我们可以查询任意时间点的商品-类目关系：

适用场景

场景1：查询2024-02-15（春节促销）的商品类目

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT 
    p.product_name,
    c.category_name,
    b.relation_type,
    b.weight
FROM dim_product p
JOIN bridge_product_category b ON p.product_id = b.product_id
JOIN dim_category c ON b.category_id = c.category_id
WHERE p.product_id = 1001
  AND '2024-02-15' BETWEEN b.start_date AND b.end_date;

-- 结果：
-- 蒙牛纯牛奶250ml | 纯牛奶 | main | 1.0
-- 只有纯牛奶类目，此时还未变更

场景2：查询2024-05-20（夏季促销）的商品类目

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT 
    p.product_name,
    c.category_name,
    b.relation_type,
    b.weight
FROM dim_product p
JOIN bridge_product_category b ON p.product_id = b.product_id
JOIN dim_category c ON b.category_id = c.category_id
WHERE p.product_id = 1001
  AND '2024-05-20' BETWEEN b.start_date AND b.end_date;

-- 结果：
-- 蒙牛纯牛奶250ml | 低温牛奶 | main   | 1.0
-- 蒙牛纯牛奶250ml | 夏季饮品 | promo  | 0.3
-- 有两个类目关系同时生效！

场景3：统计2024年Q1（1-3月）各类目的销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT 
    c.category_name,
    SUM(s.sale_amount * b.weight) AS allocated_sales
FROM fct_sales s
-- 关键：关联时要确保销售时间在关系有效期内
JOIN bridge_product_category b ON s.product_id = b.product_id
  AND s.sale_date BETWEEN b.start_date AND b.end_date
JOIN dim_category c ON b.category_id = c.category_id
WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY c.category_name;

-- 结果：所有销售额都会归到"纯牛奶"类目

如何处理复杂的权重变化？

有时候，商品在不同类目间的权重也会随时间变化。比如：

1-3月：主类目权重100%
4月开始：主类目60%，促销类目40%

这需要在变更时创建两条新记录：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 2024-04-01变更权重
-- 1. 关闭旧的单一权重关系
UPDATE bridge_product_category 
SET end_date = '2024-03-31'
WHERE product_id = 1001 
  AND end_date = '9999-12-31';

-- 2. 创建新的多权重关系
INSERT INTO bridge_product_category VALUES 
(1001, 8, 'main', 0.6, '2024-04-01', '9999-12-31', NOW(), NOW()),
(1001, 9, 'promo', 0.4, '2024-04-01', '9999-12-31', NOW(), NOW());

为什么桥接表的时间化拓展的设计是优秀的？

1. 历史可追溯

可以查询任意历史时间点的类目归属关系。比如审计时想知道"2023年双十一期间，这个商品属于哪个类目"。

2. 时间一致性

确保销售事实与当时的维度关系一致。今天做的报表和历史报表在逻辑上是一致的。

3. 变更无影响

增加新类目：直接插入新记录
移除旧类目：将原记录的end_date设为昨天
修改权重：关闭旧记录，插入新权重的新记录
所有历史报表不受影响

4. 支持复杂的业务场景

季节性类目（如"夏季特饮"只存在6-8月）
促销活动类目（如"双十一专区"只在活动期间有效）
测试分组（A/B测试时，不同用户看到不同类目）

实际查询模板

模板1：获取当前有效的类目关系

1
2
SELECT * FROM bridge_product_category
WHERE CURRENT_DATE BETWEEN start_date AND end_date;

模板2：生成类目历史变化时间线

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT 
    p.product_name,
    c.category_name,
    b.relation_type,
    b.start_date,
    b.end_date,
    -- 计算持续时间
    DATEDIFF(
        IF(b.end_date = '9999-12-31', CURRENT_DATE, b.end_date),
        b.start_date
    ) + 1 AS duration_days
FROM bridge_product_category b
JOIN dim_product p ON b.product_id = p.product_id
JOIN dim_category c ON b.category_id = c.category_id
WHERE p.product_id = 1001
ORDER BY b.start_date;

三、完整的数仓分层设计

以下是基于商品订单的完整分层设计：

1. ODS层（操作数据存储）

目标：原样同步，保留历史，快速恢复

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 1.1 订单表（增量同步，按天分区）
CREATE TABLE ods_order_inc (
    order_id BIGINT COMMENT '订单ID',
    user_id BIGINT COMMENT '用户ID',
    product_id BIGINT COMMENT '商品ID',
    quantity INT COMMENT '购买数量',
    amount DECIMAL(10,2) COMMENT '订单金额',
    order_time TIMESTAMP COMMENT '订单时间',
    -- 原样字段
    order_status INT COMMENT '订单状态',
    payment_type INT COMMENT '支付方式',
    shipping_address STRING COMMENT '收货地址',
    -- 技术字段
    dt STRING COMMENT '分区字段(yyyy-MM-dd)',
    update_time TIMESTAMP COMMENT '更新时间',
    data_source STRING COMMENT '数据源标识'
) PARTITIONED BY (dt)
STORED AS ORC;

-- 1.2 商品表（全量/增量，视业务而定）
CREATE TABLE ods_product (
    product_id BIGINT COMMENT '商品ID',
    product_name STRING COMMENT '商品名称',
    sku_code STRING COMMENT 'SKU编码',
    price DECIMAL(10,2) COMMENT '价格',
    category_id BIGINT COMMENT '当前类目ID（业务系统的当前值）',
    brand_id BIGINT COMMENT '品牌ID',
    is_active TINYINT COMMENT '是否有效',
    create_time TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP COMMENT '更新时间',
    dt STRING COMMENT '分区字段(yyyy-MM-dd)'
) PARTITIONED BY (dt)
STORED AS ORC;

-- 1.3 类目表（通常全量）
CREATE TABLE ods_category (
    category_id BIGINT COMMENT '类目ID',
    category_name STRING COMMENT '类目名称',
    parent_id BIGINT COMMENT '父类目ID',
    level INT COMMENT '层级',
    sort_order INT COMMENT '排序',
    is_leaf TINYINT COMMENT '是否叶子节点',
    dt STRING COMMENT '分区字段(yyyy-MM-dd)'
) PARTITIONED BY (dt)
STORED AS ORC;

2. DWD层（数据仓库明细层）

目标：清洗、规范化、构建维度模型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- 2.1 时间维度表（经典维度表）
CREATE TABLE dim_date (
    date_sk INT COMMENT '代理键',
    date_str STRING COMMENT '日期(yyyy-MM-dd)',
    year INT COMMENT '年',
    quarter INT COMMENT '季度',
    month INT COMMENT '月',
    day INT COMMENT '日',
    week_of_year INT COMMENT '周数',
    is_weekend TINYINT COMMENT '是否周末',
    holiday_flag TINYINT COMMENT '是否节假日',
    PRIMARY KEY (date_sk)
);

-- 2.2 商品维度表（缓慢变化维Type 2）
CREATE TABLE dim_product (
    product_sk BIGINT COMMENT '商品代理键',
    product_id BIGINT COMMENT '商品业务ID',
    product_name STRING COMMENT '商品名称',
    sku_code STRING COMMENT 'SKU编码',
    price DECIMAL(10,2) COMMENT '价格',
    brand_id BIGINT COMMENT '品牌ID',
    brand_name STRING COMMENT '品牌名称',
    -- 有效时间范围
    start_date DATE COMMENT '生效日期',
    end_date DATE COMMENT '失效日期',
    is_current TINYINT COMMENT '是否当前有效',
    create_time TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (product_sk)
);

-- 2.3 类目维度表（雪花模型，支持层级变化）
CREATE TABLE dim_category (
    category_sk BIGINT COMMENT '类目代理键',
    category_id BIGINT COMMENT '类目业务ID',
    category_name STRING COMMENT '类目名称',
    parent_sk BIGINT COMMENT '父类目代理键',
    level INT COMMENT '层级',
    full_path STRING COMMENT '完整路径',
    -- 缓慢变化维字段
    start_date DATE COMMENT '生效日期',
    end_date DATE COMMENT '失效日期',
    is_current TINYINT COMMENT '是否当前有效',
    PRIMARY KEY (category_sk)
);

-- 2.4 商品-类目桥接表（处理多对多和历史变化）
CREATE TABLE bridge_product_category (
    product_sk BIGINT COMMENT '商品代理键',
    category_sk BIGINT COMMENT '类目代理键',
    relation_type STRING COMMENT '关系类型(main/promo/scene)',
    weight DECIMAL(3,2) COMMENT '权重',
    -- 时间有效性
    start_date DATE COMMENT '生效日期',
    end_date DATE COMMENT '失效日期',
    is_current TINYINT COMMENT '是否当前有效',
    PRIMARY KEY (product_sk, category_sk, start_date)
);

-- 2.5 订单事实表（事务型事实表）
CREATE TABLE fct_order (
    order_sk BIGINT COMMENT '订单代理键',
    order_id BIGINT COMMENT '订单业务ID',
    date_sk INT COMMENT '日期代理键',
    product_sk BIGINT COMMENT '商品代理键',
    user_sk BIGINT COMMENT '用户代理键',
    quantity INT COMMENT '购买数量',
    amount DECIMAL(10,2) COMMENT '订单金额',
    order_status INT COMMENT '订单状态',
    payment_type INT COMMENT '支付方式',
    -- 退化维度
    shipping_city STRING COMMENT '收货城市',
    shipping_province STRING COMMENT '收货省份',
    create_time TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (order_sk)
);

3. DWS层（数据仓库汇总层）⭐

目标：反规范化，构建星型宽表，提升查询性能

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- 3.1 商品销售日汇总宽表（星型模型）
CREATE TABLE dws_product_sales_di (
    -- 日期维度（冗余）
    date_sk INT COMMENT '日期代理键',
    date_str STRING COMMENT '日期',
    year INT COMMENT '年',
    month INT COMMENT '月',
    day INT COMMENT '日',
    is_weekend TINYINT COMMENT '是否周末',

    -- 商品维度（冗余）
    product_sk BIGINT COMMENT '商品代理键',
    product_id BIGINT COMMENT '商品ID',
    product_name STRING COMMENT '商品名称',
    sku_code STRING COMMENT 'SKU编码',
    price DECIMAL(10,2) COMMENT '价格',
    brand_id BIGINT COMMENT '品牌ID',
    brand_name STRING COMMENT '品牌名称',

    -- 类目维度（展平冗余）
    leaf_category_sk BIGINT COMMENT '叶子类目代理键',
    leaf_category_name STRING COMMENT '叶子类目名称',
    -- 层级展平（假设最多5级）
    l1_category_sk BIGINT COMMENT '一级类目代理键',
    l1_category_name STRING COMMENT '一级类目名称',
    l2_category_sk BIGINT COMMENT '二级类目代理键',
    l2_category_name STRING COMMENT '二级类目名称',
    l3_category_sk BIGINT COMMENT '三级类目代理键',
    l3_category_name STRING COMMENT '三级类目名称',
    l4_category_sk BIGINT COMMENT '四级类目代理键',
    l4_category_name STRING COMMENT '四级类目名称',
    l5_category_sk BIGINT COMMENT '五级类目代理键',
    l5_category_name STRING COMMENT '五级类目名称',

    -- 销售指标
    order_count BIGINT COMMENT '订单数',
    sale_quantity BIGINT COMMENT '销售数量',
    sale_amount DECIMAL(20,2) COMMENT '销售金额',
    user_count BIGINT COMMENT '购买用户数',

    -- 衍生指标
    avg_order_amount DECIMAL(10,2) COMMENT '客单价',
    conversion_rate DECIMAL(5,4) COMMENT '转化率',

    -- 分区字段
    dt STRING COMMENT '分区字段(yyyy-MM-dd)'
) PARTITIONED BY (dt)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 3.2 类目销售周汇总宽表
CREATE TABLE dws_category_sales_wi (
    -- 时间维度
    week_sk INT COMMENT '周代理键',
    year INT COMMENT '年',
    week_of_year INT COMMENT '周数',

    -- 类目维度（展平）
    category_sk BIGINT COMMENT '类目代理键',
    category_name STRING COMMENT '类目名称',
    level INT COMMENT '层级',
    parent_sk BIGINT COMMENT '父类目代理键',
    parent_name STRING COMMENT '父类目名称',

    -- 聚合指标
    order_count BIGINT COMMENT '订单数',
    sale_quantity BIGINT COMMENT '销售数量',
    sale_amount DECIMAL(20,2) COMMENT '销售金额',
    sale_amount_mom DECIMAL(8,4) COMMENT '环比增长率',
    sale_amount_yoy DECIMAL(8,4) COMMENT '同比增长率',

    -- 分区字段
    dt STRING COMMENT '分区字段(yyyy-MM-dd)'
) PARTITIONED BY (dt)
STORED AS ORC;

4. ADS层（应用数据服务）

目标：面向应用，直接供给报表/接口

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 4.1 类目销售排行榜（供BI展示）
CREATE TABLE ads_category_rank_di (
    date_str STRING COMMENT '日期',
    category_name STRING COMMENT '类目名称',
    level INT COMMENT '层级',
    sale_amount DECIMAL(20,2) COMMENT '销售额',
    sale_amount_rank INT COMMENT '销售额排名',
    sale_amount_percent DECIMAL(5,2) COMMENT '销售额占比%',
    mom_growth_rate DECIMAL(8,4) COMMENT '环比增长率',
    dt STRING COMMENT '分区字段'
) PARTITIONED BY (dt)
STORED AS PARQUET;

-- 4.2 商品销售明细报表（供运营使用）
CREATE TABLE ads_product_sales_report_di (
    date_str STRING COMMENT '日期',
    product_id BIGINT COMMENT '商品ID',
    product_name STRING COMMENT '商品名称',
    category_full_path STRING COMMENT '类目全路径',
    sale_quantity BIGINT COMMENT '销售数量',
    sale_amount DECIMAL(20,2) COMMENT '销售额',
    inventory_count BIGINT COMMENT '库存数量',
    stock_days INT COMMENT '可售天数',
    dt STRING COMMENT '分区字段'
) PARTITIONED BY (dt)
STORED AS PARQUET;

数据处理流程示例

从DWD到DWS的ETL过程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 构建DWS商品销售日汇总宽表
INSERT OVERWRITE TABLE dws_product_sales_di PARTITION(dt='${date}')
SELECT 
    -- 日期维度
    d.date_sk,
    d.date_str,
    d.year,
    d.month,
    d.day,
    d.is_weekend,

    -- 商品维度
    p.product_sk,
    p.product_id,
    p.product_name,
    p.sku_code,
    p.price,
    p.brand_id,
    p.brand_name,

    -- 类目维度（展平处理）
    bc.category_sk as leaf_category_sk,
    cat_leaf.category_name as leaf_category_name,
    -- 递归获取上级类目（实际生产中可用递归CTE或UDF）
    get_category_by_level(cat_leaf.category_sk, 1) as l1_category_sk,
    get_category_by_level(cat_leaf.category_sk, 1) as l1_category_name,
    get_category_by_level(cat_leaf.category_sk, 2) as l2_category_sk,
    get_category_by_level(cat_leaf.category_sk, 2) as l2_category_name,
    get_category_by_level(cat_leaf.category_sk, 3) as l3_category_sk,
    get_category_by_level(cat_leaf.category_sk, 3) as l3_category_name,

    -- 销售指标
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(o.quantity) as sale_quantity,
    SUM(o.amount) as sale_amount,
    COUNT(DISTINCT o.user_sk) as user_count,

    -- 衍生指标
    SUM(o.amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) as avg_order_amount

FROM fct_order o
-- 关联时间维度
JOIN dim_date d ON o.date_sk = d.date_sk
-- 关联商品维度（取当前有效的商品）
JOIN dim_product p ON o.product_sk = p.product_sk 
  AND o.date_sk BETWEEN p.start_date AND p.end_date
  AND p.is_current = 1
-- 关联桥接表（取当前有效的主类目）
JOIN bridge_product_category bc ON o.product_sk = bc.product_sk
  AND o.date_sk BETWEEN bc.start_date AND bc.end_date
  AND bc.is_current = 1
  AND bc.relation_type = 'main'
-- 关联叶子类目
JOIN dim_category cat_leaf ON bc.category_sk = cat_leaf.category_sk
  AND o.date_sk BETWEEN cat_leaf.start_date AND cat_leaf.end_date
  AND cat_leaf.is_current = 1
-- 通过UDF获取各层上级类目
LEFT JOIN dim_category cat_l1 ON get_parent_category_sk(cat_leaf.category_sk, 1) = cat_l1.category_sk
  AND o.date_sk BETWEEN cat_l1.start_date AND cat_l1.end_date
  AND cat_l1.is_current = 1
-- ... 类似关联其他层级

WHERE d.date_str = '${date}'
GROUP BY 
    d.date_sk, d.date_str, d.year, d.month, d.day, d.is_weekend,
    p.product_sk, p.product_id, p.product_name, p.sku_code, p.price, p.brand_id, p.brand_name,
    bc.category_sk, cat_leaf.category_name,
    -- 各层类目字段...
;

查询性能对比

雪花模型查询（DWD层）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 查询某个一级类目下所有商品的销售额（需要多层JOIN）
SELECT 
    cat_l1.category_name as level1_category,
    SUM(o.amount) as total_sales
FROM fct_order o
JOIN dim_product p ON o.product_sk = p.product_sk
JOIN bridge_product_category bpc ON p.product_sk = bpc.product_sk
JOIN dim_category cat_leaf ON bpc.category_sk = cat_leaf.category_sk
JOIN dim_category cat_l2 ON cat_leaf.parent_sk = cat_l2.category_sk
JOIN dim_category cat_l1 ON cat_l2.parent_sk = cat_l1.category_sk
WHERE o.date_sk BETWEEN 20240101 AND 20240131
  AND cat_l1.category_name = '食品'
GROUP BY cat_l1.category_name;
-- 执行时间：~5-10秒（5-6个JOIN）

星型模型查询（DWS层）

1
2
3
4
5
6
7
8
9
-- 同样的查询，在DWS层只需要单表扫描
SELECT 
    l1_category_name as level1_category,
    SUM(sale_amount) as total_sales
FROM dws_product_sales_di
WHERE dt BETWEEN '2024-01-01' AND '2024-01-31'
  AND l1_category_name = '食品'
GROUP BY l1_category_name;
-- 执行时间：~0.5-1秒（无JOIN，只需单表聚合）

分层架构总结

分层	模型	核心目标	数据特征	用户	存储策略
ODS	原始模型	数据同步、历史备份	原始数据、无清洗	数据工程师	全量/增量
DWD	雪花模型	数据质量、一致性、灵活性	规范化、明细数据	数据开发	分区+列存
DWS	星型模型	查询性能、易用性	反规范化、汇总数据	分析师/业务	宽表+索引
ADS	应用模型	业务适配、接口性能	高度聚合、应用定制	应用/报表	缓存+分区

关键设计原则：

DWD层保持灵活性：用雪花模型支持多对多、历史变化、层级可变
DWS层追求性能：用星型模型预关联、预计算，空间换时间
层层递进：ODS → DWD（清洗） → DWS（汇总） → ADS（应用）
适度冗余：在DWS层合理冗余维度属性，避免过度规范化
数据复用：DWS层宽表可被多个ADS应用复用