数据库 - Mysql - 窗口函数 - 个人技术文章分享

1、窗口函数对MYSQL的版本要求情况

窗口函数在MySQL中的支持情况如下：

窗口函数的支持从MySQL 8.0版本开始引入。因此，如果您使用的是MySQL 8.0或更高版本，您可以使用窗口函数进行分析和报表。

在MySQL 8.0中，引入了一系列窗口函数，包括但不限于以下常见的窗口函数：

ROW_NUMBER()
RANK(), DENSE_RANK(), NTILE()
SUM(), AVG(), MIN(), MAX()
LAG(), LEAD()
FIRST_VALUE(), LAST_VALUE()

如果您的MySQL版本低于8.0，窗口函数将不可用。在较旧的MySQL版本中，您可能需要使用子查询或其他方法来实现类似窗口函数的功能。

因此，要使用窗口函数，建议您将MySQL升级到8.0或更高版本。升级到支持窗口函数的版本将为您提供更多的功能和灵活性，使数据分析和报表生成更加方便。

2、应用场景与性能影响

窗口函数在许多数据分析和报告场景中非常有用，它们提供了在结果集内执行聚合、排序和分析的能力。以下是窗口函数的一些常见应用场景：

排名和排序: 窗口函数可用于计算数据的排名、行号或排序顺序。例如，ROW_NUMBER()函数可用于为每行分配唯一的行号，RANK()和DENSE_RANK()函数可用于计算行的排名。
累计计算: 窗口函数可用于进行累计计算，如计算累计和、累计平均值、累计最大/最小值等。例如，SUM()和AVG()函数可以与ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW结合使用，计算当前行之前的累计和或平均值。
分组聚合: 窗口函数可以在保留原始数据行的同时，进行分组级别的聚合计算。与传统的GROUP BY聚合不同，窗口函数将聚合结果添加为原始数据行的一列，而不会减少结果集的行数。例如，可以使用窗口函数计算每个分组的总和、平均值或最大/最小值。
前后行比较: 窗口函数可用于在结果集内比较当前行与前一行或后一行的值。这对于计算增长率、差异或滑动窗口分析等非常有用。例如，使用LAG()和LEAD()函数可以获取前一行和后一行的值。

关于性能方面，窗口函数的性能取决于查询的复杂性、数据量和索引的使用情况。在某些情况下，窗口函数可能会对性能产生一定的影响。窗口函数通常需要对整个结果集进行排序和分组，这可能会增加查询的计算和I/O开销。

为了最大程度地优化性能，可以考虑以下几点：

适当索引：使用合适的索引来支持窗口函数的排序和分组操作，以减少排序和聚合的开销。
限制结果集大小：如果可能的话，通过适当的过滤条件、分页或其他手段限制结果集的大小，以减少窗口函数的计算量。
注意窗口帧：窗口帧的定义可能会对性能产生影响。根据实际需求选择合适的窗口帧，避免计算不必要的行。

总的来说，窗口函数提供了强大的分析功能，但在使用时需要权衡查询需求和性能要求，结合索引和优化技术以提高查询性能。

3、语法

窗口函数的一般语法格式如下：

窗口函数() OVER (PARTITION BY 列1 [, 列2, ...] ORDER BY 列A [, 列B, ...] [窗口帧])

其中，关键部分包括：

窗口函数：要应用的窗口函数，例如 ROW_NUMBER(), SUM(), AVG(), MAX(), MIN() 等等。窗口函数会在指定的窗口内计算聚合值或其他操作。
OVER：指示后面跟随的是窗口函数的定义。
PARTITION BY：可选项，用于指定分区键，根据分区键将数据划分为不同的分区。
ORDER BY：可选项，用于指定排序键，对分区内的数据进行排序。
窗口帧：可选项，用于指定窗口函数计算的数据范围。窗口帧定义了在分区内计算窗口函数的行的范围，可以包括行的前后范围、行的偏移等。

注意，PARTITION BY和ORDER BY子句是可选的，具体取决于您的需求。如果不指定这两个子句，则窗口函数将在整个结果集上进行计算。

以下是一个示例，展示了窗口函数的语法使用：

SELECT column1, column2, 
       SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS sum_column3
FROM your_table

在这个示例中，我们使用了SUM()窗口函数来计算在每个column1分区内，按照column2的顺序对column3进行求和。PARTITION BY column1指定了根据column1进行分区，ORDER BY column2指定了按照column2进行排序。

请注意，具体的窗口函数和可用的窗口帧取决于您使用的数据库管理系统和版本。不同的数据库管理系统可能会有稍微不同的窗口函数语法和支持的函数列表。

4、OVER关键字

在窗口函数中，OVER子句用于定义窗口的范围或分区，即确定窗口函数将在哪个数据子集上进行计算。它提供了对数据的逻辑分组、排序和过滤的控制。

OVER子句可以包含以下内容：

PARTITION BY: 使用PARTITION BY子句可以将数据分成不同的分区，每个分区内的数据将独立计算窗口函数。您可以根据一个或多个列对数据进行分区，将相同值的行分配到同一个分区中。
ORDER BY: 使用ORDER BY子句可以指定分区内数据的排序规则。窗口函数将按照指定的排序规则对分区内的数据进行排序，以便在计算窗口函数时得到正确的结果。可以根据一个或多个列进行排序。
窗口帧（Window Frame）: 窗口帧定义了在窗口函数计算中要考虑的行的范围。它可以用来限制窗口函数的计算范围，例如，计算当前行及其前面的几行或后面的几行。窗口帧使用ROWS关键字指定，并可以通过以下选项进行进一步定义：BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW、BETWEEN n PRECEDING AND CURRENT ROW、BETWEEN UNBOUNDED PRECEDING AND n FOLLOWING等。

通过使用OVER子句，您可以控制窗口函数的计算范围、分组和排序方式，从而根据需求灵活地进行数据分析和报表生成。它为窗口函数提供了更强大的功能和灵活性。

5、示例

1、查询每个客户最后一次订房间的房间ID信息传统查询方式：

SELECT rr.customer_id, rr.end_at, rr.room_id
FROM room_reserve rr
INNER JOIN (
    SELECT customer_id, MAX(end_at) AS max_end_at
    FROM room_reserve
    GROUP BY customer_id
) subquery ON rr.customer_id = subquery.customer_id AND rr.end_at = subquery.max_end_at

传统的方式需要先根据客户ID分组归类查询出每个用户的最后定房时间，再根据时间去查与最大时间相同的记录。但这仍然可能出现多条记录。

窗口函数查询方式：

SELECT customer_id, end_at, room_id
FROM (
    SELECT customer_id, end_at, room_id, 
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY end_at DESC) AS rn
    FROM room_reserve
) subquery
WHERE rn = 1

在这个查询中，内部子查询使用窗口函数ROW_NUMBER()来为每个用户按照end_at降序分配行号。然后，最外层的查询选择行号为1的记录，即每个用户的最大end_at值和相应的room_id。

这种方法不需要显式的GROUP BY子句，可以更简洁地获取每个用户的最大值记录。请确保您的MySQL版本支持窗口函数。