MySQL高级函数之：`OVER`子句：`PARTITION BY`和`ORDER BY`在窗口函数中的作用。

MySQL 高级函数之 OVER 子句：PARTITION BY 和 ORDER BY 在窗口函数中的作用

各位同学，大家好！今天我们来深入探讨 MySQL 中一个非常强大的特性：窗口函数及其 OVER 子句。尤其是 OVER 子句中的 PARTITION BY 和 ORDER BY，它们在定义窗口、控制计算范围和排序方面起着至关重要的作用。

什么是窗口函数？

窗口函数（Window Function）允许我们对一组与当前行相关的行（称为窗口）执行计算。与聚合函数不同，窗口函数不会将多行数据聚合为单行，而是为结果集中的每一行都返回一个值。这意味着我们可以在不影响原始数据行数的情况下，进行复杂的分析和计算。

OVER 子句：定义窗口

OVER 子句是窗口函数的核心，它定义了窗口的范围和计算方式。基本的 OVER 子句可以为空，例如 SUM(salary) OVER()，这意味着对整个结果集进行计算。但更常见的是，我们需要使用 PARTITION BY 和 ORDER BY 来更精细地控制窗口的范围和排序。

PARTITION BY 子句：分割窗口

PARTITION BY 子句将结果集划分为多个分区（partition）。窗口函数将独立地应用于每个分区。可以将 PARTITION BY 理解为 GROUP BY 的窗口函数版本，但关键区别在于，PARTITION BY 不会像 GROUP BY 那样减少行数，而是保持原始行数不变，在每个分区内进行计算。

示例：计算每个部门的平均工资

假设我们有一个 employees 表，包含以下数据：

id	name	department	salary
1	Alice	Sales	60000
2	Bob	Sales	70000
3	Charlie	Marketing	80000
4	David	Marketing	90000
5	Eve	Engineering	100000
6	Frank	Engineering	110000

我们可以使用 PARTITION BY 子句计算每个部门的平均工资：

SELECT
    id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS department_avg_salary
FROM
    employees;

查询结果：

id	name	department	salary	department_avg_salary
5	Eve	Engineering	100000	105000.00
6	Frank	Engineering	110000	105000.00
3	Charlie	Marketing	80000	85000.00
4	David	Marketing	90000	85000.00
1	Alice	Sales	60000	65000.00
2	Bob	Sales	70000	65000.00

可以看到，结果集中的每一行都包含了该员工的工资以及该员工所在部门的平均工资。PARTITION BY department 将 employees 表划分为 Sales、Marketing 和 Engineering 三个分区，AVG(salary) 函数分别在每个分区内计算平均工资。

ORDER BY 子句：定义窗口内的排序

ORDER BY 子句定义了窗口内数据的排序方式。这对于一些需要考虑顺序的窗口函数（例如 ROW_NUMBER()、RANK()、LAG()、LEAD() 等）非常重要。ORDER BY 子句影响窗口函数如何计算其结果。

示例：计算每个部门的工资排名

我们可以使用 ORDER BY 子句计算每个部门的工资排名：

SELECT
    id,
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_rank
FROM
    employees;

查询结果：

id	name	department	salary	department_salary_rank
6	Frank	Engineering	110000	1
5	Eve	Engineering	100000	2
4	David	Marketing	90000	1
3	Charlie	Marketing	80000	2
2	Bob	Sales	70000	1
1	Alice	Sales	60000	2

RANK() 函数根据 ORDER BY salary DESC 定义的排序方式计算排名。PARTITION BY department 确保排名是针对每个部门单独计算的。

结合 PARTITION BY 和 ORDER BY：更复杂的分析

PARTITION BY 和 ORDER BY 可以结合使用，以实现更复杂的分析需求。

示例：计算每个部门工资高于平均工资的人数

我们可以结合 PARTITION BY 和 ORDER BY，以及 CASE WHEN 语句，来计算每个部门工资高于平均工资的人数：

SELECT
    department,
    COUNT(CASE WHEN salary > department_avg_salary THEN 1 END) AS num_above_avg
FROM (
    SELECT
        id,
        name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS department_avg_salary
    FROM
        employees
) AS subquery
GROUP BY department;

查询结果：

department	num_above_avg
Engineering	1
Marketing	1
Sales	1

