MySQL窗口函数实现数据的按照特定条件分组排序

一、什么是MySQL窗口函数？

MySQL窗口函数与标准聚合函数的一个重要区别在于它们可以基于要分组的列计算结果，而不会产生单独分组的结果行。它们可以被认为是多行版本的聚合函数，可以让我们在不改变查询结果外观的情况下，对于要分组的列返回聚合计算后的结果。从MySQL 8.0.2版本开始，提供了对窗口函数的支持。

窗口函数在一个逻辑窗口中应用一个聚合函数，然后通过该窗口将结果分配给各个行。每个逻辑窗口在查询结果中对应一个物理窗口，它由不同语句块组成，以OVER子句定义且与可能存在的GROUP BY子句无关。

二、MySQL窗口函数的使用场景及优点

窗口函数是一种强大的SQL技术，可以应用于许多场景。例如：

计算前/后n行，这有时被称为“滑动平均”并可用于支持更深入的分析。
计算每个行对其他行的偏差。
基于统计信息生成智能的验证码。
对于一组查询数据的聚合计算

使用窗口函数可以避免多个查询操作，减少查询的时间和工作量，特别是在大数据量下，使用聚合函数时效率显著提高，同时可以在只执行一次脚本的情况下实现多个结果集输出。

三、MySQL窗口函数的语法和使用方法

基础语法为:

窗口函数名称(Syntax)
() OVER [PARTITION BY partition_name[,…]]
[ORDER BY expression [ASC|DESC],…]

其中：

窗口函数名称：MySQL支持多个窗口函数，包括SUM、ROW_NUMBER、RANK、DENSE_RANK、NTILE、FIRST_VALUE、LAST_VALUE、LEAD、LAG等等。
PARTITION BY：可选项，用于分组。通过PARTITION BY将数据分组后就可以对各个组中的数据应用窗口函数。如果省略此子句，则将所有行视为单个分组。
ORDER BY：排序方式，窗口排序根据ORDER BY子句定义的挑选方式进行。

下面是一个使用窗口函数的例子:

SELECT department_id, CONCAT(last_name,' ',first_name) AS full_name, salary,
AVG(salary) OVER (ORDER BY salary) AS avg_salaries
FROM employees
WHERE department_id IN (30, 100)
ORDER BY department_id, salary DESC;

该语句可解读为：在部门30和100中，以工资降序排序，然后计算每个员工的平均工资（使用AVG函数）。

四、MySQL窗口函数实现数据的按照特定条件分组排序

窗口函数可实现数据的按照特定条件分组排序，下面是一个例子。

假设某公司有个部门表，需要列出所有部门名称及每个部门中工资最高的前3个员工，按部门ID升序排列（部门ID一致时，以工资降序排列），该如何实现？

首先，我们可以用以下语句查找每个部门的三个最高薪水员工：

SELECT * FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS ranks
    FROM employees
) AS temp
WHERE ranks <= 3;

这将返回一个包含每个部门的前三个薪水的结果集。

接下来，我们可以用以下语句将这个结果集与department表连接，以包含每个部门的部门名称：

SELECT d.department_name, temp.*
FROM departments d
LEFT JOIN (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS ranks
    FROM employees
) AS temp
ON d.department_id = temp.department_id
WHERE ranks <= 3
ORDER BY d.department_id, temp.salary DESC;

该语句可解读为：使用JOIN函数联接departments表和employees表，找到每个部门的三个最高薪水员工（使用ROW_NUMBER函数）。

五、MySQL窗口函数的注意事项

虽然窗口函数弥补了标准聚合函数的一个重要缺陷，但是在使用时还是需要注意以下几个方面：

要记住，窗口函数不是聚合函数，它们在结果集和分组之后进行操作。
与所有SQL语句一样，窗口函数的效率取决于很多因素，例如计算行的数量、数据类型和运行环境。因此，在执行分析时，要确保对SQL查询进行充分的测试和分析。
在使用窗口函数时，尽可能使用PARTITION BY，这是使用窗口函数的最大优势之一。

六、总结

MySQL窗口函数提供了强大的SQL功能，它可以让我们更轻松地处理不同情况下的数据，并且在大数据集上节省了大量时间和努力，同时保证了结果的准确性。虽然在使用窗口函数时需要注意一些问题，但是以正确的方式使用它们可以产生重大的收益。

原创文章，作者：MVYGZ，如若转载，请注明出处：https://www.506064.com/n/332840.html