大家好，我是讯享网，很高兴认识大家。



一、什么是窗口函数

窗口函数（Window Functions）是SQL标准中的一个高级特性，它允许用户在不改变查询结果集行数的情况下，对每一行执行聚合计算或其他复杂的计算。这些计算是基于当前行与结果集中其他行之间的关系进行的。窗口函数特别适用于需要执行跨多行的计算，同时又想保持原始查询结果集的行数不变的场景。

1. 窗口函数的原理

窗口函数通过在查询结果集上定义一个“窗口”来工作，这个窗口可以是整个结果集，也可以是结果集的一个子集。窗口函数会对窗口内的行执行计算，并为每一行返回一个值。这个值是根据窗口内行的值以及窗口函数本身的逻辑计算得出的。

窗口函数不会改变查询结果集的行数，而是为每一行添加一个额外的列，这个列包含了窗口函数的计算结果。这使得窗口函数非常适合于需要在保持原始数据的同时进行聚合或其他复杂计算的场景。

2. 窗口函数的组成部分

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

3. 解释下窗口范围

MySQL的窗口函数中，指定窗口大小的语法主要是通过OVER()子句来实现的，其中可以使用ROWS或RANGE关键字来定义窗口的边界。不过，需要注意的是，ROWS和RANGE定义了窗口的范围是基于物理行位置还是列值，而不是直接指定窗口的“大小”。窗口的“大小”实际上是由这些范围参数以及ORDER BY子句共同决定的。

OVER()子句指定窗口范围的语法

其中，frame_specification定义了窗口的起始和结束位置，它有以下几种形式：

对于ROWS和RANGE，frame_start和frame_end可以是以下值之一：

ROWS是基于行的物理位置来确定窗口范围的，而RANGE则是基于ORDER BY子句中指定的列值来确定窗口范围的。RANGE在处理数值数据时特别有用，因为它可以包含与当前行值相近的其他行，即使它们的物理位置不相邻。

例子：

在第一个例子中，ROLLING_TOTAL计算了包括当前行在内的前三行的AMOUNT字段的总和。在第二个例子中，AVG_NEARBY_PRICE计算了当前PRICE值前后10个单位范围内的平均价格（注意，实际范围可能包括更多的行，因为RANGE会包含所有在这个范围内的行，即使它们的物理位置不是紧挨着的）。

需要注意的是，RANGE的使用可能会因为列值的分布和重复情况而变得复杂，因为它必须维护一个有序的数据结构来确定哪些行在指定的范围内。而ROWS则简单地基于行的物理顺序来计算窗口。

ROWS子句的常用选项

RANGE子句的常用选项

请注意，RANGE通常与ORDER BY子句一起使用，以确定窗口边界的逻辑顺序。而且，当使用RANGE时，如果列值有重复，则窗口可能会包含比预期更多的行。

RANGE的一个常见用途是计算移动平均值，尤其是当数据点不是均匀分布时。然而，在实践中，由于RANGE需要维护一个有序的数据结构，并且处理重复值时可能会导致性能问题，所以ROWS通常比RANGE更受欢迎.

4. 窗口函数与聚合函数的区别

窗口函数和聚合函数在MySQL中都是用于数据分析和报告的强大工具，但它们之间存在明显的区别。以下将通过具体例子来说明这两者的不同。

聚合函数（Aggregate Functions）

聚合函数作用于一组行，并返回单个值。常见的聚合函数有 SUM()、AVG()、MIN()、MAX() 和 COUNT() 等。这些函数通常与 GROUP BY 子句一起使用，以便对分组的数据进行聚合。

例子：假设有一个销售数据表 sales，包含 product_id、sale_date 和 amount 列。要计算每种产品的总销售额，可以使用聚合函数如下：

在这个例子中，SUM(amount) 是一个聚合函数，它对每个 product_id 分组内的 amount 值进行求和，返回每个产品的总销售额。结果集将包含更少的行，因为数据被聚合到了每个产品ID上。

窗口函数（Window Functions）

窗口函数作用于查询结果集的每一行，但它们的计算是基于一个“窗口”范围内的其他行。窗口函数不会减少结果集的行数，而是为每一行添加额外的计算结果。常见的窗口函数有 ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()（作为窗口函数使用）、AVG()（作为窗口函数使用）等。

例子：使用相同的 sales 表，如果我们想要计算每种产品在每一天的销售额，并且还想知道到那一天为止该产品的累计销售额，我们可以使用窗口函数如下：

