什么是窗口函数？与 GROUP BY 聚合函数的本质区别？

考察要点

这道题面试官想看你是否真正理解了 SQL 中两种不同维度的计算模式。它考察的核心是：

1. 数据行处理机制：你是否明白 GROUP BY 会“压缩”行数，而窗口函数会“保留”原始行数进行计算。
2. 计算上下文：你是否理解 GROUP BY 的计算是面向整个分组的“静态”聚合，而窗口函数是为每一行动态创建一个“窗口”来计算。
3. 应用场景区分：你是否能清晰地阐述何时应该用聚合做报表，何时应该用窗口函数做分析。

核心概念

窗口函数（Window Functions）与 GROUP BY 聚合函数都是对一组行进行计算，但其根本区别在于是否改变输入表的行数。

GROUP BY 聚合函数

GROUP BY 子句将表中的行根据指定的列组合成一个摘要行。它将多行数据“折叠”成一行，因此输出的行数必然小于或等于输入的行数。其本质是多对一的映射。

• 数据示例 假设我们有一张 sales 表：

  id	employee	department	amount
  1	Alice	Hardware	700
  2	Bob	Hardware	1500
  3	Carol	Software	2300
  4	David	Software	800
  5	Eve	Software	1200

• SQL 示例 计算每个部门的总销售额：

  sql

  ⧉⛶

   1SELECT
   2    department,
   3    SUM(amount) AS total_sales
   4FROM sales
   5GROUP BY department;

• 输出结果（行数被压缩）

  department	total_sales
  Hardware	2200
  Software	4300

窗口函数

窗口函数为结果集的每一行执行计算，这个计算基于一个与当前行相关的行的集合（即“窗口”）。它在保留原始行数的基础上，为每一行附加一个新的计算列。其本质是一对一的映射（每行输入对应一行输出）。

• 核心语法

  sql

  ⧉⛶

   1<window_function>() OVER (
   2    [PARTITION BY <...>]
   3    [ORDER BY <...>]
   4    [<frame_clause>]
   5)

  • PARTITION BY：定义窗口的分区，类似于 GROUP BY 的分组。计算会在每个分区内独立进行。
  • ORDER BY：定义分区内行的顺序，对于排名、累积计算等函数至关重要。
  • frame_clause (ROWS 或 RANGE)：精确定义窗口的边界，例如“当前行及之前两行”。

• SQL 示例 计算每个员工的销售额，并同时展示其所在部门的总销售额。

  sql

  ⧉⛶

   1SELECT
   2    employee,
   3    department,
   4    amount,
   5    SUM(amount) OVER (PARTITION BY department) AS department_total_sales
   6FROM sales;

• 输出结果（行数未变，增加了新列）

  employee	department	amount	department_total_sales
  Alice	Hardware	700	2200
  Bob	Hardware	1500	2200
  Carol	Software	2300	4300
  David	Software	800	4300
  Eve	Software	1200	4300

本质区别对比

如何在不支持窗口函数的版本中实现类似功能？

在 MySQL 5.7 及更早版本中，可以通过子查询或自连接来模拟窗口函数，但这通常效率更低且语法更复杂。

问题：计算每个员工的销售额，并同时展示其所在部门的总销售额。

解法 1：使用窗口函数（推荐）

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle
• SQL：
  sql

  ⧉⛶

   1SELECT
   2    employee,
   3    department,
   4    amount,
   5    SUM(amount) OVER (PARTITION BY department) AS department_total_sales
   6FROM sales;

• 复杂度：通常为 $O(N \log N)$ 或 $O(N)$。如果数据已按 PARTITION BY 的列物理有序，或有合适的索引，则可能为 $O(N)$。否则需要排序，为 $O(N \log N)$。
• 索引依赖：在 (department) 上建立索引可以帮助数据库高效地处理分区。

解法 2：使用 JOIN 和子查询（兼容老版本）

• 版本：✅ 所有版本
• SQL：
  sql

  ⧉⛶

   1SELECT
   2    s.employee,
   3    s.department,
   4    s.amount,
   5    dept_totals.total_sales AS department_total_sales
   6FROM
   7    sales s
   8JOIN
   9    (SELECT department, SUM(amount) AS total_sales FROM sales GROUP BY department) AS dept_totals
  10ON
  11    s.department = dept_totals.department;

• 复杂度：
  1. 子查询 dept_totals 对 sales 表进行 GROUP BY，通常使用哈希聚合，复杂度为 $O(N)$。
  2. 外层 JOIN 操作，如果 department 列上有索引，则为 $O(N \log K)$ 或 $O(N)$（K 为部门数），整体性能较好。
• 索引依赖：在 (department) 上建立索引对 GROUP BY 和 JOIN 都有显著的性能提升。

解法 3：使用相关子查询（不推荐，性能差）

• 版本：✅ 所有版本
• SQL：
  sql

  ⧉⛶

   1SELECT
   2    employee,
   3    department,
   4    amount,
   5    (SELECT SUM(amount) FROM sales s2 WHERE s2.department = s1.department) AS department_total_sales
   6FROM
   7    sales s1;

