MySQL 8 中的窗口函数详解

什么是窗口函数

窗口函数是一种在结果集中进行计算的函数，可以对数据集的特定“窗口”内的行进行操作，而不需要将数据聚合成单个的输出行。与普通的聚合函数不同，窗口函数允许我们保留原始行，因此在进行数据分析时具有更大的灵活性。

在 MySQL 8 中，窗口函数被广泛使用，能够带来更加复杂的数据分析能力，支持对数据的排序、分组和计算。

窗口函数的基本语法

窗口函数的基本语法如下：

函数名() OVER (
    [PARTITION BY 列1, 列2, ...]
    [ORDER BY 列3, 列4, ...]
    [ROWS|RANGE BETWEEN ... AND ...]
)

• 函数名()：可以是任何支持的窗口函数，如 SUM()、AVG()、ROW_NUMBER() 等。
• PARTITION BY：用于指定数据分区的列，类似于 GROUP BY。
• ORDER BY：用于定义在窗口内行的排序顺序。
• ROWS|RANGE BETWEEN：可选的，用于定义当前行和相关行的边界。

窗口函数的类型

聚合窗口函数

聚合窗口函数对分区中的行进行计算，计算结果包含在每一行中。常见的聚合窗口函数有：

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

排名窗口函数

排名窗口函数用于为每一行赋予一个排名。常见的排名函数包括：

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE(n)

ROW_NUMBER()

ROW_NUMBER() 函数为每一行分配一个唯一的序号，从 1 开始，按照指定的排序顺序分配。如果存在排序相同的行，ROW_NUMBER() 会为它们分配连续的序号，但它们之间不会有间隔。

示例 SQL 代码:

SELECT
    employee_id,
    first_name,
    last_name,
    ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM
    employees;

在这个例子中，ROW_NUMBER() 为每个员工根据 hire_date（入职日期）分配一个唯一的序号。

RANK()

RANK() 函数为每一行分配一个排名，如果存在排序相同的行，它们将获得相同的排名，并且后续的排名会跳过。例如，如果两行并列第一，则下一个排名将是第三。

示例 SQL 代码:

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

在这个例子中，RANK() 根据 salary（工资）为每个员工分配一个排名，工资相同的员工将获得相同的排名。

DENSE_RANK()

DENSE_RANK() 函数为每一行分配一个排名，如果存在排序相同的行，它们将获得相同的排名，并且后续的排名不会跳过。例如，如果两行并列第一，则下一个排名将是第二。

示例 SQL 代码:

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

在这个例子中，DENSE_RANK() 根据 salary（工资）为每个员工分配一个排名，工资相同的员工将获得相同的排名，后续的排名不会跳过。

NTILE(n)

NTILE(n) 函数将结果集分成 n 个相同大小的区域，并为每个区域分配一个编号。如果结果集的行数不能被 n 整除，最后一组可能会包含更少的行。

示例 SQL 代码:

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS ntile
FROM
    employees;

在这个例子中，NTILE(4) 将结果集根据 salary（工资）分成四个相同大小的区域，并为每个区域分配一个编号。

这些排名窗口函数可以帮助我们对数据进行排名和分组，进一步分析和比较不同行之间的关系和差异。

分析窗口函数

分析窗口函数提供了行间的分析功能，常见函数有：

• LEAD()
• LAG()

LEAD()

LEAD() 函数用于获取当前行之后指定偏移量的行的值。它可以用于获取下一行、下两行等的值。如果指定的偏移量超出了结果集的行数，则返回 NULL。

示例 SQL 代码:

SELECT
    employee_id,
    hire_date,
    LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire_date
FROM
    employees;

在这个例子中，LEAD(hire_date, 1) 用于获取每个员工的入职日期的下一个入职日期。

LAG()

LAG() 函数用于获取当前行之前指定偏移量的行的值。它可以用于获取上一行、上两行等的值。如果指定的偏移量超出了结果集的行数，则返回 NULL。

示例 SQL 代码:

SELECT
    employee_id,
    hire_date,
    LAG(hire_date, 1) OVER (ORDER BY hire_date) AS previous_hire_date
FROM
    employees;

在这个例子中，LAG(hire_date, 1) 用于获取每个员工的入职日期的上一个入职日期。

这些分析窗口函数可以帮助我们在数据分析过程中对行之间的关系进行比较和分析，从而获取更多有价值的信息。

窗口函数的应用示例

示例 1：计算累积和

我们假设有一个销售记录表 sales，结构如下：

id	sales_amount	sale_date
1	100	2023-01-01
2	200	2023-01-02
3	150	2023-01-03

我们想要计算每一天的累积销售额：

SELECT
    id,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM
    sales;

示例 2：计算排名

若我们需要按销售额排名，可以使用 RANK() 函数：

SELECT
    id,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

示例 3：求移动平均

假设我们需要计算过去三天的移动平均销售额：

SELECT
    id,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM
    sales;

总结

MySQL 8 中的窗口函数为数据分析提供了强大的新工具，允许我们在与原始数据保持行的独立性时进行复杂的计算和分析。通过丰富的窗口函数类型，我们可以实现各种数据聚合、排名和分析操作，使得数据处理和报表生成更加高效和灵活。

在实际应用中，可以根据需要灵活选择合适的窗口函数，帮助我们更好地理解和利用我们的数据。希望本篇文章能够帮助你深入了解和使用 MySQL 8 中的窗口函数！