MySQL8.0 窗口函数 range vs rows

概述

本文主要是为了讲解 MySQL8 中的窗口函数中 frame 单位 range 和 rows 的不同点， 为了方便叙述，假设有一张销售额表，记录店铺销售额，以下数据为店铺 shop1 和 shop2 第二季度的销售额，数据如下：

注：为了让大家更方便的心算，所以特地将销售额的数值做成很小数字。

窗口

window_function_name(expression) OVER (
  [partition_definition]
  [order_definition]
  [frame_definition]
)

窗口是由 partition_definition 定义的，举个例子：

select period as '月份', shop as '店铺', revenue as '月销售额',
       sum(revenue) over(partition by shop) as '季度销售总额'
from sales

上面的 sql 中使用了 shop 作为窗口定义的依据，由于结果集中只有两个 shop（shop1 和 shop2），因此结果集被分为两个窗口，sum 函数是作用于窗口的，查询结果如下：

frame

之前说窗口函数作用于窗口，但是确切的说窗口函数是作用于 frame 的，如果将窗口看做满足特定条件的行的集合，那么 frame 则是窗口的子集。一个 frame 是相对于 CURRENT ROW 来定义的。来个例子，下面的 frame 表达的含义为窗口开始到当前行所包含的所有行。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

由于 frame 是基于 CURRENT ROW 定义的，因此该 frame 也可以简写成下面的形式：

ROWS UNBOUNDED PRECEDING

range vs rows

MySQL 官方文档中指出了 range 和 rows 的不同：

The frame_units value indicates the type of relationship between the current row and frame rows:

ROWS: The frame is defined by beginning and ending row positions. Offsets are differences in row numbers from the current row number.

RANGE: The frame is defined by rows within a value range. Offsets are differences in row values from the current row value.

但是这个描述属实难以理解，仍然以店铺销售额的数据为例，我们分别使用 rows 和 range 来定义 frame，看看查询结果有何不同：

SELECT period, shop, revenue,
SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    ROWS UNBOUNDED PRECEDING
) AS '累计销售额'
FROM sales;

官方文档中指出，ROWS 定义了开始行和结束行的位置，开始行和结束行所包含的所有行组成了当前 frame。对照上面的查询语句，开始行为 UNBOUNDED PRECEDING，由于结束行没有定义，默认为 CURRENT ROW。

以上图的窗口1为例说明，窗口1中总共包含了三行，编号分别为1、2、3。第1行的是窗口的开始，第3行是窗口的结束。下面的表格给出了窗口1中的每一行对应的 frame 所包含的行，以及窗口函数的计算结果：

行号	frame 包含的行	sum(revenue)	说明
1	1	4	当前行为窗口的第1行，因此 UNBOUNDED PRECEDING 不包含任何行
2	1,2	9	当前行为窗口的第2行，因此 UNBOUNDED PRECEDING 为行1，因此 frame 包含第1和第2行
3	1,2,3	12	当前行为窗口的第3行，因此 UNBOUNDED PRECEDING 为行1和行2，因此 frame 包含第1和第2行和第3行

注：UNBOUNDED PRECEDING 表示窗口开始位置。

上面是 ROWS 的查询结果，接下来使用 RANGE 看查询结果有何不同：

SELECT period, shop, revenue,
SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    RANGE UNBOUNDED PRECEDING
) AS '累计销售额'
FROM sales;

官方文档中指出，RANGE 依据行的值来定义开始行和结束行的位置。问题是一个行中有多个值，依据哪一列的值？答案是依据 over 字句中的 order by 列的值。具体到我们这里，就是依据 period 列的值来确定逻辑行，先看下图：

下面的表格给出了窗口2中的每一行对应的 frame 所包含的行，以及窗口函数的计算结果：

物理行号	逻辑行号	frame 包含的行	sum(revenue)	说明
4	1	4	1	当前行为4，UNBOUNDED PRECEDING 不包含任何行
5	2	4,5,6	2	当前行为5，实际包含第5第6两行，UNBOUNDED PRECEDING 为行4，因此 frame 包含第4、5、6
6	2	4,5,6	2	当前行为6，实际包含第5第6两行，UNBOUNDED PRECEDING 为行4，因此 frame 包含第4、5、6
7	3	4,5,6,7	5	当前行为7，UNBOUNDED PRECEDING 为行4、5、6，因此 frame 包含4、5、6、7

只有理解了 frame 包含了哪些物理行，才能理解 sum 最后的计算结果。第5和第6行包含的物理行是相同的，因此他们的 sum 结果也是相同的。

总结

• ROWS 依据行号来区分行，并以此来确定 frame 的开始和结束位置；
• RANGE 依据 OVER 表达式中 ORDER BY 列的值来区分行，值相同的行在逻辑上被认为是同一行。并以此逻辑行来确定 frame 的开始和结束位置。