MySQL 窗口函数详解

mysql

MySQL 窗口函数详解

MySQL 自 8.0 版本起引入了窗口函数（Window Functions），它们允许在查询结果的特定“窗口”内对多行进行分析和计算。窗口函数不会将行压缩成一行，而是在保留每一行数据的基础上，增加计算的结果。窗口函数是进行高级数据分析和处理的强大工具，适用于累积计算、排名、滑动窗口计算等场景。

1. 窗口函数的基本概念

窗口函数是一种在 SQL 查询结果集中计算每一行的聚合值的函数。窗口函数的独特之处在于，它们不仅可以计算聚合值，还可以保留每一行的数据，并基于这些行进行进一步计算。窗口由 OVER() 子句定义，包含 PARTITION BY（分区）、ORDER BY（排序）和窗口帧。

2. 窗口函数的语法结构

窗口函数通常与 OVER() 子句结合使用，后者定义了计算的窗口范围和顺序。窗口函数的基本语法如下：

函数名 (参数) OVER (
    [PARTITION BY 分区列]
    [ORDER BY 排序列]
    [窗口帧定义]
)

PARTITION BY：指定数据的分区方式，类似于 GROUP BY，但不会聚合数据。
ORDER BY：指定在每个分区内的排序顺序。
窗口帧定义：定义窗口的范围，即在计算时，考虑当前行周围的哪些行。

3. 常见的窗口函数分类及详细介绍

MySQL 提供了多种窗口函数，可以分为以下几类：

a. 排名函数

ROW_NUMBER()：为每一行分配唯一的序号，按 ORDER BY 指定的顺序排列。
RANK()：为每一行分配排名，相同值会有相同排名，但排名会跳跃。
DENSE_RANK()：与 RANK() 类似，但排名不跳跃。
NTILE(n)：将结果集划分为 n 个组，并为每行分配组号。

示例：

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

此查询按部门对员工进行分区，并按薪资降序排列，计算每个员工在其部门内的排名。

b. 值函数

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

示例：

SELECT
    employee_id,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary,
    LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS next_salary
FROM
    employees;

此查询返回每个员工的薪资，以及其前一行和后一行员工的薪资。

c. 聚合窗口函数

SUM()：计算窗口内所有值的总和。
AVG()：计算窗口内所有值的平均值。
COUNT()：计算窗口内的行数。
MIN()：返回窗口内的最小值。
MAX()：返回窗口内的最大值。

示例：

SELECT
    employee_id,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM
    employees;

此查询计算每个部门的员工薪资总和。

d. 统计分布函数

PERCENT_RANK()：计算当前行在分区中的百分比排名。
CUME_DIST()：计算当前行在分区中的累积分布。

示例：

SELECT
    employee_id,
    department,
    salary,
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary DESC) AS cum_dist
FROM
    employees;

此查询计算每个员工在其部门内薪资的累积分布值。

4. 窗口帧定义及用法

窗口帧定义了窗口函数计算的具体行范围。ROWS 和 RANGE 是定义窗口帧的两个关键字：

ROWS：按物理行的数量来定义窗口范围。
RANGE：按逻辑值范围来定义窗口范围，通常与 ORDER BY 结合使用。

窗口帧子句：

ROWS | RANGE BETWEEN 窗口开始 AND 窗口结束

UNBOUNDED PRECEDING：从分区的第一行开始。
CURRENT ROW：当前行。
UNBOUNDED FOLLOWING：直到分区的最后一行。
n PRECEDING：当前行之前的第 n 行。
n FOLLOWING：当前行之后的第 n 行。

示例：

累计和计算：

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

此查询计算每个部门员工薪资的累计和，即从部门内薪资最高的员工开始计算到当前行的累计和。

滑动窗口平均值：

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department 
        ORDER BY salary ASC 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM
    employees;

此查询计算每个员工在其部门内的三行滑动平均薪资（前一行、当前行和后一行的平均值）。

5. 注意事项

与聚合函数的区别：窗口函数不会聚合行，而是保留每一行的详细信息。
性能问题：由于窗口函数计算的是整个窗口内的数据，因此在处理大数据集时，可能会导致性能问题。
ORDER BY 和窗口帧的影响：窗口函数的结果依赖于 ORDER BY 和窗口帧定义，错误的定义可能导致意外的结果。
窗口帧的定义：特别是在滑动窗口计算中，要小心窗口帧的边界，确保计算结果符合预期。
NULL 处理：窗口函数对 NULL 值的处理方式可能会影响排序和计算结果，使用时需谨慎。

6. 实际应用场景示例

分区内排名：

SELECT
    product_id,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
FROM
    products;

此查询返回每个分类内产品按价格降序的排名。

滑动窗口最大值：

SELECT
    sales_date,
    sales_amount,
    MAX(sales_amount) OVER (
        ORDER BY sales_date ASC 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS max_sales
FROM
    sales;

此查询返回每个日期的销售额，以及包括当前日期和前两个日期的最大销售额。

7. 参考文献

以上是 MySQL 窗口函数的内容，涵盖了函数分类、窗口帧定义、使用示例以及注意事项，帮助你更好地理解和应用窗口函数。

如果觉得文章对你有用，请随意赞赏

sql

MySQL 窗口函数详解

https://wuanwanghao.top/archives/mysql-chuang-kou-han-shu-xiang-jie

作者

忘皓

发布于

2024-08-29

更新于

2024-08-29

许可协议

CC BY 4.0

MySQL 窗口函数详解