SQL 分析进阶知识体系概览

前置知识

在学习本章内容之前，你需要掌握以下基础：

• SELECT 查询基础：WHERE、ORDER BY、LIMIT、CASE WHEN
• JOIN 连接查询：INNER JOIN、LEFT JOIN、自连接
• 聚合函数与 GROUP BY：COUNT、SUM、AVG、GROUP BY、HAVING
• 子查询：标量子查询、相关子查询、EXISTS

如果上述内容还不熟练，建议先补充基础再来学本章。

---

从 SQL 基础到分析进阶

掌握了基础 SQL 之后，你已经能处理大部分日常查询了。但在数据分析岗位面试和真实业务分析中，还有一类更高阶的 SQL 技能会被重点考察。

这些技巧的核心价值在于：用一条 SQL 完成过去需要多步操作、甚至需要导出到 Excel 才能做到的分析。

典型场景对比

分析需求	基础 SQL 的困境	进阶 SQL 的解法
每月销售额的环比增长率	需要自连接或临时表，写法复杂	LAG() 窗口函数，一行搞定
给用户订单金额排名（保留并列）	难以处理并列情况	DENSE_RANK() 窗口函数
查询每个部门中薪资最高的员工	子查询嵌套、可读性差	窗口函数 + CTE，清晰直观
递归查找组织架构的所有下属	基础 SQL 无法递归	递归 CTE
把月份作为列，商品作为行展示	难以用纯 SQL 实现	PIVOT 或 CASE WHEN 行列转换
同时得到总计、分类小计、明细	需要多条 SQL UNION ALL	ROLLUP / CUBE 一次搞定

---

高阶 SQL 与基础 SQL 的差距

下面用一个具体的业务场景，直观感受"没有高阶 SQL"是多么痛苦。

场景：计算每个用户的订单金额累计值，以及该订单在当月所有订单中的排名

数据表 orders：

-- orders 表结构
-- user_id  INT
-- order_id INT
-- amount   DECIMAL
-- order_date DATE

用基础 SQL 实现：

-- ❌ 基础 SQL：需要多步操作，写法繁琐

-- 第一步：计算每个用户的订单排名（用自连接模拟）
SELECT
    a.user_id,
    a.order_id,
    a.amount,
    a.order_date,
    COUNT(b.order_id) + 1 AS rank_in_month
FROM orders a
LEFT JOIN orders b
    ON a.user_id = b.user_id
    AND DATE_FORMAT(a.order_date, '%Y-%m') = DATE_FORMAT(b.order_date, '%Y-%m')
    AND b.amount > a.amount   -- 比当前 amount 大的有几条，+1 就是排名
GROUP BY a.user_id, a.order_id, a.amount, a.order_date;

-- 第二步：累计金额还要另写一个自连接...
-- 这已经变成了两条复杂查询，还不支持并列排名处理

用高阶 SQL 实现：

-- ✅ 进阶 SQL：窗口函数，一条查询搞定

SELECT
    user_id,
    order_id,
    amount,
    order_date,
    -- 按用户分组，按月份分区，金额降序排名（并列保留）
    DENSE_RANK() OVER (
        PARTITION BY user_id, DATE_FORMAT(order_date, '%Y-%m')
        ORDER BY amount DESC
    ) AS rank_in_month,
    -- 按用户分组，按时间顺序累计金额
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM orders;

差距在哪里

• 代码量：基础 SQL 需要 20+ 行多步查询，进阶 SQL 只需 15 行一次完成
• 可读性：进阶 SQL 的意图一目了然，基础 SQL 的自连接逻辑需要细看才懂
• 正确性：基础 SQL 的自连接排名无法正确处理并列（两个相同金额会得到不同排名）
• 性能：进阶 SQL 只扫描一次表，基础 SQL 需要多次扫描

---

各技术的使用场景详解

1. 窗口函数 vs GROUP BY

这是最容易混淆的概念。核心区别一句话：

GROUP BY 会压缩行数，OVER() 不会压缩行数。

什么时候用 GROUP BY： 你只需要聚合后的结果，不需要保留原始明细行。

-- 只想知道每个部门的平均薪资
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- 结果：每个部门只有 1 行

什么时候用窗口函数： 你既需要明细行，又需要聚合值（比如"每行显示自己的薪资以及所在部门的平均薪资"）。

-- 想知道每个员工的薪资，以及和部门平均的差距
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;
-- 结果：每个员工保留 1 行，同时有部门平均值

面试高频考点

"GROUP BY 和窗口函数的区别"是数据分析岗位面试必考题之一。记住这个核心口诀：GROUP BY 压缩行，窗口函数不压缩。

---

2. CTE vs 子查询

CTE（公共表表达式，即 WITH 子句）和子查询在功能上很多时候可以互相替换，但有明显的适用偏好。

什么时候用子查询： 逻辑简单、只用一次、嵌套层次浅（1-2 层）。

-- 子查询：查询薪资高于平均值的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

什么时候用 CTE： 逻辑复杂、需要引用多次、嵌套层数超过 2 层、需要递归。

