窗口函数的5种方法总结

标签：总结窗口函数 04 OVER ORDER 分组 2015 createtime

窗口函数也称为OLAP函数，是对一组值进行操作，但是在对一组值操作时，又不需要使用group by子句，去达成分组计算的逻辑实现。而且还可以达成一条数据被分到多个组里去重复计算。

当遇到累加、累计、到什么为止这种场景时，优先考虑用窗口分析OVER解决。

窗口函数的5种方法总结_大数据

文章目录

一、聚合：sum,avg,max,min

数据
累积计算
模板

二、排名：row_number,rank,dense_rank

数据
Row number
rank 和 dense_rank

三、切片：ntile,cume_dist,percent_rank

数据
ntile

语法
例子
场景：查询某一天中时长最高的前60% 用户的平均时长

cume_dist

例子
场景：统计不同部门小于等于当前薪水的人数，所占不同部门人数的比例
percent_rank

四、分组：grouping sets, grouping_id, cube, rollup

数据
grouping sets
cube
rollup

五、取前值或后值：lag,lead,first_value,last_value

数据
lag和lead

语法

场景：计算每个用户在访问某个页面停留的时间，以及每个页面的总停留时间
first_value和last_value

语法
场景：查下每个用户，最先访问的url

一、聚合：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;

得到结果：

窗口函数的5种方法总结_spark_02

模板

分组之后的聚合的逻辑，就是在计算某条记录时，要把哪些数据放在一组做聚合计算。我们拿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 来排序。这里的分组可以理解为开窗，窗口大小默认是两个。这步完成后，数据是这样的：

窗口函数的5种方法总结_hive_03

再确定窗口的长度
窗口长度指的是现在开始遍历到哪条记录了，针对这个窗口所计算的数据有哪些。
窗口长度的模板是：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

窗口函数的5种方法总结_数据_04

当计算到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即可。

窗口函数的5种方法总结_数据_05

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';

窗口函数的5种方法总结_hadoop_06

三、切片：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;

窗口函数的5种方法总结_大数据_07

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;

窗口函数的5种方法总结_大数据_08

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;

窗口函数的5种方法总结_数据_09

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，表示结果属于哪一个分组集合。

窗口函数的5种方法总结_hive_10

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;

窗口函数的5种方法总结_hadoop_11

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;

窗口函数的5种方法总结_数据_12

得到的结果为：月天的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;

窗口函数的5种方法总结_hadoop_13

计算用户在页面停留的时间间隔

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
);

窗口函数的5种方法总结_hive_14

计算每个用户在访问某个页面停留的时间，以及每个页面的总停留时间

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;

窗口函数的5种方法总结_大数据_15

first_value和last_value

语法

first_value：取分组内排序后，截止到当前行，第一个值
last_value：取分组内排序后，截止到当前行，最后一个值
这两个可以通用：对每组取正序last_value就是对每组排倒序，然后取first_value。

场景：查下每个用户，最先访问的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;

窗口函数的5种方法总结_数据_16

标签：总结,窗口,函数,04,OVER,ORDER,分组,2015,createtime
From： https://blog.51cto.com/u_15955938/6039496