? 优质资源分享 ?
学习路线指引(点击解锁) | 知识定位 | 人群定位 |
---|---|---|
? Python实战微信订餐小程序 ? | 进阶级 | 本课程是python flask+微信小程序的完美结合,从项目搭建到腾讯云部署上线,打造一个全栈订餐系统。 |
?Python量化交易实战? | 入门级 | 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 |
什么是窗口函数
相信很多人都比较熟悉 SQL 聚合函数的语法,比如 count(), sum(), max()等,
窗口函数类似聚合函数,不同的是窗口函数不改变原有的行。
窗口函数是数据分析和数据开发必备的技能。
基本语法:<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
可能这样的解释还是不明了,没事,往后看,一会你就明白了。
案例
现在先模拟几条数据,假如目前有学生成绩表(stu_scores)如下:
class(班级) | id(学号) | score(成绩) |
---|---|---|
1 | 004 | 71 |
2 | 003 | 98 |
1 | 002 | 98 |
2 | 001 | 80 |
2 | 005 | 77 |
1 | 006 | 80 |
语句一:
select *,
rank①() over②(partition by③ class order by④ score desc) ranking
from stu_scores;
得到的结果如下:
class(班级) | id(学号) | score(成绩) | ranking |
---|---|---|---|
1 | 002 | 98 | 1 |
1 | 006 | 80 | 2 |
1 | 004 | 71 | 3 |
2 | 003 | 98 | 1 |
2 | 001 | 80 | 2 |
2 | 005 | 77 | 3 |
接下来解释一下这段 SQL,这条 SQL 的目的是求每个班级内的成绩排名
① rank() 排序的函数
② over() 指定分析函数工作的数据窗口大小
③ partition by 指定分组字段,这个案例中用 class 作为分组字段, 类似 group by
④ order by 排序,对分组后的结果进行排序
可能有些朋友会问:“这不就是 group by 和 order by 的用法么?不用窗口函数也能实现,为啥要用它?”
这是因为,单纯使用 group by 分组汇总后改变了表的行数,一行只有一个类别;
而使用窗口函数则不改变行数,可以将详细信息也展示出来。
到这,应该大致明白窗口函数的使用场景及如何使用了吧。
为了让大家更好地理解窗口函数,再写几条语句,看看结果是否和你想的一致。
语句二:
select *,
sum(score) over(order by id) as win_sum,
count(score) over(order by id) as win_count,
min(score) over(order by id) as win_min
from stu_scores;
结果:
class | id | score | win_sum | win_count | win_min |
---|---|---|---|---|---|
2 | 001 | 80 | 80 | 1 | 80 |
1 | 002 | 98 | 178 | 2 | 80 |
2 | 003 | 98 | 276 | 3 | 80 |
1 | 004 | 71 | 347 | 4 | 71 |
2 | 005 | 77 | 424 | 5 | 71 |
1 | 006 | 80 | 504 | 6 | 71 |
这样的结果是否和你想的一样呢?
由于不加 partition by 因此没有分组,所以从第一行开始开窗做计算。
以 win_sum 为例,第一行成绩相加 80,与第二行相加得 178,再与第三行相加得 276,以此类推。
这样做有什么意义呢?
可以每一行的数据里直观的看到,截止到本行数据,统计数据是多少。
同时可以看出每一行数据,对整体统计数据的影响。
从成绩上可能不太好理解,如果是从生产经营角度,比如对比每月营业额,可以更直观地看出差距。
其它窗口函数及关键字
rank 与 dense_rank
rank(), dense_rank() 都属于排序函数,区别在于有重复数据的时候如何排,看案例就知道
select *,
rank() over(order by score desc) as ranking,
dense\_rank() over(order by score desc) as dense_ranking
from stu_scores;
结果:
class | id | score | ranking | dense_ranking |
---|---|---|---|---|
1 | 002 | 98 | 1 | 1 |
2 | 003 | 98 | 1 | 1 |
2 | 001 | 80 | 3 | 2 |
1 | 006 | 80 | 4 | 3 |
1 | 004 | 71 | 5 | 4 |
2 | 005 | 77 | 6 | 5 |
可以看到,遇到重复排名的时候,rank 是跳跃排序,如果有两个第一,那接下来是第三;
dense_rank()则是连续排序,如果有两个第一时,那接下来是第二。
lead 与 lag
lead(col, n, default_val):用于统计窗口内往下第 n 行值。
第一个参数为列名,第二个参数为往下第 n 行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL。
lag(col,n, default_val):用于统计窗口内往上第n行值,参数和 lead 一样
还是看案例吧,用文字讲确实很难讲清楚
问题:根据班级分组,统计每个班学生的成绩以及小于(大于)等于该学生成绩的上(下)一个学生的成绩:
select *,
lead(score,1) over(partition by class order by score) as lead,
lag(score,1) over(partition by class order by score) as lag
from stu_scores;
结果:
class | id | score | lead | lag |
---|---|---|---|---|
1 | 004 | 71 | 80 | null |
1 | 006 | 80 | 98 | 71 |
1 | 002 | 98 | null | 80 |
2 | 005 | 77 | 80 | null |
2 | 001 | 80 | 98 | 77 |
2 | 003 | 98 | null | 80 |
可以看到,第二行 lead 的结果是第三行的成绩,lag 的结果是第一行的成绩,没有的则为 null
last_value 与 first_value
这两个比较简单,顾名思义,分别表示取窗口内的最后一个值和第一条数据,但是先看看例子
select *,
first\_value(score) over(partition by class order by score) as first,
last\_value(score) over(partition by class order by score) as last
from stu_scores;
结果:
class | id | score | first | last |
---|---|---|---|---|
1 | 004 | 71 | 71 | 71 |
1 | 006 | 80 | 71 | 80 |
1 | 002 | 98 | 71 | 98 |
2 | 005 | 77 | 77 | 77 |
2 | 001 | 80 | 77 | 80 |
2 | 003 | 98 | 77 | 98 |
从结果看,first_value 的结果很合理,是每个分区的第一个数据;
但 last_value 的结果好像不符合期望,这个和我接下来要说的几个关键字有关。
UNBOUNDED、PRECEDING、FOLLOWING、CURRENT ROW
先粗略地解释一下这些关键字:
CURRENT ROW:当前行
n PRECEDING:往前 n 行数据
n FOLLOWING:往后 n 行数据
UNBOUNDED:起点
- UNBOUNDED PRECEDING 表示从前面的起点,
- UNBOUNDED FOLLOWING 表示到后面的终点
老规矩,先看例子
SELECT *,
last_value(score) ov.r(PARTITION BY class ORDER BY score) last1,
last_value(score) over(PARTITION BY class ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT row) last2,
last_value(score) over(PARTITION BY class ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) last3,
last_value(score) over(PARTITION BY class ORDER BY score RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) last4,
last_value(score) over(PARTITION BY class ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) last5
FROM stu_scores;
结果:
class | id | score | last1 | last2 | last3 | last4 | last5 |
---|---|---|---|---|---|---|---|
1 | 004 | 71 | 71 | 71 | 98 | 71 | 80 |
1 | 006 | 80 | 80 | 80 | 98 | 80 | 98 |
1 | 002 | 98 | 98 | 98 | 98 | 98 | 98 |
2 | 005 | 77 | 77 | 77 | 98 | 80 | 80 |
2 | 001 | 80 | 80 | 80 | 98 | 80 | 98 |
2 | 003 | 98 | 98 | 98 | 98 | 98 | 98 |
用的都是 last_value 结果还不一样,为什么呢?
这是因为,last_value 默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,
表示当前行永远是最后一个值,因此 last1 和 last2 的结果是一样的。
如果要获取每个分组的最后一个值,
则需改成 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,
表示从最前一行作为起点,最后一行为终点,就是 last3 的结果(由于两个分组排序后最后一个数都是 98,看不出区别,大家可以去验证一下)
至于 last4 和 last5 的区别,则是 RANGE 和 ROWS 的区别:
RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING:
表示当前行的值分别减 3 和 加 3,以第 4 行为例,原来的 score 是 77,各加减 3,则是 74 到 80 的范围
80 刚好是下一行的值,因此它的结果为 80,其它行由于加减 3 后没有对应的值,因此为自身。
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:
表示当前行分别往前往后减一行,记住,rows 和 range 的区别就在于是当前行数还是当前行的值。
因此在同一个分组内,第一行 last5 的值为下一行的值,第二行为下一行的值,以此类推,第二个分组也一样。
总结
到这,窗口函数的内容就基本上都讲完了。这些可以说是数据分析和数据开发必备的技能,因此必须要熟练。
至于如何才能熟练,还需要多实践。
码字不易,如果觉得不错,麻烦动动小手点个赞,谢谢!
持续关注不迷路,转载请注明出处!—— 大数据的奇妙冒险
转载请注明:xuhss » 通俗易懂:窗口函数 | 全是案例