通俗易懂:窗口函数 | 全是案例

通俗易懂:窗口函数 | 全是案例 窗口函数,全是案例,良心文章,必是精品

什么是窗口函数

相信很多人都比较熟悉 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 的值为下一行的值,第二行为下一行的值,以此类推,第二个分组也一样。

总结

到这,窗口函数的内容就基本上都讲完了。这些可以说是数据分析和数据开发必备的技能,因此必须要熟练。

至于如何才能熟练,还需要多实践。

码字不易,如果觉得不错,麻烦动动小手点个赞,谢谢!

持续关注不迷路,转载请注明出处!—— 大数据的奇妙冒险

 2 total views,  1 views today

页面下部广告