摸鱼等退休

2022-07-05 19:08 已编辑门头沟学院项目经理

关注

面试官：你用过什么窗口函数？

作为一个数据工作者，肯定不少被问窗口函数的问题，比如用过什么窗口函数，或者出个题，一般都是需要用到窗口函数才能完成。所以这一篇，专门讲窗口函数，不是做数据开发的也可以看看，当作学习也好，指不定哪天要实现这种逻辑。

什么是窗口函数

相信很多人都比较熟悉 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) over(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;

结果：