第四章｜窗口函数与高级查询

本章目标

学习 SQL 最强大的分析功能：窗口函数、CTE、递归查询，掌握高级数据分析技巧。

从窗口函数的基础概念开始，理解 OVER 子句和分区，学会用 ROW_NUMBER、RANK 进行排名，用窗口聚合函数进行滑动计算，用 CTE 组织复杂查询，用递归 CTE 处理层级数据。

学习路径

按顺序阅读以下文章，每篇包含讲解、示例和练习：

1. 窗口函数基础：OVER 与分区
   理解窗口函数的概念，掌握 OVER、PARTITION BY、ORDER BY。

2. 排名函数：ROW_NUMBER、RANK、DENSE_RANK
   使用排名函数进行数据排序和去重。

3. 窗口聚合与移动计算
   使用窗口聚合函数进行滑动平均、累计求和。

4. CTE：WITH 子句与临时结果集
   使用 CTE 简化复杂查询，提高可读性。

5. 递归 CTE：处理层级数据
   使用递归 CTE 处理树形结构和图遍历。

关键概念

窗口函数不改变行数

与聚合函数不同，窗口函数为每行返回一个值：

-- 聚合：返回 1 行
SELECT AVG(amount) FROM orders;

-- 窗口：返回 N 行（每行都有平均值）
SELECT product, amount, AVG(amount) OVER () AS avg_amount
FROM orders;

PARTITION BY 创建窗口

PARTITION BY 将数据分成多个窗口，每个窗口独立计算：

-- 每个用户的订单编号
SELECT user_id, product,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_num
FROM orders;

CTE 提高可读性

CTE（Common Table Expression）是临时命名的结果集：

WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON u.id = ut.user_id;

递归 CTE 处理层级

递归 CTE 引用自身，适合处理树形结构：

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

前置知识

• 掌握 SELECT、JOIN、GROUP BY（第一到三章）
• 理解聚合函数的工作原理
• 熟悉子查询和派生表

后续章节

完成本章后，继续学习：

• 第五章：事务与隔离
  理解事务、ACID、隔离级别、锁机制。

• 第六章：索引与性能
  学习索引类型、查询优化、EXPLAIN 分析。

练习答案

本章所有练习的答案和详细解释：第四章练习答案

深入阅读

窗口函数与 GROUP BY 的区别

GROUP BY 将多行聚合成一行，窗口函数为每行计算一个值。窗口函数可以同时访问当前行和相关行的数据，而 GROUP BY 丢失了行级细节。

为什么需要 CTE？

CTE 让复杂查询更易读、易维护。相比嵌套子查询，CTE 可以被引用多次，避免重复计算。递归 CTE 提供了处理层级数据的标准方法。

返回SQL 学习路线 · 开始第一节