MySQL开窗函数种类和使用总结

在 MySQL 中，开窗函数（Window Functions） 是一种强大的功能，能够在数据分析和聚合时提供灵活的方式。开窗函数在 MySQL 8.0 及以上版本 中引入，可以基于数据的某个分组（窗口）来执行计算，而不会像 GROUP BY 那样将数据汇总为一行。

常用的开窗函数

开窗函数主要分为以下几类：

1. 聚合类函数

这些函数通常用于计算分组的聚合值，但在开窗函数中，聚合值会应用于窗口的每一行：

• SUM()
• AVG()
• MAX()
• MIN()
• COUNT()

2. 排序相关函数

这些函数用于返回行的排序信息：

• ROW_NUMBER()：窗口内的行号，从 1 开始。1,2,3,4,5，6
• RANK()：窗口内的排名，排名相同的行会有相同的 rank，下一名会跳过。1,2,2,2,5,6
• DENSE_RANK()：类似 RANK()，但排名不会跳过。1,2,2,2,3,4
• NTILE(n)：将行分为 n 个桶，返回当前行属于哪个桶。1,1,1,1,2,2,2,3,3,3,4,4,4,4, NTILE 分桶什么意思

3. 值偏移类函数

这些函数用于返回窗口内的相对值：

• LAG(column, offset, default)：返回当前行之前第 offset 行的值。
• LEAD(column, offset, default)：返回当前行之后第 offset 行的值。
• FIRST_VALUE(column)：返回窗口内的第一行值。
• LAST_VALUE(column)：返回窗口内的最后一行值。
• NTH_VALUE(column, n)：返回窗口内第 n 行的值。

基本语法

<函数>(<列名>) OVER ( [PARTITION BY <分组列>] [ORDER BY <排序列>] [<窗口范围>] ) 

• PARTITION BY：将数据按指定列分组，类似于 GROUP BY，但不会合并成一行。
• ORDER BY：指定窗口内的排序方式。
• 窗口范围：通过 ROWS 或 RANGE 定义窗口的大小。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从窗口的第一行到当前行。
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到窗口的最后一行。

示例

1. 计算每个部门的员工薪资排名

SELECT department_id, employee_id, salary
, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank 
FROM employees; 

• 解释：按照部门（department_id）对员工（employee_id）进行分组，并根据薪资降序排序，计算薪资排名。

2. 累计薪资计算

SELECT department_id, employee_id, salary
, SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary 
FROM employees; 

• 解释：按部门分组，按照员工 ID 排序，计算每个员工的累计薪资。

3. 获取前一行和后一行的薪资差异

SELECT employee_id, salary
, LAG(salary) OVER (ORDER BY salary) AS previous_salary
, LEAD(salary) OVER (ORDER BY salary) AS next_salary
, salary - LAG(salary) OVER (ORDER BY salary) AS salary_difference 
FROM employees; 

• 解释：
  • LAG(salary) 获取前一行的薪资。
  • LEAD(salary) 获取后一行的薪资。
  • 计算当前行与前一行薪资的差异。

4. 统计窗口的最大值和最小值

SELECT employee_id, salary
, MAX(salary) OVER (PARTITION BY department_id) AS max_salary_in_department
, MIN(salary) OVER (PARTITION BY department_id) AS min_salary_in_department 
FROM employees; 

• 解释：按部门分组，分别计算部门内薪资的最大值和最小值。

5. 分组求 TOP N 的行

WITH RankedSalaries AS ( 
    SELECT department_id, employee_id, salary
    , ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank 
    FROM employees 
) 
SELECT department_id, employee_id, salary 
FROM RankedSalaries 
WHERE rank <= 3; 

• 解释：使用 ROW_NUMBER() 按部门和薪资排名，再过滤出每个部门的前三名。

窗口范围的示例

SELECT employee_id, salary
, SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum 
FROM employees; 

• 解释：计算当前行及前两行的移动总和，这个方法用于滚动计算的实践应用极好，有时候会有这类需求，滚动求和，滚动count.

注意事项

1. MySQL 版本：开窗函数需要 MySQL 8.0 或更高版本，在旧版本中不可用。
2. 性能优化：开窗函数可能会增加查询的计算量，需注意索引的使用和优化 SQL 查询。
3. 窗口范围：默认窗口范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，需要根据需求调整。
4. Hive 里面的窗口函数跟MySQL 非常相似，可以拿来就用。