常见的窗口函数汇总

老规矩，还没看前面的文章而且对这些知识点不熟悉的，可以点击连接进行观看哦：

hive从入门到放弃(一)——初识hive
hive从入门到放弃(二)——DDL数据定义
hive从入门到放弃(三)——DML数据操作
hive从入门到放弃(四)——分区与分桶

话不多说，直接开始。

什么是窗口函数

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

总结

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

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

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

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