ADB-05 数据仓库与 OLAP

📅 预计 70 分钟　|　⭐ = 重要知识点　|　📌 中英术语见文末
🛠 维度建模建表示例基于 SQLite，可直接运行

---

5.1 数据仓库的概念与四大特征 ⭐

生活比喻：账本与档案室

超市的业务系统是日常收银台——每笔扫码实时写进销售流水，这是"账本"，记"正在发生的事"。数据仓库是楼上的档案室——每晚打烊后把流水整理归并、装订存档，回答的不是"这笔收了多少钱"，而是"这个季度卖得最好的是什么"。数据库管当下，数据仓库管历史。

数据仓库的定义

数据仓库（data warehouse）是面向主题、集成、非易失、随时间变化的数据集合，用于支持管理决策。这个经典定义出自 W. H. Inmon。它强调的不是"能存多少数据"，而是组织方式与服务目标——为分析决策服务，而非日常事务。

特征一：面向主题（subject-oriented）

"主题"是分析关注的核心对象，如"销售""顾客"。业务表按功能流程组织（下单、支付、发货三张表），数据仓库按主题组织：把跟"销售"有关的字段全部聚拢到一张主题表里，分析员不用自己跨表 JOIN。

特征二：集成（integrated）

数据来自多个系统，命名、单位、编码各说各话——A 系统性别存 M/F，B 系统存 0/1。数据仓库在进入前必须统一编码、单位、命名，消除冲突。例如三家门店的"销售额"分别以元、万元、千元记录，进仓前统一折算为元。集成是质量的生命线，未经集成只是"数据垃圾场"。

特征三：非易失（non-volatile）

业务库天天 UPDATE、DELETE；数据仓库只进不改——数据加载后一般不再删除或修改，每次装载是追加新数据而非覆盖。去年 3 月的销售额一旦入库永远保持当时的数字，出错只能补录修正记录，不能改旧值。

特征四：随时间变化（time-variant）

每条数据都带时间维度，反映某时间点的状态。业务库只关心当前状态（顾客搬家就覆盖旧地址），数据仓库保留"2023 年住址""2025 年住址"两条记录，能看出流动轨迹。

⚠️ 常见错误

1. 把数据仓库当成更大的数据库：它是"组织方式与服务目标完全不同的分析库"，加外键约束、频繁增删改是用错了地方。
2. 把"实时更新"当特征：实时更新是业务库（OLTP）的特征，数据仓库是非易失 + 周期性装载。
3. 以为集成就是拼表：集成还包括统一编码、单位、命名等语义工作，不做集成就是垃圾进仓。

---

5.2 数据仓库 vs 数据库：OLTP 与 OLAP ⭐

生活比喻：收银员与分析师

OLTP（联机事务处理）像收银员：服务每位顾客，要快、要准、立刻响应。OLAP（联机分析处理）像数据分析师：对着整季度数据慢慢盘问，不着急但一次要看一大片。

两组概念

• OLTP（On-Line Transaction Processing）：面向业务操作，频繁增删改查，强调实时性与一致性。如银行取款、订单提交。
• OLAP（On-Line Analytical Processing）：面向决策分析，对大量历史数据做多维聚合查询，强调查询与分析能力。如"各区域各季度销售额对比"。

完整对比表 ⭐

对比项	OLTP（业务数据库）	OLAP（数据仓库）
面向	业务操作 / 事务	决策分析 / 主题
数据内容	当前状态，实时更新	历史快照，批量装载
数据操作	频繁增删改查	基本只读 + 批量追加
查询特征	简单、单行、高并发	复杂、海量扫描、多维聚合
响应要求	秒级实时	分钟级可接受
数据粒度	细（单条记录）	粗（汇总聚合）
典型操作	INSERT / UPDATE / DELETE	SELECT + GROUP BY 聚合
服务对象	一线业务人员	管理层、分析人员

💡 口诀：业务天天改，分析只读不写；业务查一条，分析扫一片。

⚠️ 常见错误

1. 以为 OLAP 只是个标签：它对应一整套多维模型与五种基本操作（见 5.4–5.6）。
2. 以为 OLTP 也做批量导入：OLTP 是单笔实时写入，批量导入是 OLAP 的装载方式。
3. 对比表记反：谁面向业务、谁频繁增删改，按"收银员 vs 分析师"对应就不会错。

---

5.3 数据仓库体系结构与 ETL ⭐

生活比喻：自来水厂

体系像自来水厂：水源（数据源）五花八门，水厂先抽取原水，再沉淀、净化，最后加压输送到千家万户。中间那道净化工序就是 ETL。

体系结构总览

数据源 ──> ETL ──> 数据仓库 ──> 数据集市 ──> OLAP / 报表展示