-- CTE：同样的需求，逻辑清晰，中间结果有名字
WITH avg_salary AS (
    SELECT AVG(salary) AS avg_val FROM employees
)
SELECT e.name, e.salary
FROM employees e
JOIN avg_salary a ON e.salary > a.avg_val;

CTE 可以被多次引用，子查询不行：

-- CTE 多次引用：统计高薪员工数量和占比
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 50000
)
SELECT
    (SELECT COUNT(*) FROM high_earners) AS high_count,       -- 引用第一次
    (SELECT COUNT(*) FROM employees) AS total_count,
    (SELECT COUNT(*) FROM high_earners) * 1.0                -- 引用第二次
        / (SELECT COUNT(*) FROM employees) AS ratio;

递归场景只能用 CTE：

-- 递归 CTE：查找某员工的所有上级
WITH RECURSIVE manager_chain AS (
    -- 基础查询：从当前员工开始
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE employee_id = 100  -- 起始员工

    UNION ALL

    -- 递归：向上查找上级
    SELECT e.employee_id, e.name, e.manager_id, mc.level + 1
    FROM employees e
    JOIN manager_chain mc ON e.employee_id = mc.manager_id
)
SELECT * FROM manager_chain ORDER BY level;

---

3. 行列转换：什么时候需要它

行列转换（Pivot）用于报表展示场景，把一列中的不同值变成多个列标题。

典型需求： 按月份统计每个产品的销售额，月份作为列。

-- 原始数据格式（长表）
-- product | month  | sales
-- 手机    | 2024-01 | 5000
-- 手机    | 2024-02 | 6000
-- 电脑    | 2024-01 | 8000

-- 期望格式（宽表）
-- product | 2024-01 | 2024-02 | 2024-03
-- 手机    | 5000    | 6000    | 7000
-- 电脑    | 8000    | 9000    | 10000

这种转换在 Excel 里叫"数据透视表"，在 SQL 中叫 PIVOT。适用场景包括：

• 向业务方汇报的月度/季度对比报表
• 交叉表分析（用户行为矩阵等）
• BI 工具的数据准备

---

4. 复杂聚合（ROLLUP / CUBE / GROUPING SETS）

当你需要同时展示不同粒度的汇总时，ROLLUP、CUBE、GROUPING SETS 可以替代多条 UNION ALL。

典型需求： 一张报表同时显示"每个省每个城市的销售额"+"每个省的汇总"+"全国汇总"。

-- ❌ 不用复杂聚合：需要 UNION ALL 三条查询
SELECT province, city, SUM(sales) FROM orders GROUP BY province, city
UNION ALL
SELECT province, NULL, SUM(sales) FROM orders GROUP BY province
UNION ALL
SELECT NULL, NULL, SUM(sales) FROM orders;

-- ✅ 用 ROLLUP：一条语句，自动生成各级别汇总
SELECT province, city, SUM(sales) AS total_sales
FROM orders
GROUP BY ROLLUP(province, city);

适用场景：财务报表、销售汇报、多维度数据分析看板。

---

技术全景图

---

核心知识导航

主题	核心内容	面试重要度	工作频率
窗口函数	ROW_NUMBER、RANK、LAG/LEAD、累计计算、移动平均	⭐⭐⭐⭐⭐	极高
CTE 公共表表达式	WITH 子句、递归查询、多步逻辑拆分	⭐⭐⭐⭐	高
行列转换	PIVOT/UNPIVOT、CASE WHEN 实现、报表格式化	⭐⭐⭐⭐	中等
复杂聚合	GROUPING SETS、CUBE、ROLLUP、多维分析	⭐⭐⭐	中等

---

实际工作中的使用频率

技术	使用频率	典型使用场景
ROW_NUMBER()	🔥🔥🔥🔥🔥	去重、分页、取每组第一条
RANK() / DENSE_RANK()	🔥🔥🔥🔥🔥	用户/商品/销售人员排行榜
LAG() / LEAD()	🔥🔥🔥🔥🔥	环比计算、前后行对比
SUM() OVER() 累计	🔥🔥🔥🔥	累计销售额、累计用户数
普通 CTE	🔥🔥🔥🔥	复杂查询的逻辑拆分
递归 CTE	🔥🔥	组织架构、商品分类树
行列转换	🔥🔥🔥	月度对比报表、交叉分析
ROLLUP	🔥🔥	多级汇总报表
GROUPING SETS	🔥	自定义多维度分析

---

面试考察重点

数据分析岗位（DA、BI、数据运营）的 SQL 面试，进阶 SQL 往往是区分"会用 SQL 查数据"和"能用 SQL 做分析"的分水岭。

面试官在考什么

1. 概念理解层：能说清楚"是什么"

• 窗口函数的 PARTITION BY 和 GROUP BY 有什么区别？
• RANK() 和 DENSE_RANK() 的区别是什么？
• CTE 和子查询在什么时候用哪个？

2. 代码实现层：能写出正确的 SQL

• 写出计算每月环比增长率的 SQL
• 用 SQL 找出每个部门薪资最高的 Top 3 员工
• 用 SQL 计算用户的连续登录天数

