SQL-窗口函数合集

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> SELECTtime, subject, val,SUM(val) OVER (PARTITION BY subject ORDER BY timeROWS UNBOUNDED PRECEDING)AS running_total,AVG(val) OVER (PARTITION BY subject ORDER BY timeROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)AS running_averageFROM 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> SELECTtime, 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 observationsWINDOW w AS (PARTITION BY subject ORDER BY timeROWS 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_endframe_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_idfrom(select *,row_number() over() as row_idfrom coffeeshop) t0) t1
;