• 数据源：业务数据库、文件、日志等，分散在各系统。
• ETL：连接数据源与仓库的桥梁。
• 数据仓库：企业级、面向主题的中央存储。
• 数据集市（data mart）：数据仓库的子集，按部门裁剪（如销售、财务），让部门分析更快更聚焦。
• OLAP 服务器 / 报表：面向用户的分析接口。

ETL 的三个阶段

① 抽取（Extract）：从各数据源取出需要的数据，难点是数据源格式五花八门，且不能影响源系统正常运行。

② 转换（Transform）：对数据做清洗与加工，最花功夫：去重、剔除无效记录、统一编码与单位、派生新字段（如从下单时间拆出年/季/月）、按主题粒度预聚合。

③ 加载（Load）：把转换好的数据写入数据仓库，一般按固定周期（如每日凌晨）批量追加。

💡 ETL 顺序固定：先抽取、再转换、最后加载。脏数据必须先清洗才能进仓，所以转换在加载之前。

⚠️ 常见错误

1. 把 ETL 顺序记反：常见误区是"抽取→加载→转换"或写成 ELT；绝大多数架构是先转换后加载。
2. 以为"清洗"是第四阶段：清洗、去重、统一单位都归属转换阶段，ETL 只有三阶段。
3. 把数据集市当成平行系统：数据集市是数据仓库的子集，按部门裁剪而来。

---

5.4 维度建模：事实表与维度表 ⭐

生活比喻：超市购物小票

小票上半部分是事实：买了什么、几件、多少钱（度量）；抬头是维度：日期、门店、收银员。维度建模就是"小票思维"——事实表记"发生了什么、多少量"，维度表记"从哪个角度看"。

两个核心角色

• 事实表（fact table）：存放度量值（measure）——可加和的数字，如销售额、数量、利润，外加一串指向维度表的外键。
• 维度表（dimension table）：存放描述信息，如时间、地区、商品。

判断技巧：能加起来的数字在事实表，描述性的文字在维度表。

星型模型（star schema）

事实表在中心，维度表围一圈，通过外键相连，形状像星星。

• 优点：结构简单、JOIN 少、查询快。
• 缺点：维度表有冗余（如省份在多行重复）。

雪花模型（snowflake schema）

雪花模型把维度表进一步规范化，将可拆分的维度拆成多张表（如地区拆成城市表 + 省份表）。

• 优点：消除冗余、省存储。
• 缺点：JOIN 变多、查询慢、模型复杂。

取舍：星型用得多——分析系统"读多写少"，宁可多花存储换速度；雪花适合存储紧张、维度层级深的场景。

SQLite 建表示例（可直接运行）

-- 维度表：时间（年→季度→月）
CREATE TABLE dim_time (
    time_id  INTEGER PRIMARY KEY,
    year     INTEGER, quarter INTEGER, month INTEGER
);
-- 维度表：地区（省→市）
CREATE TABLE dim_region (
    region_id INTEGER PRIMARY KEY, province TEXT, city TEXT
);
-- 维度表：商品（类目→商品名）
CREATE TABLE dim_product (
    product_id INTEGER PRIMARY KEY, category TEXT, name TEXT
);
-- 事实表：销售事实（中心表：度量值 + 指向各维度的外键）
CREATE TABLE fact_sales (
    sales_id   INTEGER PRIMARY KEY,
    time_id    INTEGER REFERENCES dim_time(time_id),
    region_id  INTEGER REFERENCES dim_region(region_id),
    product_id INTEGER REFERENCES dim_product(product_id),
    quantity   INTEGER,   -- 销售数量（度量）
    amount     REAL       -- 销售额（度量）
);

INSERT INTO dim_time VALUES (1,2025,1,1),(2,2025,1,2),(3,2025,2,4);
INSERT INTO dim_region VALUES (1,'广东','深圳'),(2,'广东','广州');
INSERT INTO dim_product VALUES (1,'饮料','可乐'),(2,'零食','薯片');
INSERT INTO fact_sales VALUES
  (1,1,1,1,100,300.0),(2,2,1,1,120,360.0),(3,3,2,2,50,150.0);
-- 输出: 插入成功；一条事实=一个度量事件，如 时间1×深圳×可乐 卖 100 件、300 元

💡 事实表用外键"指向"维度表，查询时 JOIN 起来就能按任意维度聚合度量值——这是 5.5、5.6 多维查询的基础。

⚠️ 常见错误

1. 把"商品名"放进事实表：描述性信息属维度表，塞进事实表会膨胀且无法正确聚合。
2. 星型 vs 雪花记混：星型=维度表不拆分（冗余但快）；雪花=维度表拆分规范化（省空间但慢）。
3. 事实表漏建外键：没有指向维度表的外键，就无法按维度 JOIN 聚合。

---

5.5 数据立方体 ⭐

生活比喻：魔方