3. 业务分析层：能把业务问题转化为 SQL

• 如何用 SQL 做留存分析（次日留存、7 日留存）？
• 如何用 SQL 做漏斗转化分析？
• 如何识别刷单用户（短时间内大量相似订单）？

常见"坑"题型

注意这些陷阱

• 并列排名处理：ROW_NUMBER() 不允许并列，RANK() 并列后跳号，DENSE_RANK() 并列后不跳号。面试官会故意问你区别。
• 窗口范围默认值：SUM() OVER(ORDER BY date) 的默认窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，而不是整个分区。
• NULL 值处理：LAG() 在第一行返回 NULL，如果直接计算环比会出错，需要用 COALESCE 处理。
• CTE 不是视图：CTE 只在当前查询中有效，每次引用都会重新执行（在大多数数据库中）。

高频面试题预览

本章的 SQL 面试高频题精选 收录了 15 道高频题，涵盖：

• 连续登录天数计算
• 每组 Top N 问题
• 同期群（Cohort）分析
• 漏斗转化率计算
• 用户活跃度分析

---

各数据库的兼容性

版本要求

窗口函数需要注意数据库版本支持情况：

数据库	窗口函数支持版本	递归 CTE
MySQL	8.0+	8.0+
PostgreSQL	8.4+（完整支持）	8.4+
Hive	0.11+	不支持
Spark SQL	1.4+	2.0+
SQL Server	2005+	2005+
Oracle	8i+（较早支持）	11g+
SQLite	3.25.0+（2018年）	3.8.3+

如果你的环境是 MySQL 5.7 或以下，窗口函数不可用，需要用自连接或变量模拟。大部分现代数据仓库（Hive、Spark、BigQuery、Redshift）都完整支持。

---

学习建议

建议的学习顺序

第一步：窗口函数（最重要，面试必考）
    → 先掌握 ROW_NUMBER / RANK / DENSE_RANK
    → 再学 LAG / LEAD 做时间序列分析
    → 最后学 SUM/AVG OVER 做累计和移动平均

第二步：CTE（提升代码可读性）
    → 先学普通 CTE（WITH 子句）
    → 再学多 CTE 链式写法
    → 最后学递归 CTE（用到才需要）

第三步：行列转换（报表场景用）
    → 先学 CASE WHEN 行转列（通用）
    → 再学 PIVOT/UNPIVOT 语法（特定数据库）

第四步：复杂聚合（进阶加分项）
    → 先学 ROLLUP（最常用）
    → 再学 GROUPING SETS
    → 最后学 CUBE（较少用）

学习方法建议

1. 不要死背语法，要理解"何时用"

每学一个新技术，先问自己：

• 这解决了什么问题？
• 没有它的话我怎么做？
• 它比原来的方法好在哪里？

2. 对照着真实数据练习

推荐使用以下练习平台：

• LeetCode SQL：有大量真实场景的 SQL 题
• SQLZoo：交互式练习，有即时反馈
• Mode Analytics SQL Tutorial：面向数据分析的系统教程
• 本章末尾的 SQL 分析实战练习：留存、漏斗、RFM 等经典分析场景

3. 在真实工作/项目中使用

看到查询需求时，主动思考：

• "这里能不能用窗口函数简化？"
• "这个嵌套子查询能不能改成 CTE？"

4. 总结自己的 SQL 模板库

把常用的窗口函数、CTE 结构整理成模板，遇到类似场景直接套用并修改。

---

速查手册

窗口函数语法模板

-- 通用窗口函数语法
函数名() OVER (
    [PARTITION BY 分组列]   -- 可选：按哪列分组，类似 GROUP BY
    [ORDER BY 排序列]        -- 可选：窗口内排序
    [ROWS/RANGE BETWEEN ... AND ...]  -- 可选：窗口范围
)

CTE 语法模板

-- 单个 CTE
WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

-- 多个 CTE（前面的可以被后面引用）
WITH
cte1 AS (SELECT ...),
cte2 AS (SELECT ... FROM cte1),
cte3 AS (SELECT ... FROM cte2)
SELECT * FROM cte3;

-- 递归 CTE
WITH RECURSIVE cte AS (
    SELECT ...   -- 基础查询（终止条件）
    UNION ALL
    SELECT ... FROM cte WHERE ...  -- 递归查询
)
SELECT * FROM cte;

行列转换模板

-- CASE WHEN 行转列（通用，所有数据库支持）
SELECT
    product,
    SUM(CASE WHEN month = '2024-01' THEN sales ELSE 0 END) AS "2024-01",
    SUM(CASE WHEN month = '2024-02' THEN sales ELSE 0 END) AS "2024-02",
    SUM(CASE WHEN month = '2024-03' THEN sales ELSE 0 END) AS "2024-03"
FROM sales_data
GROUP BY product;

---

相关链接

• MySQL 8.0 窗口函数官方文档
• PostgreSQL 窗口函数教程
• PostgreSQL CTE 文档
• Modern SQL - 窗口函数详解
• Use The Index, Luke - SQL 性能调优
• LeetCode 数据库题库