Hive窗口函數(shù)/分析函數(shù)詳解
在sql中有一類函數(shù)叫做聚合函數(shù),例如sum()、avg()、max()等等,這類函數(shù)可以將多行數(shù)據(jù)按照規(guī)則聚集為一行,一般來(lái)講聚集后的行數(shù)是要少于聚集前的行數(shù)的。但是有時(shí)我們想要既顯示聚集前的數(shù)據(jù),又要顯示聚集后的數(shù)據(jù),這時(shí)我們便引入了窗口函數(shù)。窗口函數(shù)又叫OLAP函數(shù)/分析函數(shù)，窗口函數(shù)兼具分組和排序功能。
窗口函數(shù)最重要的關(guān)鍵字是 partition by 和 order by。
具體語(yǔ)法如下：over (partition by xxx order by xxx)
sum,avg,min,max 函數(shù)
準(zhǔn)備數(shù)據(jù)
1建表語(yǔ)句:
2create table bigdata_t1(
3cookieid string,
4createtime string, --day
5pv int
6) row format delimited
7fields terminated by ',';
8
9加載數(shù)據(jù)：
10load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_t1;
11
12cookie1,2018-04-10,1
13cookie1,2018-04-11,5
14cookie1,2018-04-12,7
15cookie1,2018-04-13,3
16cookie1,2018-04-14,2
17cookie1,2018-04-15,4
18cookie1,2018-04-16,4
19
20開(kāi)啟智能本地模式
21SET hive.exec.mode.local.auto=true;
SUM函數(shù)和窗口函數(shù)的配合使用：結(jié)果和ORDER BY相關(guān),默認(rèn)為升序。
1#pv1
2select cookieid,createtime,pv,
3sum(pv) over(partition by cookieid order by createtime) as pv1
4from bigdata_t1;
5
6#pv2
7select cookieid,createtime,pv,
8sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
9from bigdata_t1;
10
11#pv3
12select cookieid,createtime,pv,
13sum(pv) over(partition by cookieid) as pv3
14from bigdata_t1;
15
16#pv4
17select cookieid,createtime,pv,
18sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
19from bigdata_t1;
20
21#pv5
22select cookieid,createtime,pv,
23sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
24from bigdata_t1;
25
26#pv6
27select cookieid,createtime,pv,
28sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
29from bigdata_t1;
30
31
32pv1: 分組內(nèi)從起點(diǎn)到當(dāng)前行的pv累積，如，11號(hào)的pv1=10號(hào)的pv+11號(hào)的pv, 12號(hào)=10號(hào)+11號(hào)+12號(hào)
33pv2: 同pv1
34pv3: 分組內(nèi)(cookie1)所有的pv累加
35pv4: 分組內(nèi)當(dāng)前行+往前3行，如，11號(hào)=10號(hào)+11號(hào)， 12號(hào)=10號(hào)+11號(hào)+12號(hào)，
36 13號(hào)=10號(hào)+11號(hào)+12號(hào)+13號(hào)， 14號(hào)=11號(hào)+12號(hào)+13號(hào)+14號(hào)
37pv5: 分組內(nèi)當(dāng)前行+往前3行+往后1行，如，14號(hào)=11號(hào)+12號(hào)+13號(hào)+14號(hào)+15號(hào)=5+7+3+2+4=21
38pv6: 分組內(nèi)當(dāng)前行+往后所有行，如，13號(hào)=13號(hào)+14號(hào)+15號(hào)+16號(hào)=3+2+4+4=13，
39 14號(hào)=14號(hào)+15號(hào)+16號(hào)=2+4+4=10
如果不指定rows between,默認(rèn)為從起點(diǎn)到當(dāng)前行;
如果不指定order by，則將分組內(nèi)所有值累加;
關(guān)鍵是理解rows between含義,也叫做window子句：
preceding：往前
following：往后
current row：當(dāng)前行
unbounded：起點(diǎn)
unbounded preceding 表示從前面的起點(diǎn)
unbounded following：表示到后面的終點(diǎn)
AVG，MIN，MAX，和SUM用法一樣。
row_number,rank,dense_rank,ntile函數(shù)
準(zhǔn)備數(shù)據(jù)
1cookie1,2018-04-10,1
2cookie1,2018-04-11,5
3cookie1,2018-04-12,7
4cookie1,2018-04-13,3
5cookie1,2018-04-14,2
6cookie1,2018-04-15,4
7cookie1,2018-04-16,4
8cookie2,2018-04-10,2
9cookie2,2018-04-11,3
10cookie2,2018-04-12,5
11cookie2,2018-04-13,6
12cookie2,2018-04-14,3
13cookie2,2018-04-15,9
14cookie2,2018-04-16,7
15
16CREATE TABLE bigdata_t2 (
17cookieid string,
18createtime string, --day
19pv INT
20) ROW FORMAT DELIMITED
21FIELDS TERMINATED BY ','
22stored as textfile;
23
24加載數(shù)據(jù)：
25load data local inpath '/root/hivedata/bigdata_t2.dat' into table bigdata_t2;
ROW_NUMBER()使用
ROW_NUMBER()從1開(kāi)始，按照順序，生成分組內(nèi)記錄的序列。
1SELECT
2cookieid,
3createtime,
4pv,
5ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
6FROM bigdata_t2;
RANK 和 DENSE_RANK使用
RANK() 生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中留下空位 。
DENSE_RANK()生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中不會(huì)留下空位。
1SELECT
2cookieid,
3createtime,
4pv,
5RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
6DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
7ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
8FROM bigdata_t2
9WHERE cookieid = 'cookie1';
NTILE
有時(shí)會(huì)有這樣的需求:如果數(shù)據(jù)排序后分為三部分，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來(lái)呢?NTILE函數(shù)即可以滿足。
ntile可以看成是：把有序的數(shù)據(jù)集合平均分配到指定的數(shù)量（num）個(gè)桶中, 將桶號(hào)分配給每一行。如果不能平均分配，則優(yōu)先分配較小編號(hào)的桶，并且各個(gè)桶中能放的行數(shù)最多相差1。
然后可以根據(jù)桶號(hào)，選取前或后 n分之幾的數(shù)據(jù)。數(shù)據(jù)會(huì)完整展示出來(lái)，只是給相應(yīng)的數(shù)據(jù)打標(biāo)簽；具體要取幾分之幾的數(shù)據(jù)，需要再嵌套一層根據(jù)標(biāo)簽取出。
1SELECT
2cookieid,
3createtime,
4pv,
5NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
6NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
7NTILE(4) OVER(ORDER BY createtime) AS rn3
8FROM bigdata_t2
9ORDER BY cookieid,createtime;
其他一些窗口函數(shù)
lag,lead,first_value,last_value 函數(shù)
LAG
LAG(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往上第n行值第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往上第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL）
1 SELECT cookieid,
2 createtime,
3 url,
4 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
5 LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
6 LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
7 FROM bigdata_t4;
8
9
10 last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'
11 cookie1第一行，往上1行為NULL,因此取默認(rèn)值 1970-01-01 00:00:00
12 cookie1第三行，往上1行值為第二行值，2015-04-10 10:00:02
13 cookie1第六行，往上1行值為第五行值，2015-04-10 10:50:01
14 last_2_time: 指定了往上第2行的值，為指定默認(rèn)值
15 cookie1第一行，往上2行為NULL
16 cookie1第二行，往上2行為NULL
17 cookie1第四行，往上2行為第二行值，2015-04-10 10:00:02
18 cookie1第七行，往上2行為第五行值，2015-04-10 10:50:01
LEAD
與LAG相反
LEAD(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往下第n行值
第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往下第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL）
1 SELECT cookieid,
2 createtime,
3 url,
4 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
5 LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
6 LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
7 FROM bigdata_t4;
FIRST_VALUE
取分組內(nèi)排序后，截止到當(dāng)前行，第一個(gè)值
1 SELECT cookieid,
2 createtime,
3 url,
4 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
5 FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
6 FROM bigdata_t4;
LAST_VALUE
取分組內(nèi)排序后，截止到當(dāng)前行，最后一個(gè)值
1 SELECT cookieid,
2 createtime,
3 url,
4 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
5 LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
6 FROM bigdata_t4;
如果想要取分組內(nèi)排序后最后一個(gè)值，則需要變通一下：
1 SELECT cookieid,
2 createtime,
3 url,
4 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
5 LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
6 FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
7 FROM bigdata_t4
8 ORDER BY cookieid,createtime;
特別注意order by
如果不指定ORDER BY，則進(jìn)行排序混亂，會(huì)出現(xiàn)錯(cuò)誤的結(jié)果
1 SELECT cookieid,
2 createtime,
3 url,
4 FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2
5 FROM bigdata_t4;
cume_dist,percent_rank 函數(shù)
這兩個(gè)序列分析函數(shù)不是很常用，注意：序列函數(shù)不支持WINDOW子句
數(shù)據(jù)準(zhǔn)備
1 d1,user1,1000
2 d1,user2,2000
3 d1,user3,3000
4 d2,user4,4000
5 d2,user5,5000
6
7 CREATE EXTERNAL TABLE bigdata_t3 (
8 dept STRING,
9 userid string,
10 sal INT
11 ) ROW FORMAT DELIMITED
12 FIELDS TERMINATED BY ','
13 stored as textfile;
14
15 加載數(shù)據(jù)：
16 load data local inpath '/root/hivedata/bigdata_t3.dat' into table bigdata_t3;
CUME_DIST 和order by的排序順序有關(guān)系
CUME_DIST 小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù) order 默認(rèn)順序 正序 升序
比如，統(tǒng)計(jì)小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例
1 SELECT
2 dept,
3 userid,
4 sal,
5 CUME_DIST() OVER(ORDER BY sal) AS rn1,
6 CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
7 FROM bigdata_t3;
8
9 rn1: 沒(méi)有partition,所有數(shù)據(jù)均為1組，總行數(shù)為5，
10 第一行：小于等于1000的行數(shù)為1，因此，1/5=0.2
11 第三行：小于等于3000的行數(shù)為3，因此，3/5=0.6
12 rn2: 按照部門分組，dpet=d1的行數(shù)為3,
13 第二行：小于等于2000的行數(shù)為2，因此，2/3=0.6666666666666666
PERCENT_RANK
PERCENT_RANK 分組內(nèi)當(dāng)前行的RANK值-1/分組內(nèi)總行數(shù)-1
1 SELECT
2 dept,
3 userid,
4 sal,
5 PERCENT_RANK() OVER(ORDER BY sal) AS rn1, --分組內(nèi)
6 RANK() OVER(ORDER BY sal) AS rn11, --分組內(nèi)RANK值
7 SUM(1) OVER(PARTITION BY NULL) AS rn12, --分組內(nèi)總行數(shù)
8 PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
9 FROM bigdata_t3;
10
11 rn1: rn1 = (rn11-1) / (rn12-1)
12 第一行,(1-1)/(5-1)=0/4=0
13 第二行,(2-1)/(5-1)=1/4=0.25
14 第四行,(4-1)/(5-1)=3/4=0.75
15 rn2: 按照dept分組，
16 dept=d1的總行數(shù)為3
17 第一行，(1-1)/(3-1)=0
18 第三行，(3-1)/(3-1)=1
grouping sets,grouping__id,cube,rollup 函數(shù)
這幾個(gè)分析函數(shù)通常用于OLAP中，不能累加，而且需要根據(jù)不同維度上鉆和下鉆的指標(biāo)統(tǒng)計(jì)，比如，分小時(shí)、天、月的UV數(shù)。
數(shù)據(jù)準(zhǔn)備
1 2018-03,2018-03-10,cookie1
2 2018-03,2018-03-10,cookie5
3 2018-03,2018-03-12,cookie7
4 2018-04,2018-04-12,cookie3
5 2018-04,2018-04-13,cookie2
6 2018-04,2018-04-13,cookie4
7 2018-04,2018-04-16,cookie4
8 2018-03,2018-03-10,cookie2
9 2018-03,2018-03-10,cookie3
10 2018-04,2018-04-12,cookie5
11 2018-04,2018-04-13,cookie6
12 2018-04,2018-04-15,cookie3
13 2018-04,2018-04-15,cookie2
14 2018-04,2018-04-16,cookie1
15
16 CREATE TABLE bigdata_t5 (
17 month STRING,
18 day STRING,
19 cookieid STRING
20 ) ROW FORMAT DELIMITED
21 FIELDS TERMINATED BY ','
22 stored as textfile;
23
24 加載數(shù)據(jù)：
25 load data local inpath '/root/hivedata/bigdata_t5.dat' into table bigdata_t5;
GROUPING SETS
grouping sets是一種將多個(gè)group by 邏輯寫在一個(gè)sql語(yǔ)句中的便利寫法。
等價(jià)于將不同維度的GROUP BY結(jié)果集進(jìn)行UNION ALL。
GROUPING__ID，表示結(jié)果屬于哪一個(gè)分組集合。
1 SELECT
2 month,
3 day,
4 COUNT(DISTINCT cookieid) AS uv,
5 GROUPING__ID
6 FROM bigdata_t5
7 GROUP BY month,day
8 GROUPING SETS (month,day)
9 ORDER BY GROUPING__ID;
10
11 grouping_id表示這一組結(jié)果屬于哪個(gè)分組集合，
12 根據(jù)grouping sets中的分組條件month，day，1是代表month，2是代表day
13
14 等價(jià)于
15 SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month UNION ALL
16 SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;
再如：
1 SELECT
2 month,
3 day,
4 COUNT(DISTINCT cookieid) AS uv,
5 GROUPING__ID
6 FROM bigdata_t5
7 GROUP BY month,day
8 GROUPING SETS (month,day,(month,day))
9 ORDER BY GROUPING__ID;
10
11 等價(jià)于
12 SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
13 UNION ALL
14 SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
15 UNION ALL
16 SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
CUBE
根據(jù)GROUP BY的維度的所有組合進(jìn)行聚合。
1 SELECT
2 month,
3 day,
4 COUNT(DISTINCT cookieid) AS uv,
5 GROUPING__ID
6 FROM bigdata_t5
7 GROUP BY month,day
8 WITH CUBE
9 ORDER BY GROUPING__ID;
10
11 等價(jià)于
12 SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5
13 UNION ALL
14 SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
15 UNION ALL
16 SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
17 UNION ALL
18 SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
ROLLUP
是CUBE的子集，以最左側(cè)的維度為主，從該維度進(jìn)行層級(jí)聚合。
1 比如，以month維度進(jìn)行層級(jí)聚合：
2 SELECT
3 month,
4 day,
5 COUNT(DISTINCT cookieid) AS uv,
6 GROUPING__ID
7 FROM bigdata_t5
8 GROUP BY month,day
9 WITH ROLLUP
10 ORDER BY GROUPING__ID;
11
12 --把month和day調(diào)換順序，則以day維度進(jìn)行層級(jí)聚合：
13
14 SELECT
15 day,
16 month,
17 COUNT(DISTINCT cookieid) AS uv,
18 GROUPING__ID
19 FROM bigdata_t5
20 GROUP BY day,month
21 WITH ROLLUP
22 ORDER BY GROUPING__ID;
23 （這里，根據(jù)天和月進(jìn)行聚合，和根據(jù)天聚合結(jié)果一樣，因?yàn)橛懈缸雨P(guān)系，如果是其他維度組合的話，就會(huì)不一樣）
