MySQL的窗口函数

MySQL的窗口函数

窗口函数（Window Functions）是MySQL提供的一种高级查询功能，允许用户对结果集的某些行进行计算。不同于聚合函数，窗口函数不会将行合并到一组中，而是能够保留原始行的数据，并且还能基于当前行以及相邻行的值来进行计算。

窗口函数的基本语法

窗口函数的基本结构如下：

window_function() OVER (
  PARTITION BY partition_columns
  ORDER BY order_columns
  ROWS or RANGE BETWEEN start AND end
)

其中：

• window_function() 是具体的窗口函数，如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。
• PARTITION BY 子句用于将结果集划分为多个分区，类似于 GROUP BY，但不会合并数据行。
• ORDER BY 子句用于在每个分区内对数据排序。
• ROWS 或 RANGE 子句用于定义窗口的范围，常用于滚动计算。

窗口函数的组成部分

1. 窗口函数名称：

   • 窗口函数名称指的是执行特定操作的函数，如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。这些函数可以对一个窗口（即一组行）内的每一行执行特定的计算。

2. OVER 子句：

   • OVER 子句定义了窗口函数的工作范围。它决定了窗口函数应用在哪些行上，以及如何对这些行进行分组和排序。

3. PARTITION BY 子句：

   • PARTITION BY 子句用于将结果集分割成多个分区，每个分区可以独立计算。它类似于 GROUP BY，但不同的是，PARTITION BY 不会聚合行，而是保持每一行的独立性。例如，在员工数据中，按部门分区计算每个部门内的排名：

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

   这里，每个部门有一个独立的排名。

4. ORDER BY 子句：

   • ORDER BY 子句指定了如何在每个分区内排序。排序的结果影响窗口函数的计算，例如，ROW_NUMBER() 会基于排序的结果给每一行编号。需要注意的是，ORDER BY 不能与 PARTITION BY 互换，它们的作用是不同的。

5. ROWS 和 RANGE 子句：

   • ROWS 和 RANGE 子句用于定义窗口的范围，即对当前行进行计算时，应该包含哪些行。这在计算移动平均值或累积和时非常有用。ROWS 精确指定了包括的行数，而 RANGE 则基于逻辑范围，例如时间或数值。

   示例：计算前两行和当前行的总和（包括当前行在内的前两行）：

   SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_salary
   

常用的窗口函数

ROW_NUMBER()

ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的序号，常用于为每一行生成唯一标识符。

SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

详细用法可以参考文章：
// TODO 补充链接：

2. RANK()

RANK() 函数为排序后的行分配一个排名，允许出现重复排名。不同的值会产生间隙。

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

详细用法可以参考文章：
// TODO 补充链接：

3. DENSE_RANK()

DENSE_RANK() 类似于 RANK()，但不会有排名间隙。即使有相同的排名，下一行的排名也只加1。

SELECT 
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

详细用法可以参考文章：
// TODO 补充链接：

4. SUM()

SUM() 是一种聚合函数，但作为窗口函数使用时，可以计算一个窗口范围内的累积和。

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

详细用法可以参考文章：
// TODO 补充链接：

窗口函数分类

1. 排序函数（Ranking Functions）：

   • ROW_NUMBER()：为每行分配一个唯一的顺序编号，从1开始。
   • RANK()：根据排序列的顺序为每行分配排名，相同排序值的行分配相同的排名，排名会有间隙。
   • DENSE_RANK()：类似于 RANK()，但没有间隙。相同排序值的行仍然有相同排名，但下一个不同排序值的行排名不会有间隙。
   • NTILE(n)：将结果集分成 n 个近似大小相等的桶，并为每一行分配一个桶号。

2. 聚合函数（Aggregate Functions）：

   • SUM()：计算窗口内值的总和。
   • AVG()：计算窗口内值的平均值。
   • MIN()：获取窗口内的最小值。
   • MAX()：获取窗口内的最大值。
   • COUNT()：计算窗口内行的数量。

3. 值分析函数（Value Window Functions）：

   • LAG(expr, N, default)：返回当前行之前第 N 行的值。如果不存在，则返回默认值。
   • LEAD(expr, N, default)：返回当前行之后第 N 行的值。如果不存在，则返回默认值。
   • FIRST_VALUE(expr)：返回窗口中的第一个值。
   • LAST_VALUE(expr)：返回窗口中的最后一个值。
   • NTH_VALUE(expr, N)：返回窗口中第 N 行的值。

4. 其他窗口函数：

   • CUME_DIST()：计算当前行的累积分布值，即小于或等于当前行的值的行数占总行数的比例。
   • PERCENT_RANK()：计算百分比排名，范围从 0 到 1。
   • PERCENTILE_CONT() 和 PERCENTILE_DISC()：计算连续和离散百分位数（MySQL 中不直接支持）。

使用场景

1. 排名和排序：在业务场景中，如比赛结果统计、销售业绩排名等，窗口函数能够方便地进行排名。
2. 运行总计和移动平均：在金融分析中，常需要计算一段时间内的移动平均值或运行总计，窗口函数非常适合这些计算。
3. 分区分析：例如按照地区、部门等分类进行数据分析，窗口函数可以不改变分组关系的情况下提供详细的数据分析。

性能注意事项

使用窗口函数时，要注意其对查询性能的影响。复杂的窗口函数计算尤其是在大数据集上，可能会导致性能问题。适当的索引和优化是必要的。

参考链接

• MySQL 官方文档：窗口函数
• 窗口函数的实用例子和应用场景