7、职能岗SQL学习极简攻略-窗口函数

• 窗口函数语法
• 序号窗口函数
  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • 小结
• 前后窗口函数
  • LEAD
  • LAG
• 首尾窗口函数
  • FIRST_VALUE
  • LAST_VALUE
• 分布窗口函数
  • PERCENT_RANK
  • CUME_DIST
• 其它窗口函数
  • NTILE
  • NTH_VALUE
  • 聚合窗口函数

在前文《水平学习SQL》小节中，我们介绍了SQL中的聚合函数，如SUM、AVG、COUNT等。这些函数将多行数据组合成单个输出行，以便进行统计计算，例如求和、平均值、计数等。本节将介绍另一种SQL函数类型——窗口函数，窗口函数允许我们在由查询结果集中的行组成的“窗口”上执行计算。与聚合函数不同，窗口函数不会将行组合成单个输出行，而是会为每个输入行返回一个值，使得我们可以在保持行的独立性的同时进行复杂的计算。

窗口函数语法

窗口函数的语法如下：

<窗口函数>(<参数>) OVER (  
    [PARTITION BY <分区表达式>]  
    [ORDER BY <排序表达式> [ASC | DESC]]  
    [ROWS/Range <窗口范围>]  
)

有点懵？别着急，语法不用硬记，下面我们将通过具体的示例来介绍窗口函数的用法。

序号窗口函数

ROW_NUMBER

例如对于以下成绩表（表名为scores）：

student_id	name	class	subject	score
1	屈屈	1班	创意写作	100
2	慧心	2班	创意写作	81
3	达琳	1班	创意写作	93
4	森贝	2班	创意写作	81
5	米粒	1班	创意写作	90
6	毛毛	1班	创意写作	75
7	困困	2班	创意写作	96
8	悟空	2班	创意写作	59

若我们希望按照班级对学生的成绩进行排序，并展示每个班级的学生排名，此时则可以使用如下窗口函数来实现：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS row_number_c
from scores;

其中row_number()是序号窗口函数，over()中的partition by class表示按照班级进行分组，order by score desc表示按照成绩降序排列。执行上述SQL语句后，我们可以得到如下结果：

student_id	name	class	subject	score	row_number_c
1	屈屈	1班	创意写作	100	1
3	达琳	1班	创意写作	93	2
5	米粒	1班	创意写作	90	3
6	毛毛	1班	创意写作	75	4
7	困困	2班	创意写作	96	1
2	慧心	2班	创意写作	81	2
4	森贝	2班	创意写作	81	3
8	悟空	2班	创意写作	59	4

可以看到，我们成功地为每个班级的学生排名，row_num列表示了每个学生在各自班级中的排名。

RANK

在上述例子中，我们可以看到最终的排序结果中，如果有多个学生的成绩相同，那么他们的排名也会相同。例如慧心和森贝同学都考了81分，但是森贝的排名缺比慧心低一名，这时森贝同学可不乐意了，他们本应该是并列第二名的。

为了解决该问题，我们可以使用rank()函数，rank()函数的用法与row_number()函数类似，只是在有相同值时，rank()函数会并列排名，而row_number()函数会连续排名。例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rank_c
from scores;

执行上述SQL语句后，我们可以得到如下结果，可以看到慧心和森贝同学的排名并列第2：

student_id	name	class	subject	score	rank_c
1	屈屈	1班	创意写作	100	1
3	达琳	1班	创意写作	93	2
5	米粒	1班	创意写作	90	3
6	毛毛	1班	创意写作	75	4
7	困困	2班	创意写作	96	1
2	慧心	2班	创意写作	81	2
4	森贝	2班	创意写作	81	2
8	悟空	2班	创意写作	59	4

DENSE_RANK

与rank()函数类似，dense_rank()函数也会并列排名，但是不会跳过排名。例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS dense_rank_c
from scores;

执行上述SQL语句后，我们可以得到如下结果，可以看到慧心和森贝同学的排名并列第2，但是没有跳过第3名，下一位学生的排名是第3名。

student_id	name	class	subject	score	dense_rank_c
1	屈屈	1班	创意写作	100	1
3	达琳	1班	创意写作	93	2
5	米粒	1班	创意写作	90	3
6	毛毛	1班	创意写作	75	4
7	困困	2班	创意写作	96	1
2	慧心	2班	创意写作	81	2
4	森贝	2班	创意写作	81	2
8	悟空	2班	创意写作	59	3

小结

我们可以通过一个SQL来对比row_number()、rank()和dense_rank()函数的区别：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS row_number_c,
    RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rank_c,
    DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS dense_rank_c
from scores;

