HIVE分析函数和窗口函数_hive中分析函数与窗口函数-优快云博客

本文链接：https://blog.youkuaiyun.com/cxy1991xm/article/details/80992801

一、SUM,AVG,MIN,MAX
cookieid createtime pv
---------------------------------
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

SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
FROM lxw1234;

cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
-----------------------------------------------------------------------------
cookie1 2015-04-10 1 1 1 26 1 6 26
cookie1 2015-04-11 5 6 6 26 6 13 25
cookie1 2015-04-12 7 13 13 26 13 16 20
cookie1 2015-04-13 3 16 16 26 16 18 13
cookie1 2015-04-14 2 18 18 26 17 21 10
cookie1 2015-04-15 4 22 22 26 16 20 8
cookie1 2015-04-16 4 26 26 26 13 13 4

ROWS BETWEEN含义,也叫做WINDOW子句
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

–其他AVG，MIN，MAX，和SUM用法一样。

二、NTILE 不支持WINDOW子句
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布

SELECT cookieid,createtime,pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1, --分组内将数据分成2片
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2, --分组内将数据分成3片
NTILE(4) OVER(ORDER BY createtime) AS rn3 --将所有数据分成4片
FROM lxw1234 ORDER BY cookieid,createtime;

cookieid createtime pv rn1 rn2 rn3
-------------------------------------------------
cookie1 2015-04-10 1 1 1 1
cookie1 2015-04-11 5 1 1 1
cookie1 2015-04-12 7 1 1 1
cookie1 2015-04-13 3 1 2 1
cookie1 2015-04-14 2 2 2 2
cookie1 2015-04-15 4 2 3 2
cookie1 2015-04-16 4 2 3 2
cookie2 2015-04-10 2 1 1 2
cookie2 2015-04-11 3 1 1 3
cookie2 2015-04-12 5 1 1 3
cookie2 2015-04-13 6 1 2 3
cookie2 2015-04-14 3 2 2 3
cookie2 2015-04-15 9 2 3 4
cookie2 2015-04-16 7 2 3 4

–比如，统计一个cookie，pv数最多的前1/3的天
SELECT cookieid,createtime,pv,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
FROM lxw1234 rn = 1;

三、ROW_NUMBER 不支持WINDOW子句
ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列，比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

SELECT cookieid,createtime,pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM lxw1234;

cookieid createtime pv rn
-------------------------------------------
cookie1 2015-04-12 7 1
cookie1 2015-04-11 5 2
cookie1 2015-04-15 4 3
cookie1 2015-04-16 4 4
cookie1 2015-04-13 3 5
cookie1 2015-04-14 2 6
cookie1 2015-04-10 1 7
cookie2 2015-04-15 9 1
cookie2 2015-04-16 7 2
cookie2 2015-04-13 6 3
cookie2 2015-04-12 5 4
cookie2 2015-04-14 3 5
cookie2 2015-04-11 3 6
cookie2 2015-04-10 2 7

四、RANK 和 DENSE_RANK 不支持WINDOW子句
—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 lxw1234 WHERE cookieid = 'cookie1';

cookieid createtime pv rn1 rn2 rn3
--------------------------------------------------
cookie1 2015-04-12 7 1 1 1
cookie1 2015-04-11 5 2 2 2
cookie1 2015-04-15 4 3 3 3
cookie1 2015-04-16 4 3 3 4
cookie1 2015-04-13 3 5 4 5
cookie1 2015-04-14 2 6 5 6
cookie1 2015-04-10 1 7 6 7

rn1: 15号和16号并列第3, 13号排第5
rn2: 15号和16号并列第3, 13号排第4
rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。

五、CUME_DIST 不支持WINDOW子句
–CUME_DIST (小于等于当前值的行数)/(分组内总行数)
–比如，统计小于等于当前薪水的人数，所占总人数的比例
SELECT dept,userid,sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM lxw1234;

dept userid sal rn1 rn2
-------------------------------------------
d1 user1 1000 0.2 0.3333333333333333
d1 user2 2000 0.4 0.6666666666666666
d1 user3 3000 0.6 1.0
d2 user4 4000 0.8 0.5
d2 user5 5000 1.0 1.0