三阶魔方每个轴是一种"维度"，每个小格是"度量值"。数据立方体（data cube）就是这个思想的多维版——每个轴是一个维，格子里是度量值。维度不限于三个，N 维仍叫"立方体"。

三个基本概念

① 维（dimension）：观察数据的角度，如时间、地区、商品。

② 维层次（hierarchy）：维度内从上到下的细化层级，层级越高粒度越粗。

时间维层次：年 ──> 季度 ──> 月 ──> 日
地区维层次：省 ──> 市

③ 度量（measure）：格子里的数值，如销售额，可按任意维聚合（加和、平均、计数）。

立方体如何对应多维查询

"时间 × 地区 × 商品"三维立方体，格子 (2025 年, 广东, 饮料) 存的就是"2025 年广东饮料销售额"。任何多维查询都是在立方体上取子区域并聚合：固定时间、地区两个维，对商品维全聚合，就是"2025 年广东总销售额"。

数据仓库里没有真魔方，而是把事实表 + 维度表组织成星型结构，用 GROUP BY 按维聚合，效果等价于"切立方体"。

⚠️ 常见错误

1. 把"维"和"度量"搞反：维是角度（文字），度量是数值（可加和）。"深圳"是维，"300 元"是度量。
2. 以为立方体只能三维：N 维同样叫 data cube，只是难以可视化。
3. 维层次方向搞反：上卷往粗走（月→年），下钻往细走（年→月）。

---

5.6 OLAP 基本操作 ⭐

生活比喻：翻一本销售地图册

把销售数据想成地图册：放大某页看细节（下钻），退到整页俯瞰（上卷）；只看"广东"这一页（切片），只翻"华南五省"（切块）；横过来看（旋转）。五种操作就是这本地图册的五个读法。

① 上卷（roll-up）⭐

沿维层次向上聚合，粒度变粗：从月汇总到季、从季汇总到年。

-- 上卷：从"按月看"聚合到"按年看"
SELECT d.year, SUM(f.amount) AS 年销售额
FROM fact_sales f JOIN dim_time d ON f.time_id = d.time_id
GROUP BY d.year;
-- 输出: 2025  810.0   （原分月显示，现合并为年度一行）

② 下钻（drill-down）

沿维层次向下细化，与上卷互逆。

-- 下钻：从"按年看"细化到"按季度看"
SELECT d.year, d.quarter, SUM(f.amount)
FROM fact_sales f JOIN dim_time d ON f.time_id = d.time_id
GROUP BY d.year, d.quarter;
-- 输出: 2025 1 660.0（1月+2月）  2025 2 150.0（4月）

分步推演：上卷与下钻只是改 GROUP BY 的列——上卷删列（月→年），下钻加细（年→季度）。维层次有多少级，就能上下钻多少层。

③ 切片（slice）

固定某一维的一个取值，切下一片"薄片"。

-- 固定 时间维 = 2025 年，看各季度
SELECT d.quarter, SUM(f.amount)
FROM fact_sales f JOIN dim_time d ON f.time_id = d.time_id
WHERE d.year = 2025
GROUP BY d.quarter;
-- 输出: 1 660.0  2 150.0

分步推演：先用 WHERE d.year = 2025 把时间维固定为"一个值"，再从剩下的季度维做 GROUP BY 聚合——立方体被切去其他年份，只剩"2025 年"这一片薄片。

④ 切块（dice）

固定某一维的一个区间（或同时固定多个维），切出一块"立方块"。

-- 固定 时间维区间 [2025, 2026]，按 季度×城市 看
SELECT d.year, d.quarter, r.city, SUM(f.amount)
FROM fact_sales f
JOIN dim_time   d ON f.time_id   = d.time_id
JOIN dim_region r ON f.region_id = r.region_id
WHERE d.year BETWEEN 2025 AND 2026
GROUP BY d.year, d.quarter, r.city;
-- 输出: 2025 1 深圳 660.0  2025 2 广州 150.0

分步推演：先用 WHERE d.year BETWEEN 2025 AND 2026 把时间维限定在一个区间，再同时按季度、城市两个维聚合——切出的是一块含多值、多条件的"立方块"，比切片范围更大。

切片 vs 切块：切片是"一个值"（year = 2025），切块是"一个范围"（year BETWEEN 2025 AND 2026）或同时限定多个维。

⑤ 旋转（pivot）

交换观察维度——不改变数据内容，只改变"先看哪个角度、再看哪个角度"。

-- 旋转前：先时间后地区（行首是月份）
SELECT d.month, r.city, SUM(f.amount)
FROM fact_sales f JOIN dim_time   d ON f.time_id   = d.time_id
                  JOIN dim_region r ON f.region_id = r.region_id
GROUP BY d.month, r.city;

-- 旋转后：先地区后时间（行首变成城市）
SELECT r.city, d.month, SUM(f.amount)
FROM fact_sales f JOIN dim_time   d ON f.time_id   = d.time_id
                  JOIN dim_region r ON f.region_id = r.region_id