执行上述SQL语句后，我们可以得到如下结果：

student_id	name	class	subject	score	row_number_c	rank_c	dense_rank_c
1	屈屈	1班	创意写作	100	1	1	1
3	达琳	1班	创意写作	93	2	2	2
5	米粒	1班	创意写作	90	3	3	3
6	毛毛	1班	创意写作	75	4	4	4
7	困困	2班	创意写作	96	1	1	1
2	慧心	2班	创意写作	81	2	2	2
4	森贝	2班	创意写作	81	3	2	2
8	悟空	2班	创意写作	59	4	4	3

从查询结果中，我们就可以很直观的看到这三个函数的区别了：

1. row_number()函数会连续排名，不会并列排名；
2. rank()函数会并列排名，但是会跳过排名；
3. dense_rank()函数会并列排名，不会跳过排名。

前后窗口函数

LEAD

LEAD()函数用于获取某一行的后N行数据，例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    LEAD(score, 1) OVER(PARTITION BY class ORDER BY score DESC) AS next_score
from scores;

其中LEAD(score, 1)表示获取当前行的后1行数据，即获取下一行的成绩，这里的1也可以替换为其它数字N，即获取下N行的数据。

执行上述SQL语句后，我们可以得到如下结果：

student_id	name	class	subject	score	next_score
1	屈屈	1班	创意写作	100	93
3	达琳	1班	创意写作	93	90
5	米粒	1班	创意写作	90	75
6	毛毛	1班	创意写作	75	NULL
7	困困	2班	创意写作	96	81
2	慧心	2班	创意写作	81	81
4	森贝	2班	创意写作	81	59
8	悟空	2班	创意写作	59	NULL

可以看到，我们成功地获取了每个学生的下一位同学的成绩，基于此我们可以进行更多的计算，例如计算每个学生的成绩与下一位同学的成绩的差值。

从查询结果中我们还可以看到，最后一位学生的next_score为NULL，这是因为没有下一位同学了，这种情况下，我们也可以将函数改为LEAD(score, 1, 0)来指定默认值，其中0表示默认值，即当没有下一位同学时，next_score的值为0。例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    LEAD(score, 1, 0) OVER(PARTITION BY class ORDER BY score DESC) AS next_score
from scores;

此时的查询结果如下，可以看到最后一位学生的next_score为0：

student_id	name	class	subject	score	next_score
1	屈屈	1班	创意写作	100	93
3	达琳	1班	创意写作	93	90
5	米粒	1班	创意写作	90	75
6	毛毛	1班	创意写作	75	0
7	困困	2班	创意写作	96	81
2	慧心	2班	创意写作	81	81
4	森贝	2班	创意写作	81	59
8	悟空	2班	创意写作	59	0

LAG

和LEAD函数类似，LAG()函数用于获取某一行的前N行数据，例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    LAG(score, 1) OVER(PARTITION BY class ORDER BY score DESC) AS last_score
from scores;

将返回以下结果，可以看到我们成功地获取了每个学生的上一位同学的成绩：

student_id	name	class	subject	score	last_score
1	屈屈	1班	创意写作	100	NULL
3	达琳	1班	创意写作	93	100
5	米粒	1班	创意写作	90	93
6	毛毛	1班	创意写作	75	90
7	困困	2班	创意写作	96	NULL
2	慧心	2班	创意写作	81	96
4	森贝	2班	创意写作	81	81
8	悟空	2班	创意写作	59	81

和LEAD函数类似，LAG函数也可以指定默认值，例如：LAG(score, 1, 0)。

首尾窗口函数

FIRST_VALUE

FIRST_VALUE()函数用于获取窗口内第一行的值，例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) AS first_score
from scores;

执行上述SQL语句后，我们可以得到如下结果，可以看到获取了每个班级的第一名同学的成绩：

student_id	name	class	subject	score	first_score
1	屈屈	1班	创意写作	100	100
3	达琳	1班	创意写作	93	100
5	米粒	1班	创意写作	90	100
6	毛毛	1班	创意写作	75	100
7	困困	2班	创意写作	96	96
2	慧心	2班	创意写作	81	96
4	森贝	2班	创意写作	81	96
8	悟空	2班	创意写作	59	96

基于此，我们可以进一步计算每个学生的成绩与第一名同学的成绩的差值。

LAST_VALUE

和FIRST_VALUE函数类似，LAST_VALUE()函数用于获取窗口内最后一行的值，需要注意的是，LAST_VALUE()函数默认是当前窗口的最后一行，如果需要获取整个窗口的最后一行，需要指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。我们通过以下示例来演示如何获取每个班级的最后一名同学的成绩：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) AS last_score,
    LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score_2
