mysql8.0新特性开窗函数（Window Functions）

mysql8.0新特性开窗函数（Window Functions）

MySQL 8.0 引入了开窗函数（Window Functions），这是一种用于在查询结果中执行聚合计算、排序和排名等操作的强大功能。开窗函数能够让你在查询结果集的特定窗口范围内计算值，而无需分组数据。

以下是一些常见的开窗函数：ROW_NUMBER()：为结果集中的每一行分配一个唯一的数字。通常用于为行创建一个序号。RANK()：计算结果集中每一行的排名，相同值的行具有相同的排名，并跳过后续排名。DENSE_RANK()：与 RANK() 类似，但不跳过后续排名，相同值的行具有相同的稠密排名。NTILE(n)：将结果集划分为 n 个近似相等的部分，并为每个部分分配一个标识。LEAD(expression, offset, default)：获取结果集中当前行之后的第 offset 行的值，可以指定默认值。LAG(expression, offset, default)：获取结果集中当前行之前的第 offset 行的值，可以指定默认值。FIRST_VALUE(expression)：获取结果集中指定列的第一个非空值。LAST_VALUE(expression)：获取结果集中指定列的最后一个非空值。SUM(expression) OVER (partition by ... order by ...)：在指定的分区和排序条件下计算累积和。AVG(expression) OVER (partition by ... order by ...)：在指定的分区和排序条件下计算累积平均值。MIN(expression) OVER (partition by ... order by ...)：在指定的分区和排序条件下计算最小值。MAX(expression) OVER (partition by ... order by ...)：在指定的分区和排序条件下计算最大值。

开窗函数示例

SELECT
    order_number,
    order_date,
    total_amount,
    SUM(total_amount) OVER() AS total_sum,
    SUM(total_amount) OVER(ORDER BY order_date) AS running_total
FROM
    orders;

SUM(total_amount) OVER()：计算了所有订单的总金额，将该总金额应用于每一行结果。

SUM(total_amount) OVER(ORDER BY order_date)：计算按照订单日期排序的运行总金额，即在每一行的位置上都计算了截止到该行的总金额。

通过使用开窗函数，我们可以在不进行分组操作的情况下，轻松地对结果集进行聚合计算和排序。这极大地增强了 SQL 查询的灵活性和功能性。

传统的 SQL 查询区别和优势

聚合操作无需分组： 传统的 SQL 聚合操作需要使用 GROUP BY 子句对数据进行分组，而开窗函数无需分组即可在整个结果集上进行聚合计算，从而减少了分组操作的复杂性。

分析功能更强大： 开窗函数能够在结果集的窗口中执行各种分析、排序和排名操作，而传统的 SQL 查询可能需要多次查询和临时表来实现相同的功能。

排名和排序更灵活： 使用开窗函数，可以轻松地在结果集中计算排名和排序，同时处理相同值的情况，而传统的排序可能需要额外的逻辑来处理排名并跳过相同值。

统计值的计算更简单： 使用开窗函数，可以直接在结果集中计算累积和、累积平均值、累积最小值和累积最大值，而传统的 SQL 查询可能需要复杂的子查询或自连接。

减少查询次数： 开窗函数可以在单个查询中执行多个操作，而传统的 SQL 查询可能需要多次查询来获得相同的结果。

提高性能和可读性： 使用开窗函数可以减少查询的复杂性和冗余，从而提高查询的性能和可读性。