MySQL 8.0 窗口函数：ROWS BETWEEN 与 RANGE BETWEEN 的爱恨情仇 (以及我们如何驾驭它们)
各位观众老爷们，晚上好！我是你们的老朋友，人称“代码界的段子手”的程序员老王。今天咱们不聊996，不谈内卷，来点轻松愉快的——MySQL 8.0 窗口函数中的 ROWS BETWEEN 和 RANGE BETWEEN。
别看这俩名字挺唬人，其实就是让你在窗口函数里划定一个范围，告诉MySQL：嘿，哥们，我只要这个范围里的数据参与计算！就像你在年会上抽奖，总得先规定一个抽奖范围吧？（别告诉我你年年都抽到阳光普照奖，心疼你三秒… 😭）
一、窗口函数：数据库的“变形金刚”
首先，我们先简单回顾一下窗口函数。如果你已经对它了如指掌，可以直接跳到下一节。
窗口函数，顾名思义，就是在数据集中划出一个“窗口”，然后针对这个窗口里的数据进行计算。它和GROUP BY很像，但是最大的区别在于：GROUP BY会把数据聚合，导致行数变少；而窗口函数则不会，它会在每一行数据旁边，附带上窗口计算的结果，就像给每一行数据都穿上了“外挂”。
你可以把窗口函数想象成数据库的“变形金刚”，它可以根据你的需要，变形出各种各样的计算结果，而且还不会影响原始数据的结构。是不是很酷炫？😎
窗口函数的基本语法如下：
function(argument) OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...]
[frame_clause]
)function(argument)：你想使用的窗口函数，比如SUM(),AVG(),ROW_NUMBER(),RANK()等等。OVER()：这是窗口函数的灵魂！它告诉MySQL这是一个窗口函数，而不是普通的聚合函数。PARTITION BY column1, column2, ...：可选，用于将数据集分成多个分区，每个分区就是一个独立的窗口。就像把一个班级分成几个小组，每个小组单独评奖。ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...：可选，用于指定窗口内数据的排序方式。就像小组内部，按照成绩排名。frame_clause：可选，这就是我们今天的主角！用于定义窗口的范围，也就是我们说的ROWS BETWEEN和RANGE BETWEEN。
二、ROWS BETWEEN：按行数划定势力范围
ROWS BETWEEN，顾名思义，就是按照行数来定义窗口的范围。它就像一个严谨的“数格子专家”，一丝不苟地按照行数来划定窗口范围。
ROWS BETWEEN 的语法如下：
ROWS BETWEEN frame_start AND frame_end其中，frame_start 和 frame_end 可以是以下几种选项：
UNBOUNDED PRECEDING：表示窗口从第一行开始。UNBOUNDED FOLLOWING：表示窗口到最后一行结束。CURRENT ROW：表示当前行。n PRECEDING：表示当前行之前的n行。n FOLLOWING：表示当前行之后的n行。
为了更好地理解，我们来看几个例子：
例1：计算每个员工的薪水，以及他和他前面一位员工的薪水总和。
假设我们有一个名为employees的表，包含以下数据：
| employee_id | employee_name | salary |
|---|---|---|
| 1 | 张三 | 5000 |
| 2 | 李四 | 6000 |
| 3 | 王五 | 7000 |
| 4 | 赵六 | 8000 |
我们可以使用以下SQL语句来实现：
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS salary_sum
FROM
employees;执行结果如下：
| employee_name | salary | salary_sum |
|---|---|---|
| 张三 | 5000 | 5000 |
| 李四 | 6000 | 11000 |
| 王五 | 7000 | 13000 |
| 赵六 | 8000 | 15000 |
在这个例子中，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示窗口范围为：当前行和当前行之前的1行。因此，对于李四来说，窗口范围就是张三和李四，薪水总和就是5000 + 6000 = 11000。
例2：计算每个员工的薪水，以及他和他后面一位员工的薪水总和。
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS salary_sum
FROM
employees;执行结果如下：
| employee_name | salary | salary_sum |
|---|---|---|
| 张三 | 5000 | 11000 |
| 李四 | 6000 | 13000 |
| 王五 | 7000 | 15000 |
| 赵六 | 8000 | 8000 |
在这个例子中，ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING 表示窗口范围为：当前行和当前行之后的1行。因此，对于王五来说，窗口范围就是王五和赵六，薪水总和就是7000 + 8000 = 15000。
总结：
ROWS BETWEEN按照行数来划定窗口范围，简单粗暴，易于理解。- 适用于需要精确控制窗口大小的场景。
三、RANGE BETWEEN：按值划定势力范围
RANGE BETWEEN 则更加灵活，它是按照值来定义窗口的范围。它不像 ROWS BETWEEN 那样死板地数格子，而是根据指定的列的值，来动态地划定窗口范围。
RANGE BETWEEN 的语法如下：
RANGE BETWEEN frame_start AND frame_end其中，frame_start 和 frame_end 可以是以下几种选项：
UNBOUNDED PRECEDING：表示窗口从第一行开始，与ORDER BY的列的值无关。UNBOUNDED FOLLOWING：表示窗口到最后一行结束，与ORDER BY的列的值无关。CURRENT ROW：表示当前行，值与ORDER BY的列的值相等的所有行。n PRECEDING：表示值小于等于当前行ORDER BY列的值减去 n 的所有行。n FOLLOWING：表示值大于等于当前行ORDER BY列的值加上 n 的所有行。
注意：
RANGE BETWEEN必须和ORDER BY子句一起使用。RANGE BETWEEN只能用于数值类型或日期类型。
我们继续用例子来说明：
例1：计算每个员工的薪水，以及薪水差值在1000以内的员工的薪水总和。
还是用上面的employees表。
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS salary_sum
FROM
employees;执行结果如下：
| employee_name | salary | salary_sum |
|---|---|---|
| 张三 | 5000 | 11000 |
| 李四 | 6000 | 18000 |
| 王五 | 7000 | 21000 |
| 赵六 | 8000 | 15000 |
在这个例子中，RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING 表示窗口范围为：薪水小于等于当前员工薪水减去1000，且大于等于当前员工薪水加上1000的所有员工。
- 对于张三 (5000) 来说，窗口范围是 4000 到 6000，包括张三和李四，薪水总和是 5000 + 6000 = 11000。
- 对于李四 (6000) 来说，窗口范围是 5000 到 7000，包括张三、李四和王五，薪水总和是 5000 + 6000 + 7000 = 18000。
例2：统计每天的订单数量，以及前后两天（包括当天）的订单总数量。
假设我们有一个名为orders的表，包含以下数据：
| order_date | order_count |
|---|---|
| 2023-10-26 | 10 |
| 2023-10-27 | 15 |
| 2023-10-28 | 20 |
| 2023-10-29 | 25 |
| 2023-10-30 | 30 |
我们可以使用以下SQL语句来实现：
SELECT
order_date,
order_count,
SUM(order_count) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING) AS total_order_count
FROM
orders;执行结果如下：
| order_date | order_count | total_order_count |
|---|---|---|
| 2023-10-26 | 10 | 45 |
| 2023-10-27 | 15 | 70 |
| 2023-10-28 | 20 | 90 |
| 2023-10-29 | 25 | 90 |
| 2023-10-30 | 30 | 75 |
在这个例子中，RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING 表示窗口范围为：订单日期在当前日期之前2天（包括当天）和之后2天（包括当天）的所有订单。
总结：
RANGE BETWEEN按照值来划定窗口范围，更加灵活，可以根据数据的特性来动态调整窗口大小。- 适用于需要根据值的范围进行计算的场景，例如统计某个时间段内的数据。
- 需要注意的是，
RANGE BETWEEN必须和ORDER BY子句一起使用，并且只能用于数值类型或日期类型。
四、ROWS BETWEEN vs RANGE BETWEEN：谁是你的菜？
现在，我们来总结一下 ROWS BETWEEN 和 RANGE BETWEEN 的区别：
| 特性 | ROWS BETWEEN | RANGE BETWEEN |
|---|---|---|
| 定义范围 | 按照行数 | 按照值 |
| 适用场景 | 需要精确控制窗口大小的场景 | 需要根据值的范围进行计算的场景 |
是否需要 ORDER BY |
可选 | 必须 |
| 适用数据类型 | 所有数据类型 | 数值类型或日期类型 |
| 灵活性 | 较低 | 较高 |
那么，在实际应用中，我们应该选择哪一个呢？
- 如果你需要精确控制窗口的大小，并且不在乎数据的具体值，那么
ROWS BETWEEN是你的不二之选。 就像你要统计一个班级里，每个学生的成绩和他前后两位同学的平均成绩，用ROWS BETWEEN就很方便。 - 如果你需要根据数据的范围进行计算，并且希望窗口的大小能够动态调整，那么
RANGE BETWEEN才是你的菜。 就像你要统计一段时间内的销售额，并且希望窗口的大小能够根据日期范围来动态调整，用RANGE BETWEEN就更合适。
当然，具体选择哪一个，还是要根据你的实际需求来决定。就像选择女朋友一样，没有最好的，只有最适合你的。 (手动滑稽 😜)
五、实战演练：用案例说话
说了这么多理论，不如来几个实战案例，让大家更深入地理解 ROWS BETWEEN 和 RANGE BETWEEN 的用法。
案例1：计算每个用户的连续登录天数。
假设我们有一个名为user_login的表，包含以下数据：
| user_id | login_date |
|---|---|
| 1 | 2023-10-26 |
| 1 | 2023-10-27 |
| 1 | 2023-10-28 |
| 1 | 2023-10-30 |
| 2 | 2023-10-27 |
| 2 | 2023-10-28 |
| 2 | 2023-10-29 |
我们可以使用以下SQL语句来计算每个用户的连续登录天数：
SELECT
user_id,
login_date,
COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW) AS consecutive_days
FROM
user_login;执行结果如下：
| user_id | login_date | consecutive_days |
|---|---|---|
| 1 | 2023-10-26 | 1 |
| 1 | 2023-10-27 | 2 |
| 1 | 2023-10-28 | 3 |
| 1 | 2023-10-30 | 1 |
| 2 | 2023-10-27 | 1 |
| 2 | 2023-10-28 | 2 |
| 2 | 2023-10-29 | 3 |
案例2：计算每个商品的移动平均价格。
假设我们有一个名为product_price的表，包含以下数据：
| product_id | price_date | price |
|---|---|---|
| 1 | 2023-10-26 | 10 |
| 1 | 2023-10-27 | 12 |
| 1 | 2023-10-28 | 15 |
| 1 | 2023-10-29 | 13 |
| 1 | 2023-10-30 | 14 |
我们可以使用以下SQL语句来计算每个商品的3天移动平均价格：
SELECT
product_id,
price_date,
price,
AVG(price) OVER (PARTITION BY product_id ORDER BY price_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_price
FROM
product_price;执行结果如下：
| product_id | price_date | price | moving_average_price |
|---|---|---|---|
| 1 | 2023-10-26 | 10 | 10 |
| 1 | 2023-10-27 | 12 | 11 |
| 1 | 2023-10-28 | 15 | 12.3333 |
| 1 | 2023-10-29 | 13 | 13.3333 |
| 1 | 2023-10-30 | 14 | 14 |
通过这两个案例，相信大家对 ROWS BETWEEN 和 RANGE BETWEEN 的用法有了更深入的理解。
六、总结：掌握窗口函数，解锁数据分析新姿势
今天，我们一起学习了 MySQL 8.0 窗口函数中的 ROWS BETWEEN 和 RANGE BETWEEN，了解了它们的区别和适用场景，并通过实战案例加深了理解。
掌握了窗口函数，就相当于掌握了一把数据分析的瑞士军刀，可以轻松解决各种复杂的数据分析问题。
希望今天的分享能够帮助大家更好地理解和使用窗口函数，解锁数据分析的新姿势！💪
最后，别忘了点赞、评论、转发，让更多的小伙伴们受益！我们下期再见！👋