rn1: 没有partition,所有数据均为1组，总行数为5，
第一行：小于等于1000的行数为1，因此，1/5=0.2
第三行：小于等于3000的行数为3，因此，3/5=0.6
rn2: 按照部门分组，dpet=d1的行数为3,
第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

六、PERCENT_RANK 不支持WINDOW子句
–PERCENT_RANK 分组内当前行的RANK值-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 lxw1234;

dept userid sal rn1 rn11 rn12 rn2
---------------------------------------------------
d1 user1 1000 0.0 1 5 0.0
d1 user2 2000 0.25 2 5 0.5
d1 user3 3000 0.5 3 5 1.0
d2 user4 4000 0.75 4 5 0.0
d2 user5 5000 1.0 5 5 1.0

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

七、LAG 不支持WINDOW子句
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 lxw1234;

cookieid createtime url rn last_1_time last_2_time
-------------------------------------------------------------------------------------------
cookie1 2015-04-10 10:00:00 url1 1 1970-01-01 00:00:00 NULL
cookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:00:00 NULL
cookie1 2015-04-10 10:03:04 1url3 3 2015-04-10 10:00:02 2015-04-10 10:00:00
cookie1 2015-04-10 10:10:00 url4 4 2015-04-10 10:03:04 2015-04-10 10:00:02
cookie1 2015-04-10 10:50:01 url5 5 2015-04-10 10:10:00 2015-04-10 10:03:04
cookie1 2015-04-10 10:50:05 url6 6 2015-04-10 10:50:01 2015-04-10 10:10:00
cookie1 2015-04-10 11:00:00 url7 7 2015-04-10 10:50:05 2015-04-10 10:50:01
cookie2 2015-04-10 10:00:00 url11 1 1970-01-01 00:00:00 NULL
cookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:00:00 NULL
cookie2 2015-04-10 10:03:04 1url33 3 2015-04-10 10:00:02 2015-04-10 10:00:00
cookie2 2015-04-10 10:10:00 url44 4 2015-04-10 10:03:04 2015-04-10 10:00:02
cookie2 2015-04-10 10:50:01 url55 5 2015-04-10 10:10:00 2015-04-10 10:03:04
cookie2 2015-04-10 10:50:05 url66 6 2015-04-10 10:50:01 2015-04-10 10:10:00
cookie2 2015-04-10 11:00:00 url77 7 2015-04-10 10:50:05 2015-04-10 10:50:01

last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'
cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02
cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01
last_2_time: 指定了往上第2行的值，为指定默认值
cookie1第一行，往上2行为NULL
cookie1第二行，往上2行为NULL
cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02
cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

八、LEAD 不支持WINDOW子句
与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 lxw1234;

cookieid createtime url rn next_1_time next_2_time
-------------------------------------------------------------------------------------------
cookie1 2015-04-10 10:00:00 url1 1 2015-04-10 10:00:02 2015-04-10 10:03:04
cookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:03:04 2015-04-10 10:10:00
cookie1 2015-04-10 10:03:04 1url3 3 2015-04-10 10:10:00 2015-04-10 10:50:01
cookie1 2015-04-10 10:10:00 url4 4 2015-04-10 10:50:01 2015-04-10 10:50:05
cookie1 2015-04-10 10:50:01 url5 5 2015-04-10 10:50:05 2015-04-10 11:00:00
cookie1 2015-04-10 10:50:05 url6 6 2015-04-10 11:00:00 NULL
cookie1 2015-04-10 11:00:00 url7 7 1970-01-01 00:00:00 NULL
cookie2 2015-04-10 10:00:00 url11 1 2015-04-10 10:00:02 2015-04-10 10:03:04
cookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:03:04 2015-04-10 10:10:00
cookie2 2015-04-10 10:03:04 1url33 3 2015-04-10 10:10:00 2015-04-10 10:50:01
cookie2 2015-04-10 10:10:00 url44 4 2015-04-10 10:50:01 2015-04-10 10:50:05
cookie2 2015-04-10 10:50:01 url55 5 2015-04-10 10:50:05 2015-04-10 11:00:00
cookie2 2015-04-10 10:50:05 url66 6 2015-04-10 11:00:00 NULL
cookie2 2015-04-10 11:00:00 url77 7 1970-01-01 00:00:00 NULL

