元数据卡
- 前置知识：Vol 5 第1章（数据库基础）
- 预计时间：45 分钟
- 核心难度：进阶
- 阅读模式：高度专注
- 完成标志：能用窗口函数、CTE、OLAP 查询完成多步骤分析任务
你的进度
通过 EDA 你知道了数据的大致形貌——哪几列值得深挖，哪几列几乎全是空值。但预言厅的情报官看了你的图表摇了摇头：'图表漂亮，但我没法从里面查到具体数字。帮我查一下：过去三个月，哪个堡垒的任务完成率最高？'
你发现 pandas 做这种跨表聚合很别扭。你需要的是一把趁手的查询武器——SQL 的分析模式。
你的任务
之前在 Vol 5 里你用 SQL 做 CRUD——插入、更新、删除、简单查询。那是 MySQL 里的日常操作，每次查几行，最多几百行。现在你的数据在数仓里，每次查询扫描几百万行，你需要做跨分组的累计统计、移动平均、同环比。普通的 SELECT-GROUP BY 不够用了。这一章给你分析型 SQL 的武器库。
从行操作到集合操作
分析型 SQL 和事务型 SQL 的区别在于思考方式：
- 事务型 SQL：我需要这一行，把这个字段改了
- 分析型 SQL：我需要这一组数据，跨行计算一个值
你在 Vol 5 里用 GROUP BY 做过分组聚合。但 GROUP BY 有一个限制：分组后每组的明细行被折叠成一行聚合值。你想在保持每一行数据的在旁边加上分组聚合结果——这就要用窗口函数。
窗口函数
窗口函数不折叠行，它在每一行旁边计算一个"窗口"内的聚合值。
-- 基础语法：
-- 函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列)看一个具体的例子。假设你有一张任务日志表：
-- 创建示例表
CREATE TABLE mission_logs (
mission_id INT,
log_date DATE,
resources_used INT
);
INSERT INTO mission_logs VALUES
(1, '2026-06-01', 100),
(1, '2026-06-02', 120),
(1, '2026-06-03', 110),
(2, '2026-06-01', 200),
(2, '2026-06-02', 220);
-- 你想看：每个任务每天的用量，以及这个任务到目前为止的总用量
SELECT
mission_id,
log_date,
resources_used,
SUM(resources_used) OVER (
PARTITION BY mission_id
ORDER BY log_date
) AS running_total
FROM mission_logs;结果：
mission_id | log_date | resources_used | running_total
-----------+------------+----------------+--------------
1 | 2026-06-01 | 100 | 100
1 | 2026-06-02 | 120 | 220
1 | 2026-06-03 | 110 | 330
2 | 2026-06-01 | 200 | 200
2 | 2026-06-02 | 220 | 420看到区别了吗？GROUP BY 只能给你两个聚合值（任务 1 = 330，任务 2 = 420）。而窗口函数在保留每行数据的在右侧添加了运行累计值。
常见的窗口函数用途：
-- 排名：每个任务内部的资源使用排名
SELECT
mission_id, log_date, resources_used,
ROW_NUMBER() OVER (PARTITION BY mission_id ORDER BY resources_used DESC) AS rank
FROM mission_logs;
-- 移动平均：过去 3 天平均资源用量
SELECT
mission_id, log_date, resources_used,
AVG(resources_used) OVER (
PARTITION BY mission_id
ORDER BY log_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3d
FROM mission_logs;
-- 滞后：取前一天的值做环比
SELECT
mission_id, log_date, resources_used,
LAG(resources_used, 1) OVER (
PARTITION BY mission_id
ORDER BY log_date
) AS prev_day,
resources_used - LAG(resources_used, 1) OVER (
PARTITION BY mission_id
ORDER BY log_date
) AS daily_change
FROM mission_logs;CTE：把多步查询拆成可读的步骤
分析型查询往往不是一个 SELECT 能搞定的。你需要先算 A，再基于 A 算 B，再基于 B 算 C。CTE（Common Table Expression）让你按步骤写，每个步骤有名字。
WITH daily_usage AS (
-- 第一步：计算每日使用量总和
SELECT
log_date,
SUM(resources_used) AS total_resources
FROM mission_logs
GROUP BY log_date
),
weekly_avg AS (
-- 第二步：计算每周 7 天移动平均
SELECT
log_date,
total_resources,
AVG(total_resources) OVER (
ORDER BY log_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS weekly_ma
FROM daily_usage
)
-- 第三步：找出低于均值 20% 的异常日
SELECT *
FROM weekly_avg
WHERE total_resources < weekly_ma * 0.8;CTE 最大好处是每个步骤可以独立测试。先跑 daily_usage，确认结果对，再加下一步。
OLAP 操作：ROLLUP 和 CUBE
当你想从不同维度层级看数据时（总聚合、按类型聚合、按类型+地区聚合），不需要写三个 UNION ALL。用 ROLLUP：
SELECT
mission_type,
location,
SUM(resources_used) AS total
FROM mission_logs
GROUP BY ROLLUP (mission_type, location);结果会返回：
- 总聚合一行（mission_type = NULL, location = NULL）
- 按 mission_type 聚合的行（location = NULL）
- 按 mission_type + location 详细聚合的行
CUBE 更彻底，它会生成所有可能的维度组合聚合。
在 Python 中写分析型 SQL
Pandas 的 groupby + transform 等价于 SQL 窗口函数：
import pandas as pd
df = pd.read_sql("SELECT * FROM mission_logs", engine)
# 等价于 SUM OVER (PARTITION BY mission_id ORDER BY log_date)
df["running_total"] = df.groupby("mission_id")["resources_used"].cumsum()
# 等价于移动平均
df["moving_avg_3d"] = (
df.groupby("mission_id")["resources_used"]
.transform(lambda x: x.rolling(3, min_periods=1).mean())
)常见陷阱
- 窗口函数中忘记
PARTITION BY。结果是全局排序 + 聚合，不是你想要的组内计算。 - 窗口函数
ORDER BY的默认窗口是从开头到当前行。如果你只想看当前行（比如排名、移动平均），需要显式指定窗口范围。 - CTE 里用了不可重复使用的临时表名，导致调试困难。
通关挑战
- 热身：在 missions 表上写一个窗口函数，计算每个团队成员的平均任务完成时间。
- 挑战：用 CTE + 窗口函数写一个完整的分析查询：找出连续 3 天资源使用量异常偏高（超出 30 天移动平均的 2 倍标准差）的任务。
- 观察：把一个 5 步骤的 CTE 查询逐步拆解，每一步输出到中间表，验证每一步的中间结果。
验收标准
- 能写出包含
PARTITION BY和ORDER BY的窗口函数 - 能用 CTE 把多步分析查询拆成可读的步骤
- 理解
ROLLUP和CUBE的作用 - 能在 Python 中用 pandas 复现等价的窗口计算
旅人笔记
SQL 窗口函数是你处理分析问题时最趁手的武器。学会它，你的 SQL 能力从"能查"变成"能分析"。
下一站预告
上一章你从数据中看到了模式。但有些模式是真是假？下一章，用统计推断做判断。