这个例子首先使用子查询计算每个员工所在部门的平均工资，然后使用外部查询统计每个部门工资高于平均工资的人数。

FRAME 子句：进一步定义窗口

除了 PARTITION BY 和 ORDER BY，OVER 子句还可以包含 FRAME 子句，用于更精确地定义窗口的范围。FRAME 子句基于当前行定义窗口的起始和结束位置。

FRAME 子句有两种形式：

• ROWS frame: 基于行数定义窗口。
• RANGE frame: 基于值的范围定义窗口。

常见的 FRAME 子句的语法：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口从分区的第一行开始，到当前行结束。
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 窗口从当前行开始，到分区的最后一行结束。
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 窗口包括当前行，前一行和后一行。
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口从分区的第一行开始，到与当前行具有相同值的行结束（ORDER BY 列的值）。
• RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING: 窗口包括与当前行ORDER BY 列的值相差不超过1000的行。

示例：计算每个部门工资的累积总和

我们可以使用 FRAME 子句计算每个部门工资的累积总和：

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

查询结果：

id	name	department	salary	cumulative_salary
1	Alice	Sales	60000	60000
2	Bob	Sales	70000	130000
3	Charlie	Marketing	80000	80000
4	David	Marketing	90000	170000
5	Eve	Engineering	100000	100000
6	Frank	Engineering	110000	210000

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了一个窗口，从分区的第一行开始，到当前行结束。SUM(salary) 函数计算该窗口内的工资总和。

常用窗口函数总结

以下是一些常用的窗口函数：

函数	描述	是否需要 ORDER BY
ROW_NUMBER()	为每个分区中的每一行分配一个唯一的序号。	是
RANK()	为每个分区中的每一行分配一个排名，相同的值具有相同的排名，下一个排名会被跳过。	是
DENSE_RANK()	为每个分区中的每一行分配一个排名，相同的值具有相同的排名，下一个排名不会被跳过。	是
NTILE(n)	将每个分区中的行分成 n 组，并为每一行分配一个组号。	是
LAG(value, n, default)	返回分区中当前行之前第 n 行的值。	是
LEAD(value, n, default)	返回分区中当前行之后第 n 行的值。	是
FIRST_VALUE(value)	返回分区中第一行的值。	是
LAST_VALUE(value)	返回分区中最后一行的值。	是
SUM()	计算窗口内值的总和。	否 (但通常与 ORDER BY 一起使用)
AVG()	计算窗口内值的平均值。	否 (但通常与 ORDER BY 一起使用)
MIN()	计算窗口内值的最小值。	否 (但通常与 ORDER BY 一起使用)
MAX()	计算窗口内值的最大值。	否 (但通常与 ORDER BY 一起使用)
COUNT()	计算窗口内值的数量。	否 (但通常与 ORDER BY 一起使用)

注意事项

• 窗口函数只能在 SELECT 列表中或 ORDER BY 子句中使用。
• OVER 子句不能包含其他窗口函数。
• 某些窗口函数（例如 ROW_NUMBER()、RANK()、DENSE_RANK()）必须与 ORDER BY 子句一起使用。

实际应用场景

窗口函数在各种实际应用场景中都非常有用，例如：

• 财务分析: 计算移动平均线、累积总和、同比/环比增长率等。
• 市场营销: 计算客户的购买频率、最近购买日期、客户价值等。
• 运营分析: 计算服务器的平均响应时间、请求数量、错误率等。
• 数据挖掘: 识别异常值、发现数据趋势等。

总结

OVER 子句是 MySQL 窗口函数的核心，PARTITION BY 和 ORDER BY 子句允许我们精确地定义窗口的范围和排序方式。掌握这些概念和技巧，可以帮助我们进行更复杂的分析和计算，从而更好地理解和利用数据。

深入理解：窗口函数的核心

OVER 子句是窗口函数的灵魂，它定义了计算的范围和顺序。PARTITION BY 将数据分割成逻辑组，ORDER BY 确定了每个组内的排序，而 FRAME 子句则进一步细化了窗口的范围。 通过灵活运用这些子句，我们可以实现各种复杂的分析需求。

实践出真知：不断练习，掌握精髓

理解窗口函数最好的方法就是不断地练习和实践。 尝试使用不同的窗口函数和 OVER 子句组合，解决实际问题。 只有通过实践，才能真正掌握窗口函数的精髓，并在实际工作中灵活运用。