GROUP BY r.city, d.month;
-- 数据没变，只是维度次序换了，行列发生"转置"

分步推演：把 GROUP BY 的列顺序从 (month, city) 换成 (city, month)，结果就从"月份做行、城市做列"变成"城市做行、月份做列"。

💡 五个操作一句话：上卷向下聚合，下钻向下细化，切片取一个值，切块取一个区间，旋转换角度看。

⚠️ 常见错误

1. 切片与切块混用：切片=一个值，切块=一个区间；范围更宽、条件更多的是切块。
2. 上卷下钻方向搞反：沿层次向上（月→年）是上卷，向下（年→月）是下钻。
3. 以为旋转改变数据：旋转只改观察维度次序，数据与聚合结果不变。

---

5.7 从 OLAP 到数据挖掘

生活比喻：从"查地图"到"发现新路"

OLAP 像拿地图册查路线——问题是你提出的，系统只是算答案："去年华东区卖了多少？"数据挖掘（data mining）像勘探队——没有明确问题，进山找矿脉："周末晚上、江南、30 岁以下人群买奶茶最多。"

两种工作模式的区别

	OLAP	数据挖掘
驱动方式	用户驱动（先有疑问再查）	数据驱动（找规律）
结果	验证已知（confirm）	发现未知（discover）
典型问题	"各区域销售额是多少？"	"哪些因素最影响销售额？"
工作方式	上卷/下钻/切片/切块/旋转	分类、聚类、关联规则、预测

一句话：OLAP 是"查已知"，数据挖掘是"发现未知"。 OLAP 建立的多维数据仓库是数据挖掘的优质底座——数据干净、按主题组织、历史完整。数据挖掘详细内容（分类、聚类、关联规则、预测）在 ADB-06 展开。

⚠️ 常见错误

1. 以为 OLAP 能"自己发现问题"：OLAP 只回答用户指定的查询，不会主动告诉你"没问但有用的事"。
2. 把数据挖掘当更大号的 OLAP：OLAP 验证已知，数据挖掘生成新假设并建模预测。
3. 忽略数据仓库是数据挖掘的底座：直接拿未清洗业务库做挖掘，结果常被脏数据带偏。

---

🧠 记忆口诀

1. 数据仓库四大特征：主集非随——面向主题、集成、非易失、随时间变化。
2. OLTP vs OLAP：业务天天改，分析只读不写；业务查一条，分析扫一片。
3. 体系结构一条链：源 → 抽 → 转 → 载 → 仓 → 集市 → 展示。
4. ETL 三阶段：抽、转、载——顺序不能反。
5. 事实 vs 维度：能加起来的数字在事实表，描述性的文字在维度表。
6. 星型 vs 雪花：星星一张表，雪花拆细表。
7. OLAP 五操作：上卷下钻，切片切块，旋转看。

⭐ 考点清单

1. 数据仓库四大特征及每条的含义与例子。
2. OLTP 与 OLAP 对比：服务对象、数据操作、查询特征、响应要求。
3. 体系结构：数据源 → ETL → 数据仓库 → 数据集市 → OLAP/报表。
4. ETL 三阶段职责，尤其转换阶段的清洗、去重、统一单位。
5. 事实表 vs 维度表：谁放度量值、谁放描述信息。
6. 星型与雪花模型的区别与优缺点。
7. 数据立方体：维、维层次（年-季度-月）、度量。
8. OLAP 五种操作：上卷、下钻、切片、切块、旋转，能识别场景、能用 SQL 示意。
9. 切片（一个值）与切块（一个区间）的辨析。
10. OLAP"查已知"与数据挖掘"发现未知"的定位差异。

📌 中英术语表

中文	English	记忆点
数据仓库	data warehouse	面向分析的历史数据集合
面向主题	subject-oriented	按业务主题组织
集成	integrated	统一编码/单位/命名
非易失	non-volatile	只进不改
随时间变化	time-variant	每份数据带时间维
联机事务处理	OLTP	面向业务、实时增删改
联机分析处理	OLAP	面向分析、多维查询
抽取/转换/加载	Extract / Transform / Load	ETL 三阶段
数据集市	data mart	数据仓库的部门子集
事实表	fact table	放度量值+外键
维度表	dimension table	放描述性文字
度量	measure	可加和的数值
维度	dimension	观察数据的角度
维层次	hierarchy	年-季度-月的层级
星型模型	star schema	事实表居中，维表环绕
雪花模型	snowflake schema	维度表拆分规范化
上卷	roll-up	沿维层次向上聚合
下钻	drill-down	沿维层次向下细化
切片	slice	固定一维的一个值
切块	dice	固定一维的一个区间
旋转	pivot	交换观察维度次序
数据挖掘	data mining	发现未知规律