通俗易懂：窗口函数 | 全是案例
窗口函数，全是案例，良心文章，必是精品
什么是窗口函数
相信很多人都比较熟悉 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 的值为下一行的值，第二行为下一行的值，以此类推，第二个分组也一样。
总结
到这，窗口函数的内容就基本上都讲完了。这些可以说是数据分析和数据开发必备的技能，因此必须要熟练。
至于如何才能熟练，还需要多实践。
码字不易，如果觉得不错，麻烦动动小手点个赞，谢谢！
持续关注不迷路，转载请注明出处！—— 大数据的奇妙冒险