from scores;

将返回如下结果：

student_id	name	class	subject	score	last_score	last_score_2
1	屈屈	1班	创意写作	100	100	75
3	达琳	1班	创意写作	93	93	75
5	米粒	1班	创意写作	90	90	75
6	毛毛	1班	创意写作	75	75	75
7	困困	2班	创意写作	96	96	59
2	慧心	2班	创意写作	81	81	59
4	森贝	2班	创意写作	81	81	59
8	悟空	2班	创意写作	59	59	59

可以看到，last_score是获取当前窗口的最后一行的值，last_score_2是获取整个窗口的最后一行的值。

分布窗口函数

PERCENT_RANK

PERCENT_RANK()函数用于计算某一行在分区中的百分比排名，例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    PERCENT_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS percent_rank_c
from scores;

查询结果如下：

student_id	name	class	subject	score	percent_rank_c
1	屈屈	1班	创意写作	100	0
3	达琳	1班	创意写作	93	0.3333333333333333
5	米粒	1班	创意写作	90	0.6666666666666666
6	毛毛	1班	创意写作	75	1
7	困困	2班	创意写作	96	0
2	慧心	2班	创意写作	81	0.3333333333333333
4	森贝	2班	创意写作	81	0.3333333333333333
8	悟空	2班	创意写作	59	1

其百分比排名的计算方式为：(当前行的排名 - 1) / (分区中的总行数 - 1)。例如第一名的百分比排名为(1 - 1) / (4 - 1) = 0，第二名的百分比排名为(2 - 1) / (4 - 1) = 0.3333，以此类推。

CUME_DIST

CUME_DIST()函数用于计算某一行在分区中的累积分布，例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    CUME_DIST() OVER(PARTITION BY class ORDER BY score DESC) AS cume_dist_c
from scores;

查询结果如下：

student_id	name	class	subject	score	cume_dist_c
1	屈屈	1班	创意写作	100	0.25
3	达琳	1班	创意写作	93	0.5
5	米粒	1班	创意写作	90	0.75
6	毛毛	1班	创意写作	75	1
7	困困	2班	创意写作	96	0.25
2	慧心	2班	创意写作	81	0.75
4	森贝	2班	创意写作	81	0.75
8	悟空	2班	创意写作	59	1

其累积分布的计算方式为：小于等于当前rank值的行数 / 分区中的总行数。例如第一名的累积分布为1 / 4 = 0.25，第二名的累积分布为2 / 4 = 0.5，以此类推。注意这里是“小于等于”，并列第2名的话，小于等于第2名的行数有3行，所以其累积分布为3 / 4 = 0.75。

其它窗口函数

NTILE

NTILE()函数用于将窗口内的行分成n个组，记录组号，每个组的行数尽量平均，亦称为“分桶”。也可以理解为，将窗口内的有序数据分为n个等级，记录等级数。例如：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    NTILE(2) OVER(PARTITION BY class ORDER BY score DESC) AS ntile_c
from scores;

查询结果如下，可以看到我们成功地将每个班级的学生分为了两组，ntile_c的值为1表示成绩更好的组，值为2表示成绩较差的组：

student_id	name	class	subject	score	ntile_c
1	屈屈	1班	创意写作	100	1
3	达琳	1班	创意写作	93	1
5	米粒	1班	创意写作	90	2
6	毛毛	1班	创意写作	75	2
7	困困	2班	创意写作	96	1
2	慧心	2班	创意写作	81	1
4	森贝	2班	创意写作	81	2
8	悟空	2班	创意写作	59	2

NTH_VALUE

NTH_VALUE()函数用于获取当前窗口内的第n个值。例如，我们希望获取截止到当前行，每个班级的第二名同学的成绩：

select 
    student_id, 
    name, 
    class, 
    subject, 
    score, 
    NTH_VALUE(score, 2) OVER(PARTITION BY class ORDER BY score DESC) AS nth_value_c
from scores;

其中NTH_VALUE(score, 2)表示获取当前窗口的第2个值，这里的2也可以替换为其它数字N。

查询结果如下：

student_id	name	class	subject	score	nth_value_c
1	屈屈	1班	创意写作	100	NULL
3	达琳	1班	创意写作	93	93
5	米粒	1班	创意写作	90	93
6	毛毛	1班	创意写作	75	93
7	困困	2班	创意写作	96	NULL
2	慧心	2班	创意写作	81	81
4	森贝	2班	创意写作	81	81
8	悟空	2班	创意写作	59	81

其中NULL表示当前行所在窗口没有第2个值，所以其nth_value_c为NULL。

聚合窗口函数

