在 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,1cookie1,2021-05-11,5cookie1,2021-05-12,7cookie1,2021-05-13,3cookie1,2021-05-14,2cookie1,2021-05-15,4cookie1,2021-05-16,4
复制代码
执行如下查询语句：
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,1cookie1,2021-05-11,5cookie1,2021-05-12,7cookie1,2021-05-13,3cookie1,2021-05-14,2cookie1,2021-05-15,4cookie1,2021-05-16,4cookie2,2021-05-10,2cookie2,2021-05-11,3cookie2,2021-05-12,5cookie2,2021-05-13,6cookie2,2021-05-14,3cookie2,2021-05-15,9cookie2,2021-05-16,7
复制代码
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()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。
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 可以看成是：把有序的数据集合平均分配到指定的数量（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 rn3FROM 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,url2cookie1,2021-06-10 10:00:00,url1cookie1,2021-06-10 10:03:04,1url3cookie1,2021-06-10 10:50:05,url6cookie1,2021-06-10 11:00:00,url7cookie1,2021-06-10 10:10:00,url4cookie1,2021-06-10 10:50:01,url5cookie2,2021-06-10 10:00:02,url22cookie2,2021-06-10 10:00:00,url11cookie2,2021-06-10 10:03:04,1url33cookie2,2021-06-10 10:50:05,url66cookie2,2021-06-10 11:00:00,url77cookie2,2021-06-10 10:10:00,url44cookie2,2021-06-10 10:50:01,url55
复制代码
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:01last_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
复制代码
与 LAG 相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值。
第一个参数为列名，第二个参数为往下第 n 行（可选，默认为 1），第三个参数为默认值（当往下第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）
SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time FROM user_url;
复制代码
结果如下：
取分组内排序后，截止到当前行，第一个值。
SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 FROM user_url;
复制代码
结果如下：
取分组内排序后，截止到当前行，最后一个值。
SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 FROM user_url;
复制代码
结果如下：
如果想要取分组内排序后最后一个值，则需要变通一下：
SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 FROM user_url ORDER BY cookieid,createtime;
复制代码
注意上述 SQL，使用的是 FIRST_VALUE 的倒序取出分组内排序最后一个值！
结果如下：
此处要特别注意 order by
如果不指定 ORDER BY，则进行排序混乱，会出现错误的结果
SELECT cookieid,createtime,url,FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2 FROM user_url;
复制代码
结果如下：
上述 url2 和 url55 的 createtime 即不属于最靠前的时间也不属于最靠后的时间，所以结果是混乱的。
4. CUME_DIST
先创建一张员工薪水表：staff_salary
CREATE EXTERNAL TABLE staff_salary (dept string,userid string,sal int);
复制代码
表中加入如下数据：
d1,user1,1000d1,user2,2000d1,user3,3000d2,user4,4000d2,user5,5000
复制代码
此函数的结果和 order by 的排序顺序有关系。
CUME_DIST：小于等于当前值的行数/分组内总行数。 order 默认顺序：正序
比如，统计小于等于当前薪水的人数，所占总人数的比例。
SELECT dept,userid,sal,CUME_DIST() OVER(ORDER BY sal) AS rn1,CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 FROM staff_salary;
复制代码
结果如下：
解释：
rn1: 没有partition,所有数据均为1组，总行数为5， 第一行：小于等于1000的行数为1，因此，1/5=0.2 第三行：小于等于3000的行数为3，因此，3/5=0.6rn2: 按照部门分组，dpet=d1的行数为3, 第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666
复制代码
5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP
这几个分析函数通常用于 OLAP 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的 UV 数。
还是先创建一个用户访问表：user_date
CREATE TABLE user_date (month STRING,day STRING, cookieid STRING );
复制代码
表中加入如下数据：
2021-03,2021-03-10,cookie12021-03,2021-03-10,cookie52021-03,2021-03-12,cookie72021-04,2021-04-12,cookie32021-04,2021-04-13,cookie22021-04,2021-04-13,cookie42021-04,2021-04-16,cookie42021-03,2021-03-10,cookie22021-03,2021-03-10,cookie32021-04,2021-04-12,cookie52021-04,2021-04-13,cookie62021-04,2021-04-15,cookie32021-04,2021-04-15,cookie22021-04,2021-04-16,cookie1
复制代码
grouping sets 是一种将多个 group by 逻辑写在一个 sql 语句中的便利写法。
等价于将不同维度的 GROUP BY 结果集进行 UNION ALL。
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM user_date GROUP BY month,day GROUPING SETS (month,day) ORDER BY GROUPING__ID;
复制代码
注：上述 SQL 中的 GROUPING__ID，是个关键字，表示结果属于哪一个分组集合，根据 grouping sets 中的分组条件 month，day，1 是代表 month，2 是代表 day。
结果如下：
上述 SQL 等价于：
SELECT month,NULL as day,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month
UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day;
复制代码
根据 GROUP BY 的维度的所有组合进行聚合。
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM user_date GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID;
复制代码
结果如下：
上述 SQL 等价于：
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM user_date
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM user_date GROUP BY month,day;
复制代码
是 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。
比如，以 month 维度进行层级聚合：
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM user_date GROUP BY month,dayWITH ROLLUP ORDER BY GROUPING__ID;
复制代码
结果如下：
把 month 和 day 调换顺序，则以 day 维度进行层级聚合：
SELECT day,month,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM user_date GROUP BY day,month WITH ROLLUP ORDER BY GROUPING__ID;
复制代码
结果如下：
这里，根据日和月进行聚合，和根据日聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样。
窗口函数实际应用
1. 第二高的薪水
难度简单。
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。
+----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+
复制代码
例如上述 Employee 表，SQL 查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+---------------------+| SecondHighestSalary |+---------------------+| 200 |+---------------------+
复制代码
这道题可以用 row_number 函数解决。
参考代码：
SELECT * FROM( SELECT Salary, row_number() over(order by Salary desc) rk FROM Employee ) t WHERE t.rk = 2;
复制代码
更简单的代码：
SELECT DISTINCT SalaryFROM EmployeeORDER BY Salary DESCLIMIT 1 OFFSET 1
复制代码
OFFSET：偏移量，表示从第几条数据开始取，0 代表第 1 条数据。
2. 分数排名
难度简单。
编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
+----+-------+| Id | Score |+----+-------+| 1 | 3.50 || 2 | 3.65 || 3 | 4.00 || 4 | 3.85 || 5 | 4.00 || 6 | 3.65 |+----+-------+
复制代码
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+| Score | Rank |+-------+------+| 4.00 | 1 || 4.00 | 1 || 3.85 | 2 || 3.65 | 3 || 3.65 | 3 || 3.50 | 4 |+-------+------+
复制代码
参考代码：
SELECT Score,dense_rank() over(order by Score desc) as `Rank`FROM Scores;
复制代码
3. 连续出现的数字
难度中等。
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+| Id | Num |+----+-----+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 1 || 6 | 2 || 7 | 2 |+----+-----+
复制代码
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+| ConsecutiveNums |+-----------------+| 1 |+-----------------+
复制代码
参考代码：
SELECT DISTINCT `Num` as ConsecutiveNumsFROM ( SELECT Num, lead(Num, 1, null) over(order by id) n2, lead(Num, 2, null) over(order by id) n3 FROM Logs ) t1WHERE Num = n2 and Num = n3
复制代码
4. 连续 N 天登录
难度困难。
写一个 SQL 查询, 找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户，返回的结果表按照 id 排序。
表 Accounts：
+----+-----------+| id | name |+----+-----------+| 1 | Winston || 7 | Jonathan |+----+-----------+
复制代码
表 Logins：
+----+-------------+| id | login_date |+----+-------------+| 7 | 2020-05-30 || 1 | 2020-05-30 || 7 | 2020-05-31 || 7 | 2020-06-01 || 7 | 2020-06-02 || 7 | 2020-06-02 || 7 | 2020-06-03 || 1 | 2020-06-07 || 7 | 2020-06-10 |+----+-------------+
复制代码
例如，给定上面的 Accounts 和 Logins 表，至少连续 5 天登录账户的是 id=7 的用户
+----+-----------+| id | name |+----+-----------+| 7 | Jonathan |+----+-----------+
复制代码
思路：
去重：由于每个人可能一天可能不止登陆一次，需要去重
排序：对每个 ID 的登录日期排序
差值：计算登录日期与排序之间的差值，找到连续登陆的记录
连续登录天数计算：select id, count(*) group by id, 差值（伪代码）
取出登录 5 天以上的记录
通过表合并，取出 id 对应用户名
参考代码：
SELECT DISTINCT b.id, nameFROM (SELECT id, login_date, DATE_SUB(login_date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY login_date)) AS diff FROM(SELECT DISTINCT id, login_date FROM Logins) a) bINNER JOIN Accounts acON b.id = ac.idGROUP BY b.id, diffHAVING COUNT(b.id) >= 5
复制代码
注意点：
DATE_SUB 的应用：DATE_SUB (DATE, X)，注意，X 为正数表示当前日期的前 X 天；
如何找连续日期：通过排序与登录日期之间的差值，因为排序连续，因此若登录日期连续，则差值一致；
GROUP BY 和 HAVING 的应用：通过 id 和差值的 GROUP BY，用 COUNT 找到连续天数大于 5 天的 id，注意 COUNT 不是一定要出现在 SELECT 后，可以直接用在 HAVING 中
5. 给定数字的频率查询中位数
难度困难。
Numbers 表保存数字的值及其频率。
+----------+-------------+| Number | Frequency |+----------+-------------|| 0 | 7 || 1 | 1 || 2 | 3 || 3 | 1 |+----------+-------------+
复制代码
在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。
+--------+| median |+--------|| 0.0000 |+--------+
复制代码
请编写一个查询来查找所有数字的中位数并将结果命名为 median 。
参考代码：
selectavg(cast(number as float)) as medianfrom ( select Number, Frequency, sum(Frequency) over(order by Number) - Frequency as prev_sum, sum(Frequency) over(order by Number) as curr_sum from Numbers ) t1, ( select sum(Frequency) as total_sum from Numbers ) t2wheret1.prev_sum <= (cast(t2.total_sum as float) / 2) andt1.curr_sum >= (cast(t2.total_sum as float) / 2)
复制代码
评论