在这个例子中，SUM(amount) OVER (…) 是一个窗口函数。它计算了到当前行为止（包括当前行），按 sale_date 排序的每个 product_id 的累计销售额。PARTITION BY product_id 表示数据首先按产品ID分区，然后在每个分区内按销售日期排序。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口范围，从分区的第一行到当前行。

结果集将包含与原始 sales 表相同数量的行，但会添加一个额外的 cumulative_sales 列，显示到每一行为止的累计销售额。

聚合函数减少结果集的行数，将多行数据聚合成单个值。 窗口函数保持结果集的行数不变，为每一行添加基于窗口范围内其他行的计算结果。 聚合函数通常与 GROUP BY 一起使用，而窗口函数则与 OVER() 子句一起使用来定义窗口的行为。

二、窗口函数分类

MySQL的窗口函数可以根据它们的功能和用途进行分类：

1. 序号窗口函数

序号函数为结果集中的每一行分配一个唯一的序号或排名。这些函数通常基于排序顺序和其他条件来分配这些序号。

ROW_NUMBER(): 为每一行分配一个唯一的序号。

RANK(): 为每一行分配一个排名，对于相同的值会留下空位。

DENSE_RANK(): 为每一行分配一个排名，但不会为相同的值留下空位。

讯享网

假设我们有一个名为employees的表，其中包含员工的信息，如下所示：

现在，我们想要为每个员工分配一个唯一的序号（使用ROW_NUMBER()），一个排名（使用RANK()），以及一个密集排名（使用DENSE_RANK()），都是基于他们的薪水。

在这个结果集中：

row_num 列显示了使用 ROW_NUMBER() 函数分配的唯一序号。序号是根据薪水降序排列的，所以薪水最高的员工（David）得到了序号 1。

rank 列显示了使用 RANK() 函数分配的排名。注意，当两个员工的薪水相同时，他们会获得相同的排名，并且下一个员工会跳过相应的排名。在这个例子中，Bob 和 Eva 都获得了排名 2，因此 Alice 和 Charlie 跳过了排名 3，直接获得了排名 4。

dense_rank 列显示了使用 DENSE_RANK() 函数分配的密集排名。与 RANK() 不同，DENSE_RANK() 不会在遇到重复值时留下任何间隔。因此，尽管 Bob 和 Eva 的薪水相同，但 Alice 和 Charlie 仍然获得了紧接着的密集排名 3。

2. 分布窗口函数

分布函数用于计算值在窗口内的相对位置或分布。

PERCENT_RANK(): 计算行的百分比排名。

CUME_DIST(): 计算行相对于所有其他行的累积分布。

当使用窗口函数 PERCENT_RANK() 和 CUME_DIST() 时，这些函数通常用于计算结果集中行的相对排名和累积分布。下面是一个示例，展示了如何在一个查询中同时使用这两个函数。

假设我们有一个名为 sales 的表，其中包含销售数据，如下所示：

现在，我们想要计算每一行销售额的百分比排名和累积分布。以下是查询的示例：

在这个结果集中：

percent_rank 列显示了使用 PERCENT_RANK() 函数计算的百分比排名。它是当前行的排名与总行数减1的比值，再乘以100。因为我们有5行数据，所以百分比排名的范围是0到1（包括0但不包括1），并且按 amount 降序排列。

cume_dist 列显示了使用 CUME_DIST() 函数计算的累积分布。它表示当前行的值小于或等于当前行的值的行数占总行数的比例。在这个例子中，CUME_DIST() 也是按 amount 降序排列的，所以最高销售额的行有最低的累积分布值（但不会是0，除非有相同的 amount 值），而最低销售额的行有最高的累积分布值（总是1）。

请注意，PERCENT_RANK() 和 CUME_DIST() 的计算结果可能会因数据库的实现和精度而略有不同，但上面的示例应该给出了大致的概念。此外，如果 amount 有相同的值，这两个函数的行为也会有所不同，PERCENT_RANK() 会为相同的值分配相同的百分比排名，而 CUME_DIST() 则会考虑相同值对累积分布的影响。

3. 前后窗口函数

前后函数允许您访问与当前行相关的前一行或后一行的值。

LAG(expr, offset, default): 返回指定偏移量之前的行的值。

LEAD(expr, offset, default): 返回指定偏移量之后的行的值。

4. 首尾窗口函数

首尾函数允许您获取窗口的第一行或最后一行的值。

FIRST_VALUE(expr): 返回窗口内第一行的值。

LAST_VALUE(expr): 返回窗口内最后一行的值。

需要注意的是，FIRST_VALUE() 和 LAST_VALUE() 在没有指定 ORDER BY 子句时可能不会按预期工作，因为窗口的顺序是不确定的。此外，LAST_VALUE() 在某些情况下可能不如使用 LEAD() 函数灵活。

