详解Hive窗口函数实际应用

在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