目录
1.窗口函数简介
MySQL 开窗函数（Window Functions）是 MySQL 8.0 版本引入的一个强大特性，它可以用于计算聚合的同时提供数据行的上下文信息。开窗函数可以分为以下几类：
- 聚合开窗函数：SUM(), AVG(), MIN(), MAX() 。
- 排名开窗函数：ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK() 。
- 首尾开窗函数：LEAD(), LAG(),LAST_VALUE(),FIRST_VALUE(),NTH_VALUE()。
- 其他：CUME_DIST() 、NTILE()。
窗口函数示例1：
mysql> SELECT
time, subject, val,
SUM(val) OVER (PARTITION BY subject ORDER BY time
ROWS UNBOUNDED PRECEDING)
AS running_total,
AVG(val) OVER (PARTITION BY subject ORDER BY time
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS running_average
FROM observations;
+----------+---------+------+---------------+-----------------+
| time | subject | val | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113 | 10 | 10 | 9.5000 |
| 07:15:00 | st113 | 9 | 19 | 14.6667 |
| 07:30:00 | st113 | 25 | 44 | 18.0000 |
| 07:45:00 | st113 | 20 | 64 | 22.5000 |
| 07:00:00 | xh458 | 0 | 0 | 5.0000 |
| 07:15:00 | xh458 | 10 | 10 | 5.0000 |
| 07:30:00 | xh458 | 5 | 15 | 15.0000 |
| 07:45:00 | xh458 | 30 | 45 | 20.0000 |
| 08:00:00 | xh458 | 25 | 70 | 27.5000 |
+----------+---------+------+---------------+-----------------+
窗口函数示例2：
mysql> SELECT
time, subject, val,
FIRST_VALUE(val) OVER w AS 'first',
LAST_VALUE(val) OVER w AS 'last',
NTH_VALUE(val, 2) OVER w AS 'second',
NTH_VALUE(val, 4) OVER w AS 'fourth'
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time
ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time | subject | val | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113 | 10 | 10 | 10 | NULL | NULL |
| 07:15:00 | st113 | 9 | 10 | 9 | 9 | NULL |
| 07:30:00 | st113 | 25 | 10 | 25 | 9 | NULL |
| 07:45:00 | st113 | 20 | 10 | 20 | 9 | 20 |
| 07:00:00 | xh458 | 0 | 0 | 0 | NULL | NULL |
| 07:15:00 | xh458 | 10 | 0 | 10 | 10 | NULL |
| 07:30:00 | xh458 | 5 | 0 | 5 | 10 | NULL |
| 07:45:00 | xh458 | 30 | 0 | 30 | 10 | 30 |
| 08:00:00 | xh458 | 25 | 0 | 25 | 10 | 30 |
+----------+---------+------+-------+------+--------+--------+
2.窗口的定义
窗口的单位(frame unit)：
- ROWS：表示当前行和 frame 行之间的偏移量是行号之间的差异
- RANGE：表示当前行和 frame 行之间的偏移量是行值与当前行值之间的差异
窗口的范围：
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
窗口参数示例：
10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING
注： 如果使用的是RANGE，则需要根据窗口排序中的列，选择对应的时间单位
常用的时间单位：MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR…
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
-> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
-> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
3.相关题目示例
3.1 PERCENT_RANK()
PERCENT_RANK()函数返回一个从0到1的数字。
对于指定的行,PERCENT_RANK()计算行的等级减1,除以评估的分区或查询结果集中的行数减1: (rank - 1) / (total_rows - 1) 在此公式中,rank是指定行的等级,total_rows是要计算的行数。
2346 以百分比计算排名
表: Students
+---------------+------+
| Column Name | Type |
+---------------+------+
| student_id | int |
| department_id | int |
| mark | int |
+---------------+------+
student_id 包含唯一值。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。
编写一个解决方案，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:
(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。 percentage 应该 四舍五入到小数点后两位。
student_rank_in_the_department 由 mark 的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入:
Students 表:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2 | 2 | 650 |
| 8 | 2 | 650 |
| 7 | 1 | 920 |
| 1 | 1 | 610 |
| 3 | 1 | 530 |
+------------+---------------+------+
输出:
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7 | 1 | 0.0 |
| 1 | 1 | 50.0 |
| 3 | 1 | 100.0 |
| 2 | 2 | 0.0 |
| 8 | 2 | 0.0 |
+------------+---------------+------------+
解释:
对于院系 1:
- 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0
- 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0
- 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0
对于院系 2: - 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
- 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
答案：
select student_id
,department_id
,round((percent_rank() over (partition by department_id order by mark desc))*100,2) as percentage
from Students
3.2 FIRST_VALUE()/LAST_VALUE()/NTH_VALUE()
FIRST_VALUE() 函数的作用是返回子集中第一行的指定列数据，该函数的语法如下：
FIRST_VALUE(expr)
OVER (
[partition_definition] [order_definition] [frame_clause]
)
其中，expr 为要获取数据的列明或者表达式，partition_definition 和 partition_definition 与 ROW_NUMBER() 函数一致；
frame_clause 的语法如下：
frame_unit {<frame_start>|<frame_between>}
LAST_VALUE() 和 FIRST_VALUE() 十分类似，区别在于 LAST_VALUE() 返回的是子集中的最后一条数据的指定列数据
NTH_VALUE() 的作用是获取指定 frame 中的第
N
个记录行的指定数据，对应的函数语法如下所示：
NTH_VALUE(expr, N)
OVER (
[partition_definition] [order_definition] [frame_clause]
)
2388 将表中的空值更改为前一个值
表: CoffeeShop
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| drink | varchar |
+-------------+---------+
id 是该表的主键（具有唯一值的列）。
该表中的每一行都显示了订单 id 和所点饮料的名称。一些饮料行为 null。
编写一个解决方案将 drink 的 null 值替换为前面最近一行不为 null 的 drink。保证表第一行的 drink 不为 null。
返回 与输入顺序相同的 结果表。
查询结果格式示例如下。
示例 1:
输入:
CoffeeShop 表:
+----+-------------------+
| id | drink |
+----+-------------------+
| 9 | Rum and Coke |
| 6 | null |
| 7 | null |
| 3 | St Germain Spritz |
| 1 | Orange Margarita |
| 2 | null |
+----+-------------------+
输出:
+----+-------------------+
| id | drink |
+----+-------------------+
| 9 | Rum and Coke |
| 6 | Rum and Coke |
| 7 | Rum and Coke |
| 3 | St Germain Spritz |
| 1 | Orange Margarita |
| 2 | Orange Margarita |
+----+-------------------+
解释:
对于 ID 6，之前不为空的值来自 ID 9。我们将 null 替换为 “Rum and Coke”。
对于 ID 7，之前不为空的值来自 ID 9。我们将 null 替换为 “Rum and Coke”。
对于 ID 2，之前不为空的值来自 ID 1。我们将 null 替换为 “Orange Margarita”。
请注意，输出中的行与输入中的行相同。
答案：
select id
,first_value(drink) over(partition by group_id order by row_id) as drink
from
(select *
,sum(IF(drink is null, 0, 1)) over(order by row_id) as group_id
from
(select *
,row_number() over() as row_id
from coffeeshop) t0
) t1
;
3.3 LAG()/LEAD()
3126 服务器利用时间
表：Servers
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| server_id | int |
| status_time | datetime |
| session_status | enum |
+----------------+----------+
(server_id, status_time, session_status) 是这张表的主键（有不同值的列的组合）。
session_status 是 (‘start’, ‘stop’) 的 ENUM (category)。
这张表的每一行包含 server_id, status_time 和 session_status。
编写一个解决方案来查找服务器 运行 的 总时间。输出应四舍五入为最接近的 整天数。
以 任意 顺序返回结果表。
结果格式如下所示。
示例：
输入：
Servers 表：
+-----------+---------------------+----------------+
| server_id | status_time | session_status |
+-----------+---------------------+----------------+
| 3 | 2023-11-04 16:29:47 | start |
| 3 | 2023-11-05 01:49:47 | stop |
| 3 | 2023-11-25 01:37:08 | start |
| 3 | 2023-11-25 03:50:08 | stop |
| 1 | 2023-11-13 03:05:31 | start |
| 1 | 2023-11-13 11:10:31 | stop |
| 4 | 2023-11-29 15:11:17 | start |
| 4 | 2023-11-29 15:42:17 | stop |
| 4 | 2023-11-20 00:31:44 | start |
| 4 | 2023-11-20 07:03:44 | stop |
| 1 | 2023-11-20 00:27:11 | start |
| 1 | 2023-11-20 01:41:11 | stop |
| 3 | 2023-11-04 23:16:48 | start |
| 3 | 2023-11-05 01:15:48 | stop |
| 4 | 2023-11-30 15:09:18 | start |
| 4 | 2023-11-30 20:48:18 | stop |
| 4 | 2023-11-25 21:09:06 | start |
| 4 | 2023-11-26 04:58:06 | stop |
| 5 | 2023-11-16 19:42:22 | start |
| 5 | 2023-11-16 21:08:22 | stop |
+-----------+---------------------+----------------+
输出：
+-------------------+
| total_uptime_days |
+-------------------+
| 1 |
+-------------------+
解释：
对于 server ID 3：
从 2023-11-04 16:29:47 到 2023-11-05 01:49:47: ~9.3 小时
从 2023-11-25 01:37:08 到 2023-11-25 03:50:08: ~2.2 小时
从 2023-11-04 23:16:48 到 2023-11-05 01:15:48: ~1.98 小时
server 3 共计：~13.48 小时
对于 server ID 1：
从 2023-11-13 03:05:31 到 2023-11-13 11:10:31: ~8 小时
从 2023-11-20 00:27:11 到 2023-11-20 01:41:11: ~1.23 小时
server 1 共计：~9.23 小时
对于 server ID 4:
从 2023-11-29 15:11:17 到 2023-11-29 15:42:17: ~0.52 小时
从 2023-11-20 00:31:44 到 2023-11-20 07:03:44: ~6.53 小时
从 2023-11-30 15:09:18 到 2023-11-30 20:48:18: ~5.65 小时
从 2023-11-25 21:09:06 到 2023-11-26 04:58:06: ~7.82 小时
server 4 共计：~20.52 小时
对于 server ID 5:
从 2023-11-16 19:42:22 到 2023-11-16 21:08:22: ~1.43 小时
server 5 共计：~1.43 小时
所有服务器的累积运行时间总计约为 44.46 小时，相当于一整天加上一些额外的小时。然而，由于我们只考虑整天，因此最终输出四舍五入为 1 整天。
答案：
select floor(sum(diff)/24) as total_uptime_days
from
(
select *
,timestampdiff(second, status_time, next_time)/3600 as diff
from
(
select *
,lead(status_time, 1, null) over(partition by server_id order by status_time) as next_time
from Servers
) t0
where next_time is not null and session_status = 'start'
) t1
2986 找到第三笔交易
表： Transactions
+------------------+----------+
| Column Name | Type |
+------------------+----------+
| user_id | int |
| spend | decimal |
| transaction_date | datetime |
+------------------+----------+
(user_id, transaction_date) 是这张表具有唯一值的列。
该表包含 user_id, spend,和 transaction_date。
编写一个查询，找到符合要求的用户的 第三笔交易 （如果他们有至少三笔交易），并且满足 前两笔交易 的花费 低于 第三笔交易的花费。
返回 按 升序 user_id 排序的结果表。
结果格式如下例所示。
示例 1:
输入：
Transactions table:
+---------+--------+---------------------+
| user_id | spend | transaction_date |
+---------+--------+---------------------+
| 1 | 65.56 | 2023-11-18 13:49:42 |
| 1 | 96.0 | 2023-11-30 02:47:26 |
| 1 | 7.44 | 2023-11-02 12:15:23 |
| 1 | 49.78 | 2023-11-12 00:13:46 |
| 2 | 40.89 | 2023-11-21 04:39:15 |
| 2 | 100.44 | 2023-11-20 07:39:34 |
| 3 | 37.33 | 2023-11-03 06:22:02 |
| 3 | 13.89 | 2023-11-11 16:00:14 |
| 3 | 7.0 | 2023-11-29 22:32:36 |
+---------+--------+---------------------+
输出
+---------+-------------------------+------------------------+
| user_id | third_transaction_spend | third_transaction_date |
+---------+-------------------------+------------------------+
| 1 | 65.56 | 2023-11-18 13:49:42 |
+---------+-------------------------+------------------------+
解释
- 对于 user_id 1，他们的第三笔交易发生在 2023-11-18 13:49:42，金额为 $65.56，超过了前两笔交易的支出，分别是 2023-11-02 12:15:23 的 $7.44 和 2023-11-12 00:13:46 的 $49.78。因此，此第三笔交易将包含在输出表中。
- user_id 2 只有总共 2 笔交易，因此没有第三笔交易。
- 对于 user_id 3，第三笔交易的金额 $7.0 少于前两笔交易，因此不会包含在内。
输出表按升序按 user_id 排序。
答案：
select user_id
,spend as third_transaction_spend
,transaction_date as third_transaction_date
from
(select *
,lag(spend, 1, null) over(partition by user_id order by transaction_date) as spend1
,lag(spend, 2, null) over(partition by user_id order by transaction_date) as spend2
,row_number() over(partition by user_id order by transaction_date) as rn
from transactions ) t0
where rn = 3
AND spend1 < spend AND spend2 < spend;
3.4 Range/Rows的使用场景
579 查询员工的累计薪水
表：Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| month | int |
| salary | int |
+-------------+------+
(id, month) 是该表的主键(具有唯一值的列的组合)。
表中的每一行表示 2020 年期间员工一个月的工资。
编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。
员工的 累计工资汇总 可以计算如下:
对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。
结果格式如下所示。
示例 1
输入：
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
| 1 | 7 | 90 |
| 1 | 8 | 90 |
+----+-------+--------+
输出：
+----+-------+--------+
| id | month | Salary |
+----+-------+--------+
| 1 | 7 | 90 |
| 1 | 4 | 130 |
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
+----+-------+--------+
解释：
员工 “1” 有 5 条工资记录，不包括最近一个月的 “8”:
- 第 ‘7’ 个月为 90。
- 第 ‘4’ 个月为 60。
- 第 ‘3’ 个月是 40。
- 第 ‘2’ 个月为 30。
- 第 ‘1’ 个月为 20。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 7 | 90 | (90 + 0 + 0)
| 1 | 4 | 130 | (60 + 40 + 30)
| 1 | 3 | 90 | (40 + 30 + 20)
| 1 | 2 | 50 | (30 + 20 + 0)
| 1 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+
请注意，‘7’ 月的 3 个月的总和是 90，因为他们没有在 ‘6’ 月或 ‘5’ 月工作。
员工 ‘2’ 只有一个工资记录(‘1’ 月)，不包括最近的 ‘2’ 月。
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 2 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+
员工 ‘3’ 有两个工资记录，不包括最近一个月的 ‘4’ 月:
- 第 ‘3’ 个月为 60 。
- 第 ‘2’ 个月是 40。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 3 | 3 | 100 | (60 + 40 + 0)
| 3 | 2 | 40 | (40 + 0 + 0)
+----+-------+--------+
答案：
select id
,month
,sum(salary) over(partition by id order by month range between 2 preceding and current row) as salary
-- 窗口为最近三个月,是对值进行偏移，所以这里是range，而不是用rows，rows是对行进行偏移
-- 另外这里月是整数，如果是日期月份比如（2022-06），则需要改成Interval 2 month
from employee
where (id, month) not in ( -- 过滤掉最近一个月
select id, max(month) as month
from employee
group by id
)
order by id, month desc;
2854 滚动平均步数
表： Steps
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| steps_count | int |
| steps_date | date |
+-------------+------+
(user_id, steps_date) 是此表的主键。
该表的每一行包含 user_id、steps_count 和 steps_date。
编写一个解决方案，计算出每个用户的 3-day 滚动平均步数 。
计算 n-day 滚动平均值 的计算方式如下：
对于每一天，如果有可用数据的情况下，我们会计算以该天为结束的 n 天连续步数的平均值，否则，对于该天来说，n 天滚动平均步数是未定义的。
输出 user_id 、 steps_date 和滚动平均值。并将滚动平均值四舍五入到 两位小数。
返回结果表以user_id 和 steps_date 升序 排序。
结果的格式如下示例。
示例 1:
输入：
Steps table:
+---------+-------------+------------+
| user_id | steps_count | steps_date |
+---------+-------------+------------+
| 1 | 687 | 2021-09-02 |
| 1 | 395 | 2021-09-04 |
| 1 | 499 | 2021-09-05 |
| 1 | 712 | 2021-09-06 |
| 1 | 576 | 2021-09-07 |
| 2 | 153 | 2021-09-06 |
| 2 | 171 | 2021-09-07 |
| 2 | 530 | 2021-09-08 |
| 3 | 945 | 2021-09-04 |
| 3 | 120 | 2021-09-07 |
| 3 | 557 | 2021-09-08 |
| 3 | 840 | 2021-09-09 |
| 3 | 627 | 2021-09-10 |
| 5 | 382 | 2021-09-05 |
| 6 | 480 | 2021-09-01 |
| 6 | 191 | 2021-09-02 |
| 6 | 303 | 2021-09-05 |
+---------+-------------+------------+
输出：
+---------+------------+-----------------+
| user_id | steps_date | rolling_average |
+---------+------------+-----------------+
| 1 | 2021-09-06 | 535.33 |
| 1 | 2021-09-07 | 595.67 |
| 2 | 2021-09-08 | 284.67 |
| 3 | 2021-09-09 | 505.67 |
| 3 | 2021-09-10 | 674.67 |
+---------+------------+-----------------+
解释：
- 对于 ID 为 1 的用户，截止到 2021-09-06 的三天连续的步数可用。因此，该日期的滚动平均值计算为 (395 + 499 + 712) / 3 = 535.33。
- 对于 ID 为 1 的用户，截止到 2021-09-07 的三天连续的步数可用。因此，该日期的滚动平均值计算为 (499 + 712 + 576) / 3 = 595.67。
- 对于 ID 为 2 的用户，截止到 2021-09-08 的三天连续的步数可用。因此，该日期的滚动平均值计算为 (153 + 171 + 530) / 3 = 284.67。
- 对于 ID 为 3 的用户，截止到 2021-09-09 的三天连续的步数可用。因此，该日期的滚动平均值计算为 (120 + 557 + 840) / 3 = 505.67。
- 对于 ID 为 3 的用户，截止到 2021-09-10 的三天连续的步数可用。因此，该日期的滚动平均值计算为 (557 + 840 + 627) / 3 = 674.67。
- 对于 ID 为 4 和 5 的用户，由于连续三天的数据不足，无法计算滚动平均值。结果表按 user_id 和 steps_date 升序排序。
select user_id
,steps_date
,round(rolling_average, 2) as rolling_average
from
(select *
,avg(steps_count) over(partition by user_id order by steps_date range between interval 2 day preceding and current row) as rolling_average -- 近三天的平均步数
,count(1) over(partition by user_id order by steps_date range between interval 2 day preceding and current row) as cnt
-- 近三天的天数，用于判断数据行是否真的有3天
from steps) t1
where cnt = 3
order by user_id
,steps_date
;