举个栗子：我们假设有一个名为 stock_prices 的表，该表记录了某支股票每天的价格信息。

表结构如下：

现在，我们想要查询每天的股票价格，以及前一天和后一天的价格，还有该股票在记录期间的首日和末日的价格。以下是查询的示例：

请注意，LAST_VALUE() 默认不会按预期工作，因为它返回的是窗口内的最后一行，而不是整个结果集的最后一行。为了确保 LAST_VALUE() 返回整个结果集的最后一行，我们使用了 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这样它就会考虑整个分区（在这种情况下，整个结果集就是一个分区）。

在这个例子中，prev_day_price 列显示了前一天的价格（使用 LAG 函数），next_day_price 列显示了后一天的价格（使用 LEAD 函数），first_day_price 列显示了整个记录期间的首日价格（使用 FIRST_VALUE 函数），而 last_day_price 列显示了整个记录期间的末日价格（使用 LAST_VALUE 函数，并确保了正确的窗口范围）。

请注意，根据您的数据库系统，LAST_VALUE() 的行为可能有所不同，特别是在处理默认窗口时。上面的查询在某些数据库系统中可能需要调整，以确保 LAST_VALUE() 正确地返回整个结果集的最后一行。在某些情况下，您可能需要使用子查询或其他技术来实现这一点。

5. 聚合窗口函数

聚合函数作为窗口函数：SUM(), AVG(), MIN(), MAX() 等也可以作为窗口函数使用，为每一行计算累计、移动或其他聚合值

假设我们有一个名为 sales_data 的表，该表记录了不同销售人员的每日销售额。表结构如下：

现在，我们想要查询每位销售人员在每天的销售额，以及该销售人员到目前为止（从月初到当前日期）的平均销售额、最大销售额、总销售额和最小销售额。以下是查询的示例：

在这个查询中：

sales_date, salesperson_id, 和 sales_amount 列直接来自 sales_data 表。

avg_sales 列计算了从月初到当前日期每位销售人员的平均销售额。

max_sales 列计算了从月初到当前日期每位销售人员的最大销售额。

total_sales 列计算了从月初到当前日期每位销售人员的总销售额。

min_sales 列计算了从月初到当前日期每位销售人员的最小销售额。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 确保了窗口从当前分区的第一行开始，到当前行为止。在这种情况下，分区是由 PARTITION BY salesperson_id 定义的，每个销售人员的数据构成一个分区。ORDER BY sales_date 确保了数据按照销售日期排序，这对于计算累计的统计数据是必要的。

6. 其他函数

这个类别包括一些不属于上述类别的窗口函数，但仍然在窗口计算的上下文中非常有用。

NTH_VALUE(expr, n): 返回窗口内第n行的值。

NTILE(n): 将结果集分成指定数量的近似相等的组，并为每一行分配一个组号。

假设我们有一个销售数据表sales_data，其中包含每个销售人员的销售额和销售日期。

表结构如下：

现在，我们想要完成以下两个任务：

我们可以使用窗口函数来完成这些任务。首先，我们来完成第一个任务：

注意：上述查询有个问题，NTH_VALUE可能不会返回预期的结果，因为它并不保证只返回一行。当存在并列的销售额时，NTH_VALUE可能会返回多个销售人员的ID。为了解决这个问题，我们可能需要使用ROW_NUMBER()或DENSE_RANK()。但是，为了简化，我们假设没有并列的销售额，并稍微调整查询。

一个更准确的查询可能是这样的：

现在，让我们来完成第二个任务：

这个查询将每天的销售数据按照销售额降序排列，并使用NTILE(2)将它们分成两组。销售额较高的销售人员将被分配到第一组（sale_performance_group = 1），而销售额较低的销售人员将被分配到第二组（sale_performance_group = 2）。在每个日期内，销售额是独立分组的。

三、常见的应用场景

窗口函数在多个场景中非常有用，以下是几个典型示例：

四、优化策略

虽然窗口函数功能强大，但在处理大量数据时，性能可能会成为问题。以下是一些优化策略：

五、总结

MySQL窗口函数为数据分析和报表生成提供了强大的工具。通过深入理解其原理和应用场景，并采用有效的优化策略，可以充分发挥窗口函数在数据处理和分析中的优势。随着数据量的不断增长和分析需求的日益复杂，掌握窗口函数将成为数据库开发人员和数据分析师的重要技能之一。

术因分享而日新，每获新知，喜溢心扉。 诚邀关注公众号 『 』 ，获取更多技术资料。