--逻辑与LAG一样，只不过LAG是往上，LEAD是往下。

九、FIRST_VALUE 不支持WINDOW子句
取分组内排序后，截止到当前行，第一个值
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 lxw1234;

cookieid createtime url rn first1
---------------------------------------------------------
cookie1 2015-04-10 10:00:00 url1 1 url1
cookie1 2015-04-10 10:00:02 url2 2 url1
cookie1 2015-04-10 10:03:04 1url3 3 url1
cookie1 2015-04-10 10:10:00 url4 4 url1
cookie1 2015-04-10 10:50:01 url5 5 url1
cookie1 2015-04-10 10:50:05 url6 6 url1
cookie1 2015-04-10 11:00:00 url7 7 url1
cookie2 2015-04-10 10:00:00 url11 1 url11
cookie2 2015-04-10 10:00:02 url22 2 url11
cookie2 2015-04-10 10:03:04 1url33 3 url11
cookie2 2015-04-10 10:10:00 url44 4 url11
cookie2 2015-04-10 10:50:01 url55 5 url11
cookie2 2015-04-10 10:50:05 url66 6 url11
cookie2 2015-04-10 11:00:00 url77 7 url11

十、LAST_VALUE 不支持WINDOW子句
取分组内排序后，截止到当前行，最后一个值
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 lxw1234
ORDER BY cookieid,createtime;

cookieid createtime url rn last1 last2
-------------------------------------------------------------
cookie1 2015-04-10 10:00:00 url1 1 url1 url7
cookie1 2015-04-10 10:00:02 url2 2 url2 url7
cookie1 2015-04-10 10:03:04 1url3 3 1url3 url7
cookie1 2015-04-10 10:10:00 url4 4 url4 url7
cookie1 2015-04-10 10:50:01 url5 5 url5 url7
cookie1 2015-04-10 10:50:05 url6 6 url6 url7
cookie1 2015-04-10 11:00:00 url7 7 url7 url7
cookie2 2015-04-10 10:00:00 url11 1 url11 url77
cookie2 2015-04-10 10:00:02 url22 2 url22 url77
cookie2 2015-04-10 10:03:04 1url33 3 1url33 url77
cookie2 2015-04-10 10:10:00 url44 4 url44 url77
cookie2 2015-04-10 10:50:01 url55 5 url55 url77
cookie2 2015-04-10 10:50:05 url66 6 url66 url77
cookie2 2015-04-10 11:00:00 url77 7 url77 url77

十一、GROUPING SETS
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
SELECT month,day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;

month day uv GROUPING__ID
------------------------------------------------
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-03-10 4 2
NULL 2015-03-12 1 2
NULL 2015-04-12 2 2
NULL 2015-04-13 3 2
NULL 2015-04-15 2 2
NULL 2015-04-16 2 2
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3
2015-04 2015-04-16 2 3

等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

其中的 GROUPING__ID，表示结果属于哪一个分组集合。

十二、CUBE
根据GROUP BY的维度的所有组合进行聚合。
SELECT month,day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;

month day uv GROUPING__ID
--------------------------------------------
NULL NULL 7 0
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-04-12 2 2
NULL 2015-04-13 3 2
NULL 2015-04-15 2 2
NULL 2015-04-16 2 2
NULL 2015-03-10 4 2
NULL 2015-03-12 1 2
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-16 2 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3

等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

十三、ROLLUP
是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。比如，以month维度进行层级聚合：
SELECT month,day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;

month day uv GROUPING__ID
---------------------------------------------------
NULL NULL 7 0
2015-03 NULL 5 1
2015-04 NULL 6 1
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3
2015-04 2015-04-16 2 3

可以实现这样的上钻过程：
月天的UV->月的UV->总UV