hive窗口分析函数使用详解系列二之分组排序窗口函数

1.综述

我们讨论面试中各大厂的SQL算法面试题，往往核心考点就在于窗口函数，所以掌握好了窗口函数，面对SQL算法面试往往事半功倍。

已更新第一类聚合函数类，点击这里阅读 hive窗口函数聚合函数类

本节介绍Hive聚合函数中的第二类聚合函数：分组排序窗口函数。

这些函数的用法不仅仅适用于Hive，对于很多数数据库来说同样也适用，比如SparkSQL，FlinkSQL以及Mysql8，Oracle，MSSQL等传统的关系型数据库。

如果论使用的广泛性，hive窗口函数中我们使用最广泛的就是排序类窗口函数，我们通常一提起窗口函数，想到的就是这类排序类窗口函数，它在我们进行数据去重中扮演了至关重要的角色。

1.1 Hive窗口函数分类

Hive提供的窗口函数可以分为一下几类

• 聚合函数类

count() over();
sum() over();
max() over();
min() over();
avg() over();

• 分组排序类

row_number() over();
rank() over();
dense_rank() over();
percent_rank() over();
cume_dist() over();
ntile() over();

• 求偏移量类

lead() over();
lag() over();
first_value() over();

1.2 分析函数语法

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

具体解析

over()括号内为空时，是直接进行计算。

其中partition by 列名 是按指定列进行分组，进而进行计算。

最后的order by 列名 是按照指定列进行排序，进而进行计算。

1.3 基础数据准备

create table if not exists temp.user_info (
  `id` bigint comment '用户id',
  `client` string comment '客户端',
  `gender` int comment '性别，0女1男',
  `constellation` string comment '星座',
  `age` int comment '年龄',
  `pv` bigint comment '访问量',
  `chat_num` bigint comment '聊天次数'
) comment '用户信息测试临时表' 

数据预览

id	client	gender	constellation	age	pv	chat_num
1	ios	0	处女座	29	174	3
2	ios	1	双鱼座	26	263	2
3	android	1	双鱼座	35	232	39
4	ios	1	水瓶座	32	57	3
5	ios	1	射手座	33	67	6
6	ios	1	双子座	36	81	5
7	ios	1	狮子座	29	68	4
8	ios	1	狮子座	28	19	3
9	ios	0	射手座	32	479	2
10	ios	1	白羊座	26	255	36

2.各分组排序类函数的使用

2.1 row_number

• 功能

按照排序的顺序输出窗口中的数据的行号信息，不会出现排名相同的情况，且排名是连续的。即使是值相同，排名也会按照其排序顺序递增。

• 示例

按照星座分组，统计出pv由高到低的排名。

select id,client,gender,row_number() over(partition by constellation order by pv desc) as rank_id
from temp.user_info where id <= 10

数据结果：

id	client	gender	constellation	pv	rank_id
6	ios	1	双子座	81	1
2	ios	1	双鱼座	263	1
3	android	1	双鱼座	232	2
1	ios	0	处女座	174	1
9	ios	0	射手座	479	1
5	ios	1	射手座	67	2
4	ios	1	水瓶座	57	1
7	ios	1	狮子座	68	1
8	ios	1	狮子座	19	2
10	ios	1	白羊座	255	1

可以很清晰的看到按星座分组，每个星座内部的PV排名的id。例如，射手座用户id9排名第一，用户id5排名第二。

• 拓展使用：

1. 更深一步的需求是筛出每个星座最高pv的用户，（或者说按星座去重，只取最高访问pv的用户）

2. 再更进一步还可以计算诸如最大在线天数等SQL问题。

3. 业务中常用用法为通过指定主键进行数据去重。

2.2 rank

• 功能

按照指定列进行排名，如果值相同，则排名并列，下一个排名会出现跳跃，即排名是不连续的。例如有前2个值一样，那么前2行并列第一，第3行的排名则为3。

• 示例

按使用客户端分组，统计年龄由高到低的排名。

select id,client,age,rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10

数据结果：

id	client	age	rank_id
3	android	35	1
6	ios	36	1
5	ios	33	2
9	ios	32	3
4	ios	32	3
1	ios	29	5
7	ios	29	5
8	ios	28	7
2	ios	26	8
10	ios	26	8

可以看到上述信息中，通过安卓和iOS客户端的年龄大小排名。其中32岁两个并列第三，没有第四名，直接开始第五名。相同值进行并列。rank_id的排名是不连续的。

• 拓展使用：

常用于指定排名场景。

2.3 dense_rank

• 功能

该函数可以和rank()对照使用，按照指定列排序的顺序输出窗口中的数据的排名，**如果值相同时，排名并列，下一个排名是连续递增的，不会出现跳跃情况。**即如果前2行的值相同，则前2行并列第1，第3行的排名则为第2。

可以理解为一个为疏松排名（rank）,一个为密集排名（dense_rank）

• 示例

按使用客户端分组，统计年龄由高到低的排名。

select id,client,age,dense_rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10

数据结果：对比上文

