窗口函数也称为OLAP函数,是对一组值进行操作,但是在对一组值操作时,又不需要使用group by子句,去达成分组计算的逻辑实现。而且还可以达成一条数据被分到多个组里去重复计算。
当遇到累加、累计、到什么为止这种场景时,优先考虑用窗口分析OVER解决。
文章目录
一、聚合:sum,avg,max,min
数据
task1,2022-04-10,1
task1,2022-04-11,5
task1,2022-04-12,7
task1,2022-04-13,3
task1,2022-04-14,2
task1,2022-04-15,4
task1,2022-04-16,4
累积计算
SELECT taskid,createtime,pv,
-- pv1:分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
SUM(pv) OVER(PARTITION BY taskid ORDER BY createtime) AS pv1,
-- pv2:同pv1
SUM(pv) OVER(PARTITION BY taskid ORDER BY createtime ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS pv2,
-- pv3: 分组内(cookie1)所有的pv累加
SUM(pv) OVER(PARTITION BY taskid) AS pv3,
-- pv4: 分组内当前行+往前3行,11号=10号+11号, 12号=10号+11号+12号, 13号=10号+11号+12号+13号, 14号=11号+12号+13号+14号
SUM(pv) OVER(PARTITION BY taskid ORDER BY createtime ROWS BETWEEN 3 PRECEDING
AND CURRENT ROW) AS pv4,
-- pv5: 分组内当前行+往前3行+往后1行,如,14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
SUM(pv) OVER(PARTITION BY taskid ORDER BY createtime ROWS BETWEEN 3 PRECEDING
AND 1 FOLLOWING) AS pv5,
-- pv6: 分组内当前行+往后所有行,如,13号=13号+14号+15号+16号=3+2+4+4=13,14号=14号+15号+16号=2+4+4=10
SUM(pv) OVER(PARTITION BY taskid ORDER BY createtime ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS pv6
FROM hive_zjyprc_hadoop.tmp.task1 order by taskid, createtime;
得到结果:
模板
分组之后的聚合的逻辑,就是在计算某条记录时,要把哪些数据放在一组做聚合计算。我们拿pv4来举例说明:
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4
-
先确定哪些数据是一组,以及在一组中,数据的顺序是怎样的
PARTITION BY cookieid ORDER BY:按照cookieid 来分组,按照createtime 来排序。这里的分组可以理解为开窗,窗口大小默认是两个。这步完成后,数据是这样的:
-
再确定窗口的长度
窗口长度指的是现在开始遍历到哪条记录了,针对这个窗口所计算的数据有哪些。
窗口长度的模板是:Rows between A and B
。
A有三种写法:- unbounded preceding 表示从当前组的第一条开始,一直到这组的第一条
- X preceding 从当前行数开始,不算当前这条,往前再数X条
- current row 当前行
B有三种写法:
- unbounded following 到分组的最后一行结束 ,从当前行到分组的最后一行
- X following 当前行的后*行,往后取X行
- current row 当前行
拿pv4来看:ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
当计算到4-13的数据时,往前推3个计算到当前行3,求sum:1+5+7+3=16
- 最后再看对这个窗口执行什么计算
最后执行的计算为聚合函数,可以对窗口中的求sum,avg,max或min。
二、排名:row_number,rank,dense_rank
当遇到把表中的每一条数据都要放入对应的组里面做一个排序的场景。row_number, rank, dense_rank就非常有用。
数据
cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7
Row number
ROW_NUMBER() 的功能是:从1开始,按照顺序,生成分组内记录的序列。比如:
- 可以按照pv降序排列,生成分组内每天的pv名次;
- 获取分组内排序第一的记录;
- 获取一个session中的第一条refer
SELECT taskid, createtime, pv,
ROW_NUMBER() OVER(PARTITION BY taskid ORDER BY pv desc) AS rn
FROM hive_zjyprc_hadoop.tmp.task1;
所以如果需要取每一组的前3名,只需要rn<=3即可。
rank 和 dense_rank
- RANK() 生成数据项在分组中的排名,当排名相等时,会在名次中留下空位,新值排序会增加。如:335
- DENSE_RANK() 生成数据项在分组中的排名,当排名相等时,会在名次中不会留下空位,并列排名,新值排序不会变。如:334
SELECT taskid, createtime, pv,
RANK() OVER(PARTITION BY taskid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY taskid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY taskid ORDER BY pv DESC) AS rn3
FROM hive_zjyprc_hadoop.tmp.task1
WHERE taskid = 'task1';
三、切片:ntile,cume_dist,percent_rank
数据
d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
ntile
语法
NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布。
NTILE不支持窗口语法,即ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
例子
SELECT taskid,createtime,pv,
--分组内将数据分成2片
NTILE(2) OVER(PARTITION BY taskid ORDER BY createtime) AS rn1,
--分组内将数据分成3片
NTILE(3) OVER(PARTITION BY taskid ORDER BY createtime) AS rn2,
--将所有数据分成4片,默认asc升序
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM hive_zjyprc_hadoop.tmp.task1
ORDER BY taskid,createtime;
ntile的场景适合去统计百分数的top组,比如:查询某一天中时长最高的前60% 用户的平均时长
场景:查询某一天中时长最高的前60% 用户的平均时长
利用ntile函数按照时长降序将其分为五组,则排名为第1,2,3组的则是前60%的用户时长。
SELECT avg(时长)
FROM (
SELECT 时长, ntile ( 5 ) over ( ORDER BY 时长 desc ) AS ranking
FROM 表1
) a
WHERE a.ranking in (1,2,3);
cume_dist
CUME_DIST 小于等于当前值的行数/分组内总行数。
例子
对当前数据统计小于等于当前薪水的人数,所占总人数的比例。
SELECT dept, userid, sal,
round(CUME_DIST() OVER(ORDER BY sal), 2) AS rn1,
round(CUME_DIST() OVER(PARTITION BY dept ORDER BY sal desc), 2) AS rn2
FROM hive_zjyprc_hadoop.tmp.task1;
rn1: 没有partition,所有数据均为1组,总行数为5
- 第一行:小于等于3000的行数为3,因此,3/5=0.6
- 第三行:小于等于1000的行数为1,因此,1/5=0.2
rn2: 按照部门分组,dpet=d1的行数为3,
- 第二行:小于等于2000的行数为2,因此,2/3=0.67
对于重复值,计算的时候,取重复值的最后一行的位置。
场景:统计不同部门小于等于当前薪水的人数,所占不同部门人数的比例
cume_dist的实际场景可以统计某个值在总值中的分布,如:
统计不同部门小于等于当前薪水的人数,所占不同部门人数的比例
SELECT 部门, 人, 薪水,
round(CUME_DIST() OVER(PARTITION BY 部门 ORDER BY 薪水), 2) AS rn1
FROM 表;
percent_rank
percent_rank:和cume_dist 的不同点在于计算分布结果的方法,计算方法为(相对位置-1)/(总行数-1)
SELECT dept, userid, sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1, --分组内
RANK() OVER(ORDER BY sal) AS rn11, --分组内RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12, --分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM hive_zjyprc_hadoop.tmp.task1;
rn1: rn1 = (rn11-1) / (rn12-1)
- 第一行,(1-1)/(5-1)=0/4=0
- 第二行,(2-1)/(5-1)=1/4=0.25
- 第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组,dept=d1的总行数为3
- 第一行,(1-1)/(3-1)=0
- 第三行,(3-1)/(3-1)=1
四、分组:grouping sets, grouping_id, cube, rollup
这几个分析函数通常用于OLAP中,不能累加,而且需要根据不同维度上钻和下钻的指标统计,比如,
分小时、天、月的UV数。
数据
2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
grouping sets
在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL。
SELECT month, day,
COUNT(DISTINCT cookied) AS uv,GROUPING__ID
FROM hive_zjyprc_hadoop.tmp.task4
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
-- 等价于分别group by 做union all
ORDER BY GROUPING__ID;
GROUPING__ID,表示结果属于哪一个分组集合。
cube
根据GROUP BY的维度的所有组合进行聚合
SELECT month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM hive_zjyprc_hadoop.tmp.task4
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
rollup
是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合。
比如,以month维度进行层级聚合,SQL语句
SELECT month, day, COUNT(DISTINCT cookied) AS uv, GROUPING__ID
FROM hive_zjyprc_hadoop.tmp.task4
GROUP BY month,day WITH ROLLUP ORDER BY GROUPING__ID;
得到的结果为:月天的UV->月的UV->总UV
with rollup 最后会出现的 null,这一行是针对每次分组前 ,需要显示的某列运用分组后的集合运算得出的值。
五、取前值或后值:lag,lead,first_value,last_value
数据
Peter,2015-10-1201:10:00,url1
Peter,2015-10-1201:15:10,url2
Peter,2015-10-1201:16:40,url3
Peter,2015-10-1202:13:00,url4
Peter,2015-10-1203:14:30,url5
Marry,2015-11-1201:10:00,url1
Marry,2015-11-1201:15:10,url2
Marry,2015-11-1201:16:40,url3
Marry,2015-11-1202:13:00,url4
Marry,2015-11-1203:14:30,url5
lag和lead
语法
这俩函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
- LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名,
第二个参数为往上第n行(可选,默认为1),
第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL) - LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名,
第二个参数为往下第n行(可选,默认为1),
第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
场景:计算每个用户在访问某个页面停留的时间,以及每个页面的总停留时间
实际场景:计算每个用户在访问某个页面停留的时间,以及每个页面的总停留时间。
思路:
- 获取用户在某个页面停留的起始与结束时间
select cookieid,
createtime stime,
lead(createtime) over(partition by cookieid order by createtime) etime,
url
from hive_zjyprc_hadoop.tmp.task5;
- 计算用户在页面停留的时间间隔
SELECT cookieid
,stime
,etime
,UNIX_TIMESTAMP(etime,'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(stime,'yyyy-MM-dd HH:mm:ss') AS period
,url
FROM
(
SELECT cookieid
,createtime stime
,lead(createtime) over(partition by cookieid ORDER BY createtime) etime
,url
FROM hive_zjyprc_hadoop.tmp.task5
);
- 计算每个用户在访问某个页面停留的时间,以及每个页面的总停留时间
SELECT url
,cookieid
,sum(period)
FROM
(
SELECT cookieid
,createtime stime
,lead(createtime) over(partition by cookieid ORDER BY createtime) etime
,UNIX_TIMESTAMP(lead(createtime) over(partition by cookieid ORDER BY createtime),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(createtime,'yyyy-MM-dd HH:mm:ss') AS period
,url
FROM hive_zjyprc_hadoop.tmp.task5
) temp
group by url, cookieid with ROLLUP;
first_value和last_value
语法
first_value:取分组内排序后,截止到当前行,第一个值
last_value:取分组内排序后,截止到当前行,最后一个值
这两个可以通用:对每组取正序last_value就是对每组排倒序,然后取first_value。
场景:查下每个用户,最先访问的url
场景:查下每个用户,最先访问的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 hive_zjyprc_hadoop.tmp.task5;