SQL数据分析之窗口排序函数rank、dense_rank、raw_number与lag、lead窗口偏移函数【用法整理】

注意：这些函数是必须在MySQL3.8版本以上才能使用，否则都是会出错的。

零、写在前面

本文所有代码均是在SQL ZOO平台进行，数据也该平台下的world表和一些其他平台提供的数据表，所有代码均已通过测试。

一、窗口排序函数

1、基础

• 标准语法：over (partition by 字段名 order by 字段名 asc/desc)
• over()两个子句为可选项，partition by指定分区依据，order by指定排序依据
• 比较：
  rank函数：对于4，4，4，8，也就是如果有并列名次的行，排序结果是：1，1，1，4
  dense_rank函数：对于4，4，4，8，也就是如果有并列名次的行，排序结果是：1，1，1，2
  row_number函数：对于4，4，4，8，也就是如果有并列名次的行，排序结果是：1，2，3，4

2、rank() over()——跳跃式排序

（1）说明
比如数值为99， 99， 90， 89， 那么通过这个
函数得到的排名为1, 1, 3, 4
因为前面2个同为第一位，且都占了一个位置。

（2）练习：查询每个大洲里面国土面积大于100万、首都城市的名字不是以A,M,C开头的分别在每个大洲人口数量降序的大洲和国家名。（相等人口的国家排名相同，但各占一个位置）

select continent,name,
rank() over(PARTITION BY continent ORDER BY population desc) RO
from world
where area>=1000000 and left(capital,1) not in ('A','M','C')
#在SQL ZOO练习平台上有时候需要这句：group by continent,name
#若能正常编译则不管
1
2
3
4
5
6

（3）其他条件不变，若我们只想要看每个大洲人口排名前二的国家，这时候RO这个命名就起作用了（需要用到子查询）：

select RF.continent,RF.name
from (select continent,name,
rank() over(PARTITION BY continent ORDER BY population desc) as RO
from world
where area>=1000000 and left(capital,1) not in ('A','M','C')) as RF
where RF.RO=2
1
2
3
4
5
6

3、dense_rank() over()

（1）说明：dense. _rank();并列连续型排序–比如数值为99, 99，90, 89，
那么通过这个函数得到的排名为1, 1, 2, 3

（2）练习：查询选号为’S14000024’且选举年份为2017年的按照选举票数排序的党派和票数。

select party,votes,
dense_rank() over(ORDER BY votes desc) as RO
from ge
where constituency='S14000024' and yr=2017
group by party,votes
order by party
1
2
3
4
5
6

4、raw_number() over()

（1）说明：row _number():连续型排序–比如数值为99， 99， 90， 89， 那么通
过这个函数得到的排名为1, 2, 3, 4
根据对排序值的需求选择相应排序窗口函数，由于值的不同特性(比如数值不重复) , 这三个函数可以通用

（2）练习：查询日期为’2020-4-25’的所有姓名和确诊日期，并列出它们的确诊日期和死亡日期的降序排名

select name,confirmed,
row_number() over(ORDER BY confirmed desc) RC
,deaths
,row_number() over(ORDER BY deaths desc) RD
from covid
where whn='2020-4-25'
order by confirmed desc;
1
2
3
4
5
6
7

5、注意

1、窗口函数只能在select子句中使用
2、窗口函数中的partition by子句可以指定数据的分区,和group by要去重分组不同的是，partition by只分区不去重
3、窗口函数中没有partition by子句时，即不对数据分区，直接整个表为一个区
4、排序窗口函数中order by子句是必选项，窗口函数中order by子句在分区内，依据指定字段和排序方法对数据行排序
5、窗口函数必须有个名字，比如常用的RO，RI等

二、窗口偏移函数

1、lag()

lag(字段名,偏移量,默认值) over()

select
name,
date_format(whn,'%Y-%m-%d') date,
confirmed 当天截至时间累计确诊人数,
lag(confirmed,1) over(partition by name order by whn) 昨天截至时间累计确诊人数,
(confirmed-lag(confirmed,1) over(partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn)=1
group by name,whn,confirmed
order by whn;
1
2
3
4
5
6
7
8
9
10

2、lead()

lead(字段名,偏移量,默认值) over()

允许我们从窗口分区中，根据给定的相对于当前行的前偏移量（LAG）或后偏移量（LEAD），并返回对应行的值，默认的偏移量为1。当指定的偏移量没有对用的行是，LAG 和LEAD 默认返回 NULL，当然可用其他值替换 LAG（val，1，0.00） 第3个参数就是替换值。

3、综合练习

select name,date_format(whn,'%Y-%m-%d') date,
confirmed - lag(confirmed,1) over(PARTITION BY name ORDER BY whn) Week_New
from covid
where name='Italy' and weekday(whn)=0
group by name,whn,confirmed
order by whn
1
2
3
4
5
6

confirmed 本周一的确诊人数，weekday(whn)=0筛选出周一（把计算范围都控制在周一，每个周一相减就是一周的新增）

文章总结归纳于自戴师兄的课程：https://www.bilibili.com/video/BV1ZM4y1u7uF?p=4，在此课程学习的基础上进行了一些修改和验证。