详解Hive窗口函数实际应用
在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数
在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数,窗口函数兼具分组和排序功能。
本文分为两部分:
第一部分是Hive窗口函数详解,剖析各种窗口函数(几乎涵盖Hive所有的窗口函数);
第二部分是窗口函数实际应用,这部分总共有五个例子,都是工作常用、面试必问的非常经典的例子。
Hive 窗口函数
窗口函数最重要的关键字是 partition by 和 order by
具体语法如下:XXX over (partition by xxx order by xxx)
特别注意:over()里面的 partition by 和 order by 都不是必选的,over()里面可以只有partition by,也可以只有order by,也可以两个都没有,大家需根据需求灵活运用。
窗口函数我划分了几个大类,我们一类一类的讲解。
1. SUM、AVG、MIN、MAX
讲解这几个窗口函数前,先创建一个表,以实际例子讲解大家更容易理解。
首先创建用户访问页面表:user_pv
create table user_pv(
cookieid string, -- 用户登录的cookie,即用户标识
createtime string, -- 日期
pv int -- 页面访问量
);
给上面这个表加上如下数据:
cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
SUM()使用
执行如下查询语句:
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from user_pv;
结果如下:(因命令行原因,下图字段名和值是错位的,请注意辨别!)
执行如下查询语句:
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid ) as pv1
from user_pv;
结果如下:
第一条SQL的over()里面加 order by ,第二条SQL没加order by ,结果差别很大
所以要注意了:
over()里面加 order by 表示:分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号;
over()里面不加 order by 表示:将分组内所有值累加。
AVG,MIN,MAX,和SUM用法一样,这里就不展开讲了,但是要注意 AVG,MIN,MAX 的over()里面加不加 order by 也和SUM一样,如 AVG 求平均值,如果加上 order by,表示分组内从起点到当前行的平局值,不是全部的平局值。MIN,MAX 同理。
2. ROW_NUMBER、RANK、DENSE_RANK、NTILE
还是用上述的用户登录日志表:user_pv,里面的数据换成如下所示:
cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
cookie2,2021-05-10,2
cookie2,2021-05-11,3
cookie2,2021-05-12,5
cookie2,2021-05-13,6
cookie2,2021-05-14,3
cookie2,2021-05-15,9
cookie2,2021-05-16,7
ROW_NUMBER()使用:
ROW_NUMBER()从1开始,按照顺序,生成分组内记录的序列。
SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM user_pv;
结果如下:
RANK 和 DENSE_RANK 使用:
RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位。
DENSE_RANK()生成数据项在分组中的排名,排名相等会在名次中不会留下空位。
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM user_pv
WHERE cookieid = 'cookie1';
结果如下:
NTILE的使用:
有时会有这样的需求:如果数据排序后分为三部分,业务人员只关心其中的一部分,如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。
ntile可以看成是:把有序的数据集合平均分配到指定的数量(num)个桶中, 将桶号分配给每一行。如果不能平均分配,则优先分配较小编号的桶,并且各个桶中能放的行数最多相差1。
然后可以根据桶号,选取前或后 n分之几的数据。数据会完整展示出来,只是给相应的数据打标签;具体要取几分之几的数据,需要再嵌套一层根据标签取出。
SELECT
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM user_pv
ORDER BY cookieid,createtime;
结果如下:
3. LAG、LEAD、FIRST_VALUE、LAST_VALUE
讲解这几个窗口函数时还是以实例讲解,首先创建用户访问页面表:user_url
CREATE TABLE user_url (
cookieid string,
createtime string, --页面访问时间
url string --被访问页面
);
表中加入如下数据:
cookie1,2021-06-10 10:00:02,url2
cookie1,2021-06-10 10:00:00,url1
cookie1,2021-06-10 10:03:04,1url3
cookie1,2021-06-10 10:50:05,url6
cookie1,2021-06-10 11:00:00,url7
cookie1,2021-06-10 10:10:00,url4
cookie1,2021-06-10 10:50:01,url5
cookie2,2021-06-10 10:00:02,url22
cookie2,2021-06-10 10:00:00,url11
cookie2,2021-06-10 10:03:04,1url33
cookie2,2021-06-10 10:50:05,url66
cookie2,2021-06-10 11:00:00,url77
cookie2,2021-06-10 10:10:00,url44
cookie2,2021-06-10 10:50:01,url55