在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
- LAG的使用：
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time, LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time FROM user_url;
结果如下：
解释：
last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00' cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00 cookie1第三行，往上1行值为第二行值，2021-06-10 10:00:02 cookie1第六行，往上1行值为第五行值，2021-06-10 10:50:01 last_2_time: 指定了往上第2行的值，为指定默认值 cookie1第一行，往上2行为NULL cookie1第二行，往上2行为NULL cookie1第四行，往上2行为第二行值，2021-06-10 10:00:02 cookie1第七行，往上2行为第五行值，2021-06-10 10:50:01