在前面小节介绍的常与group by一起使用的聚合函数，例如sum()、avg()、count()等，也可以与窗口函数一起使用，此时称之为聚合窗口函数。聚合窗口函数不会将多行数据聚合为一行，而是将聚合结果添加到每一行数据中。

例如以下是一个连锁超市的订单表，其中branch为分店名称，amount为订单金额，order_date为订单日期。

order_id	user_name	order_date	amount	product	branch ｜
1	蛐蛐	2023-01-01	22.00	贝果	1店
2	毛毛	2023-01-01	65.00	洗发水	2店
3	蛐蛐	2023-01-02	15.00	提子	1店
4	困困	2023-01-02	23.00	牙膏	1店
5	毛毛	2023-01-03	60.00	大米	2店
6	悟空	2023-01-04	88.00	桃子	2店
7	蛐蛐	2023-01-05	22.00	贝果	1店
8	毛毛	2023-01-05	65.00	洗发水	2店
9	蛐蛐	2023-01-05	15.00	提子	1店
10	困困	2023-01-06	23.00	牙膏	1店
11	毛毛	2023-01-06	60.00	大米	2店
12	悟空	2023-01-06	88.00	桃子	2店

若我们希望计算每个分店截止到当前日期的订单总金额、平均金额、最大金额和最小金额，则可以使用以下SQL：

 SELECT
    order_id,
    user_name,
    order_date,
    amount,
    product,
    branch,
    SUM(amount) OVER(PARTITION BY branch ORDER BY order_date) AS total_amount,
    AVG(amount) OVER(PARTITION BY branch ORDER BY order_date) AS avg_amount,
    MAX(amount) OVER(PARTITION BY branch ORDER BY order_date) AS max_amount,
    MIN(amount) OVER(PARTITION BY branch ORDER BY order_date) AS min_amount
FROM
    orders;

执行结果如下，可以看到我们以分店为维度统计了：截止到当前日期的订单总金额（total_amount），平均金额（avg_amount），最大金额（max_amount）和最小金额（min_amount）。

order_id	user_name	order_date	amount	product	branch	total_amount	avg_amount	max_amount	min_amount
1	蛐蛐	2023-01-01	22	贝果	1店	22	22	22	22
3	蛐蛐	2023-01-02	15	提子	1店	60	20	23	15
4	困困	2023-01-02	23	牙膏	1店	60	20	23	15
7	蛐蛐	2023-01-05	22	贝果	1店	97	19.4	23	15
9	蛐蛐	2023-01-05	15	提子	1店	97	19.4	23	15
10	困困	2023-01-06	23	牙膏	1店	120	20	23	15
2	毛毛	2023-01-01	65	洗发水	2店	65	65	65	65
5	毛毛	2023-01-03	60	大米	2店	125	62.5	65	60
6	悟空	2023-01-04	88	桃子	2店	213	71	88	60
8	毛毛	2023-01-05	65	洗发水	2店	278	69.5	88	60
11	毛毛	2023-01-06	60	大米	2店	426	71	88	60
12	悟空	2023-01-06	88	桃子	2店	426	71	88	60

如果我们希望窗口的范围是到当前行（而不是当前日期），则可以使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW来指定窗口范围，例如：

SELECT
    order_id,
    user_name,
    order_date,
    amount,
    product,
    branch,
    SUM(amount) OVER(PARTITION BY branch ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_amount,
    AVG(amount) OVER(PARTITION BY branch ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg_amount,
    MAX(amount) OVER(PARTITION BY branch ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_amount,
    MIN(amount) OVER(PARTITION BY branch ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_amount
FROM
    orders;

此时的结果为：

order_id	user_name	order_date	amount	product	branch	total_amount	avg_amount	max_amount	min_amount
1	蛐蛐	2023-01-01	22	贝果	1店	22	22	22	22
3	蛐蛐	2023-01-02	15	提子	1店	37	18.5	22	15
4	困困	2023-01-02	23	牙膏	1店	60	20	23	15
7	蛐蛐	2023-01-05	22	贝果	1店	82	20.5	23	15
9	蛐蛐	2023-01-05	15	提子	1店	97	19.4	23	15
10	困困	2023-01-06	23	牙膏	1店	120	20	23	15
2	毛毛	2023-01-01	65	洗发水	2店	65	65	65	65
5	毛毛	2023-01-03	60	大米	2店	125	62.5	65	60
6	悟空	2023-01-04	88	桃子	2店	213	71	88	60
8	毛毛	2023-01-05	65	洗发水	2店	278	69.5	88	60
11	毛毛	2023-01-06	60	大米	2店	338	67.6	88	60
12	悟空	2023-01-06	88	桃子	2店	426	71	88	60