id	client	age	rank_id
3	android	35	1
6	ios	36	1
5	ios	33	2
9	ios	32	3
4	ios	32	3
1	ios	29	4
7	ios	29	4
8	ios	28	5
2	ios	26	6
10	ios	26	6

可以看到，排名没有出现跳跃，是连续的，相同排名并列其名次。例如ios的32岁两个并列第三老。

• 拓展使用

和rank相反，我希望出现不中断的排名，这样的使用场景。

2.4 percent_rank

• 功能

见名知意，按百分比进行排名。

与 percent 函数类似，percent_rank （分布函数）函数的窗口 order by 子句所指定列中的值的返回值，是介于 0 和 1 之间的小数形式表示。

• 计算方法

(rank - 1) / (n - 1)

rank为上述rank()函数的排名，n 为当前窗口的总数。

• 示例

按使用客户端分组，统计年龄由高到低的百分比排名（percent_rank）。

另一种问法:某某用户的年龄（或者其他指标）在ios客户端排名前百分之多少?

select id,client,age,percent_rank() over(partition by client order by age desc) as rank_id
from temp.user_info where id <= 10

数据结果：

id	client	age	rank_id
3	android	35	0.0
6	ios	36	0.0
5	ios	33	0.125
9	ios	32	0.25
4	ios	32	0.25
1	ios	29	0.5
7	ios	29	0.5
8	ios	28	0.75
2	ios	26	0.875
10	ios	26	0.875

可以看出，用户9和4的年龄32岁排名ios客户端前百分之二十五，其并列排名。

• 拓展使用

该函数经常用于较大数据量的百分比占比分析，也常用于探究数据分布分析场景。

例如可以通过分布函数踢除极值，进而求解均值，降低数据误差。

2.5 cume_dist

• 功能

如果按升序排列，则统计：小于等于当前值的行数所占当前分区窗口总行数的比例。(number of rows ≤ current row)/(total number of rows）。

如果是降序排列，则统计：大于等于当前值的行数所占当前分区窗口总行数的比例。

• 示例

1.统计小于等于当前年龄的人数占总人数的比例。

另一种问法：小于等于29岁的人占总人数的比例。

select id,client,age,cume_dist() over(order by age desc) as rank_id
from temp.user_info where id <= 10
order by age

数据结果：

id	client	age	rank_id
2	ios	26	0.2
10	ios	26	0.2
8	ios	28	0.3
1	ios	29	0.5
7	ios	29	0.5
4	ios	32	0.7
9	ios	32	0.7
5	ios	33	0.8
3	android	35	0.9
6	ios	36	1.0

可以看到小于等于29岁所占人群的总比例为50%。

2.统计当前客户端分区小于等于当前年龄的人数占总人数的比例。

另一种问法：ios客户端小于等于29岁的人占总人数的比例。

select id,client,age,cume_dist() over(partition by client order by age) as rank_id
from temp.user_info where id <= 10
order by age

数据结果：

id	client	age	rank_id
2	ios	26	0.2222222222222222
10	ios	26	0.2222222222222222
8	ios	28	0.3333333333333333
1	ios	29	0.5555555555555556
7	ios	29	0.5555555555555556
4	ios	32	0.7777777777777778
9	ios	32	0.7777777777777778
5	ios	33	0.8888888888888888
3	android	35	1.0
6	ios	36	1.0

可以看到ios客户端小于等于29岁的人群占比为55.6%左右。

• 拓展使用

该函数是一个累积求比例的函数，常用于求解排名前百分之多少或者排名后百分之多少的问题。

2.6 ntile

• 功能

分桶窗口函数，用于将按指定列分组的数据按照顺序切分成N片，返回当前切片值。将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数（切片值，第几个切片，第几个分区等概念）。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。

• 注意

ntile不支持rows between，range between.

• 示例

统计按照客户端分组，按年龄排序，将每个窗口分成3片（桶），返回每片（桶）的的分片（桶）信息。

另一种问法：把ios客户端的人群按年龄正序分成三部分，返回任意一部分的值。

select id,client,age,ntile(3) over(partition by client order by age) as rank_id
from temp.user_info where id <= 10
order by age

数据结果：

id	client	age	rank_id
3	android	35	1
10	ios	26	1
2	ios	26	1
8	ios	28	1
7	ios	29	2
1	ios	29	2
4	ios	32	2
9	ios	32	3
5	ios	33	3
6	ios	36	3

可以看到ios客户端9人被分为三部分，需要哪一部分，只要再限制rank_id 等于几就行。

• 拓展使用

该函数是一个分桶函数，可以按照指定的列把数据均匀的分成想要的几部分数据。

例如，求解用户活跃时长前百分之二十的群体，如果包含0活跃时长用户，用百分比排序就不好计算了，而用该函数可以很快计算出来。

以上，为本次分享内容。

后续计划会开启一个新的系列内容：SQL每日一题系列，多来自各大厂的高频面试题，有好的算法面试题也可以积极分享，互相交流。

感谢阅读。

下一期：hive窗口分析函数使用详解之三-求偏移量类窗口函数

按例，欢迎点击此处关注我的个人公众号，交流更多知识。