• 复杂度：对于外层查询的每一行，都会执行一次内层子查询。如果没有索引，复杂度接近 $O(N^2)$。即使 department 有索引，也远不如 JOIN 法高效。
• 索引依赖：department 列上的索引是必须的，否则将导致灾难性的性能问题。

常见低分回答

1. 混淆概念：“窗口函数和 GROUP BY 差不多，都是用来分组计算的。” —— 这是最常见的低分回答，完全没有点出“是否保留原始行”这一本质区别。
2. 在 WHERE 子句中使用窗口函数：“SELECT ... FROM ... WHERE RANK() OVER(...) = 1;” —— 这是错误的。窗口函数在 WHERE 子句之后执行，不能用于 WHERE 或 GROUP BY 子句的过滤。正确做法是使用 CTE 或子查询。
   sql

   ⧉⛶

    1-- 错误用法
    2SELECT * FROM sales WHERE SUM(amount) OVER (PARTITION BY department) > 2000;
    3
    4-- 正确用法
    5WITH SalesWithTotal AS (
    6    SELECT *, SUM(amount) OVER (PARTITION BY department) AS department_total_sales
    7    FROM sales
    8)
    9SELECT * FROM SalesWithTotal WHERE department_total_sales > 2000;

3. 对聚合函数和窗口函数混用时执行顺序不清晰：误认为 GROUP BY 和窗口函数在同一阶段处理。实际上，SQL 的逻辑执行顺序是 FROM -> WHERE -> GROUP BY -> HAVING -> 窗口函数 -> SELECT -> ORDER BY。窗口函数是在 GROUP BY 聚合之后才执行的。
4. 只知道 PARTITION BY，不了解 ORDER BY 和 Frame：只把窗口函数当作 GROUP BY 的“保留行”版本，而忽略了其强大的排序和滑动窗口计算能力（如计算移动平均值、YTD/MTD 等）。

工程实践与优化

• 执行计划分析：在 MySQL 8.0+ 中，使用 EXPLAIN 查看包含窗口函数的查询，Extra 列会显示 Window aggregate。这表明优化器正在使用窗口函数执行引擎。
• 索引优化：窗口函数的性能高度依赖于 PARTITION BY 和 ORDER BY 子句。为这些列创建复合索引 (partition_key, order_key) 能极大地提升性能，因为它避免了昂贵的内存/磁盘排序操作，数据库可以直接按序读取数据。
• 内存使用：窗口函数，特别是需要排序的大分区，可能会消耗大量内存。MySQL 中的 windowing_tmp_buffer_size 变量控制了用于窗口函数的临时缓冲区大小。如果分区数据过大，会溢出到磁盘，导致性能急剧下降。
• 替代方案：对于简单的“获取分组聚合值”的场景，解法 2（JOIN + GROUP BY 子查询）在所有版本中都表现良好，且逻辑清晰。不要为了用新技术而用，选择最适合当前场景和数据库版本的方案。

常见追问与延伸题目

1. 追问：请解释 RANK(), DENSE_RANK(), 和 ROW_NUMBER() 的区别？

   • 回答要点：这三个都是排名窗口函数，区别在于处理并列（tie）值的方式。
     • ROW_NUMBER()：为分区内的每一行分配一个从 1 开始的唯一连续排名，不考虑并列。
     • RANK()：并列的行获得相同的排名，但后续排名会跳过相应数量。例如：1, 2, 2, 4。
     • DENSE_RANK()：并列的行获得相同的排名，但后续排名是连续的。例如：1, 2, 2, 3。 最好能现场举一个简单的例子说明。

2. 追问：如果要实现“Top N per Group”的需求（例如，找出每个部门销售额最高的前 3 名员工），在 MySQL 5.7 和 8.0 中分别如何实现？

   • 回答要点：
     • MySQL 8.0：这是窗口函数的经典应用场景。使用 ROW_NUMBER() 或 RANK()，PARTITION BY department ORDER BY amount DESC，然后在外部查询中筛选出排名小于等于 3 的记录。
     • MySQL 5.7：这是一个难题，通常有两种方法：
       1. 用户变量模拟：通过设置和更新 session 变量来模拟行号，这是过去常用的“黑魔法”，但在高并发和复杂查询下不稳定，且 MySQL 8.0 已不推荐。
       2. 自连接/相关子查询：SELECT ... FROM sales s1 WHERE (SELECT COUNT(*) FROM sales s2 WHERE s2.department = s1.department AND s2.amount > s1.amount) < 3。这种方法逻辑复杂，且性能极差，数据量大时不可行。

3. 追问：窗口函数中的 ROWS 和 RANGE 有什么区别？

   • 回答要点：ROWS 和 RANGE 都用于定义 frame_clause（窗口帧），但定义边界的方式不同。
     • ROWS：基于物理行位置。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指的是当前行的前一行和后一行。
     • RANGE：基于逻辑值。RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 依赖于 ORDER BY 列的值。如果当前行的 ORDER BY 列值为 X，它会包含所有 ORDER BY 列值在 [X-1, X+1] 区间内的行。
     • 关键区别：当 ORDER BY 的列有重复值时，ROWS 会逐个处理，而 RANGE 会将所有重复值视为一个整体。RANGE 要求 ORDER BY 的列必须是数值或日期等可以进行加减的类型。