Hive窗口分析函數(shù)

1泻帮、概念

窗口分析函數(shù)：窗口函數(shù)也稱為OLAP（OnlineAnalytical Processing）函數(shù)，是對一組值進(jìn)行操作返十，不需要使用Group by子句對數(shù)據(jù)進(jìn)行分組，還能在同一行返回原來行的列和使用聚合函數(shù)得到的聚合列。
官網(wǎng)：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

2雹锣、sum, avg, max, min

數(shù)據(jù)準(zhǔn)備：cookie.txt

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

建表準(zhǔn)備相關(guān)：

create database if not exists myhive;
use myhive;
drop table if exists cookie;
create table cookie(cookieid string, createtime string, pv int) row format
delimited fields terminated by ',';
load data local inpath "/home/bigdata/cookie.txt" into table cookie;
select * from cookie;

sum

SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默認(rèn)為從起點(diǎn)到
當(dāng)前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS pv2, --從起點(diǎn)到當(dāng)前行，結(jié)果同pv1
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分組內(nèi)所有行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING
AND CURRENT ROW) AS pv4, --當(dāng)前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING
AND 1 FOLLOWING) AS pv5, --當(dāng)前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS pv6 --當(dāng)前行+往后所有行
FROM cookie order by cookieid, createtime;

結(jié)果：

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

解釋：

pv1: 分組內(nèi)從起點(diǎn)到當(dāng)前行的pv累積癞蚕，如蕊爵，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號
pv2: 同pv1
pv3: 分組內(nèi)(cookie1)所有的pv累加
pv4: 分組內(nèi)當(dāng)前行+往前3行，11號=10號+11號桦山， 12號=10號+11號+12號攒射， 13號=10號+11號+12號+13號， 14號=11號+12號+13號+14號
pv5: 分組內(nèi)當(dāng)前行+往前3行+往后1行恒水，如会放，14號=11號+12號+13號+14號+15號=5+7+3+2+4=21
pv6: 分組內(nèi)當(dāng)前行+往后所有行，如钉凌，13號=13號+14號+15號+16號=3+2+4+4=13咧最，14號=14號+15號+16號=2+4+4=10

擴(kuò)展：

如果不指定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
其他AVG，MIN闽坡，MAX栽惶，和SUM用法一樣。只需要把sum函數(shù)疾嗅，改成avg外厂，min，max宪迟，sum等就可以酣衷。

SELECT cookieid, createtime, pv,
round(AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime), 2) AS pv1, --默
認(rèn)為從起點(diǎn)到當(dāng)前行
round(AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS pv2, --從起點(diǎn)到當(dāng)前行，結(jié)果同pv1
round(AVG(pv) OVER(PARTITION BY cookieid), 2) AS pv3, --分組內(nèi)所有行
round(AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3
PRECEDING AND CURRENT ROW), 2) AS pv4, --當(dāng)前行+往前3行
round(AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3
PRECEDING AND 1 FOLLOWING), 2) AS pv5, --當(dāng)前行+往前3行+往后1行
round(AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING), 2) AS pv6 --當(dāng)前行+往后所有行
FROM cookie;

執(zhí)行結(jié)果：

cookie1 2015-04-16 4 3.71 3.71 3.71 3.25 3.25 4.0
cookie1 2015-04-15 4 3.67 3.67 3.71 4.0 4.0 4.0
cookie1 2015-04-14 2 3.6 3.6 3.71 4.25 4.2 3.33
cookie1 2015-04-13 3 4.0 4.0 3.71 4.0 3.6 3.25
cookie1 2015-04-12 7 4.33 4.33 3.71 4.33 4.0 4.0
cookie1 2015-04-11 5 3.0 3.0 3.71 3.0 4.33 4.17
cookie1 2015-04-10 1 1.0 1.0 3.71 1.0 3.0 3.71

3次泽、ntile, row_number, rank, dense_rank

準(zhǔn)備數(shù)據(jù)：cookie2.txt

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

建表導(dǎo)入數(shù)據(jù)相關(guān)操作：

create database if not exists myhive;
use myhive;
drop table if exists cookie2;
create table cookie2(cookieid string, createtime string, pv int) row format
delimited fields terminated by ',';
load data local inpath "/home/bigdata/cookie2.txt" into table cookie2;
select * from cookie2;

ntile

NTILE(n)穿仪，用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值
NTILE不支持ROWS BETWEEN意荤，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY
createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均勻啊片，默認(rèn)增加第一個切片的分布

SQL語句實例：

SELECT cookieid,createtime,pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1, --分組內(nèi)將數(shù)據(jù)
分成2片
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2, --分組內(nèi)將數(shù)據(jù)
分成3片
NTILE(4) OVER(ORDER BY createtime) AS rn3 --將所有數(shù)據(jù)分成4片
FROM cookie2
ORDER BY cookieid,createtime;

結(jié)果：

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

比如，統(tǒng)計一個cookie玖像，pv數(shù)最多的前1/3的天

SELECT cookieid, createtime, pv,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
FROM cookie2;

結(jié)果：

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

其中：rn = 1 的記錄紫谷，就是我們想要的結(jié)果
row_number
ROW_NUMBER() – 從1開始，按照順序，生成分組內(nèi)記錄的序列 –比如笤昨，按照pv降序排列祖驱，生成分組內(nèi)每天的pv名次，ROW_NUMBER() 的應(yīng)用場景非常多瞒窒，再比如捺僻，獲取分組內(nèi)排序第一的記錄;獲取一個session中的第一條refer等。
// 分組排序

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

結(jié)果：

cookie1 2015-04-12 7 1
cookie1 2015-04-11 5 2
cookie1 2015-04-16 4 3
cookie1 2015-04-15 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-11 3 5
cookie2 2015-04-14 3 6
cookie2 2015-04-10 2 7

所以如果需要取每一組的前3名崇裁，只需要rn<=3即可
rank 和 dense_rank
RANK() 生成數(shù)據(jù)項在分組中的排名匕坯，排名相等會在名次中留下空位
DENSE_RANK() 生成數(shù)據(jù)項在分組中的排名，排名相等會在名次中不會留下空位
SQL語句實例：

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 cookie2
WHERE cookieid = 'cookie1';

結(jié)果：

cookie1 2015-04-12 7 1 1 1
cookie1 2015-04-11 5 2 2 2
cookie1 2015-04-16 4 3 3 3
cookie1 2015-04-15 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

三者對比總結(jié)：

row_number 按順序編號拔稳，不留空位
rank 按順序編號葛峻，相同的值編相同號，留空位
dense_rank 按順序編號巴比，相同的值編相同的號术奖，不留空位

4、cume_dist, percent_rank

數(shù)據(jù)準(zhǔn)備：cookie3.txt

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

建表導(dǎo)入數(shù)據(jù)相關(guān)操作：

create database if not exists myhive;
use myhive;
drop table if exists cookie3;
create table cookie3(dept string, userid string, sal int) row format delimited
fields terminated by ',';
load data local inpath "/home/bigdata/cookie3.txt" into table cookie3;
select * from cookie3;

cume_dist
–CUME_DIST 小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)
比如匿辩，統(tǒng)計小于等于當(dāng)前薪水的人數(shù)腰耙，所占總?cè)藬?shù)的比例

SELECT dept, userid, sal,
round(CUME_DIST() OVER(ORDER BY sal), 2) AS rn1,
round(CUME_DIST() OVER(PARTITION BY dept ORDER BY sal), 2) AS rn2
FROM cookie3;

結(jié)果：

d1 user1 1000 0.2 0.33
d1 user2 2000 0.4 0.67
d1 user3 3000 0.6 1.0
d2 user4 4000 0.8 0.5
d2 user5 5000 1.0 1.0

SQL語句實例：

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

結(jié)果：

d1 user3 3000 0.6 0.33
d1 user2 2000 0.4 0.67
d1 user1 1000 0.2 1.0
d2 user5 5000 1.0 0.5
d2 user4 4000 0.8 1.0

percent_rank
PERCENT_RANK 分組內(nèi)當(dāng)前行的RANK值-1/分組內(nèi)總行數(shù)-1
SQL語句實例：

SELECT dept, userid, sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1, --分組內(nèi)
RANK() OVER(ORDER BY sal) AS rn11, --分組內(nèi)RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12, --分組內(nèi)總行數(shù)
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM cookie3;

結(jié)果：

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

SQL語句實例：

SELECT dept, userid, sal,
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn1, --分組內(nèi)
RANK() OVER(ORDER BY sal) AS rn11, --分組內(nèi)RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12, --分組內(nèi)總行數(shù)
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM cookie3;

執(zhí)行結(jié)果：

d2 user5 5000 1.0 5 5 1.0
d2 user4 4000 0.0 4 5 0.0
d1 user3 3000 1.0 3 5 1.0
d1 user2 2000 0.5 2 5 0.5
d1 user1 1000 0.0 1 5 0.0

5、lag, lead, frist_value, last_value

數(shù)據(jù)準(zhǔn)備：cookie4.txt

cookie1,2015-04-10 10:00:02,url2
cookie1,2015-04-10 10:00:00,url1
cookie1,2015-04-10 10:03:04,1url3
cookie1,2015-04-10 10:50:05,url6
cookie1,2015-04-10 11:00:00,url7
cookie1,2015-04-10 10:10:00,url4
cookie1,2015-04-10 10:50:01,url5
cookie2,2015-04-10 10:00:02,url22
cookie2,2015-04-10 10:00:00,url11
cookie2,2015-04-10 10:03:04,1url33
cookie2,2015-04-10 10:50:05,url66
cookie2,2015-04-10 11:00:00,url77
cookie2,2015-04-10 10:10:00,url44
cookie2,2015-04-10 10:50:01,url55

建表導(dǎo)入數(shù)據(jù)相關(guān)操作：

create database if not exists myhive;
use myhive;
drop table if exists cookie4;
create table cookie4(cookieid string, createtime string, url string) row format
delimited fields terminated by ',';
load data local inpath "/home/bigdata/cookie4.txt" into table cookie4;
select * from cookie4;

lag
LAG(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往上第n行值
第一個參數(shù)為列名铲球，
第二個參數(shù)為往上第n行（可選挺庞，默認(rèn)為1），
第三個參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時候稼病，取默認(rèn)值选侨，如不指定，則為NULL）
SQL語句實例：

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

結(jié)果數(shù)據(jù)：

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,因此取默認(rèn)值 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行的值拆檬，為指定默認(rèn)值
cookie1第一行洪己，往上2行為NULL
cookie1第二行，往上2行為NULL
cookie1第四行，往上2行為第二行值，2015-04-10 10:00:02
cookie1第七行总处，往上2行為第五行值，2015-04-10 10:50:01

lead
與LAG相反
LEAD(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往下第n行值
第一個參數(shù)為列名昔逗，
第二個參數(shù)為往下第n行（可選关霸，默認(rèn)為1）梨熙，
第三個參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時候突想，取默認(rèn)值沃琅，如不指定哗咆，則為NULL）
SQL語句實例：

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

結(jié)果：

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

first_value
取分組內(nèi)排序后，截止到當(dāng)前行阵难，第一個值
SQL語句實例：

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

結(jié)果：

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
取分組內(nèi)排序后岳枷，截止到當(dāng)前行芒填，最后一個值
SQL語句實例:

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

結(jié)果數(shù)據(jù)：

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

問題：如果不指定ORDER BY呜叫，則默認(rèn)按照記錄在文件中的偏移量進(jìn)行排序，會出現(xiàn)錯誤的結(jié)果
// 求得每組的最后一個值：排倒序殿衰，然后取FIRST_VALUE
SQL語句：

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 cookie4
ORDER BY cookieid,createtime;

結(jié)果：

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

6朱庆、 grouping sets, grouping__id, cube,rollup

這幾個分析函數(shù)通常用于OLAP中，不能累加闷祥，而且需要根據(jù)不同維度上鉆和下鉆的指標(biāo)統(tǒng)計娱颊，比如，分小時凯砍、天箱硕、月的UV數(shù)
官網(wǎng)介紹：https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2
C+Grouping+and+Rollup
數(shù)據(jù)準(zhǔn)備：cookie5.txt

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

建表導(dǎo)入數(shù)據(jù)相關(guān)：

create database if not exists myhive;
use myhive;
drop table if exists cookie5;
create table cookie5(month string, day string, cookieid string) row format
delimited fields terminated by ',';
load data local inpath "/home/bigdata/cookie5.txt" into table cookie5;
select * from cookie5;

grouping sets
在一個GROUP BY查詢中，根據(jù)不同的維度組合進(jìn)行聚合悟衩，等價于將不同維度的GROUP BY結(jié)果集進(jìn)行UNION ALL
SQL語句實例：

SELECT month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie5
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;

其中的 GROUPING__ID剧罩，表示結(jié)果屬于哪一個分組集合。
結(jié)果：

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

其實這個SQL語句等價于下面這個SQL：

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5
GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5
GROUP BY day;

執(zhí)行結(jié)果：

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 NULL 5 1
2015-04 NULL 6 1

SQL語句：

SELECT month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie5
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;

其中的 GROUPING__ID座泳，表示結(jié)果屬于哪一個分組集合惠昔。
結(jié)果數(shù)據(jù)：

2015-03 2015-03-10 4 0
2015-04 2015-04-16 2 0
2015-04 2015-04-13 3 0
2015-04 2015-04-12 2 0
2015-04 2015-04-15 2 0
2015-03 2015-03-12 1 0
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-04-16 2 2
NULL 2015-04-15 2 2
NULL 2015-04-13 3 2
NULL 2015-04-12 2 2
NULL 2015-03-12 1 2
NULL 2015-03-10 4 2

等價于：

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5
GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5
GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5
GROUP BY month,day;

cube
根據(jù)GROUP BY的維度的所有組合進(jìn)行聚合
SQL語句：

SELECT month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie5
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;

結(jié)果：

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

等價于：

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM cookie5
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5
GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5
GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5
GROUP BY month,day;

rollup
是CUBE的子集，以最左側(cè)的維度為主挑势，從該維度進(jìn)行層級聚合
比如镇防，以month維度進(jìn)行層級聚合，SQL語句：

SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID
FROM cookie5
GROUP BY month,day WITH ROLLUP ORDER BY GROUPING__ID;

可以實現(xiàn)這樣的上鉆過程：月天的UV->月的UV->總UV
結(jié)果：

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

把month和day調(diào)換順序潮饱，則以day維度進(jìn)行層級聚合：SQL語句：

SELECT day, month, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID
FROM cookie5
GROUP BY day,month WITH ROLLUP ORDER BY GROUPING__ID;

可以實現(xiàn)這樣的上鉆過程：天月的UV->天的UV->總UV
這里来氧，根據(jù)天和月進(jìn)行聚合，和根據(jù)天聚合結(jié)果一樣香拉，因為有父子關(guān)系啦扬，如果是其他維度組合的話，就會不一樣

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末缕溉，一起剝皮案震驚了整個濱河市考传，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌证鸥，老刑警劉巖僚楞，帶你破解...
沈念sama閱讀 206,482評論 6贊 481
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件勤晚，死亡現(xiàn)場離奇詭異，居然都是意外死亡泉褐，警方通過查閱死者的電腦和手機(jī)赐写，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,377評論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來膜赃，“玉大人挺邀，你說我怎么就攤上這事√” “怎么了端铛？”我有些...
開封第一講書人閱讀 152,762評論 0贊 342
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長疲眷。經(jīng)常有香客問我禾蚕，道長，這世上最難降的妖魔是什么狂丝？我笑而不...
開封第一講書人閱讀 55,273評論 1贊 279
?港島之戀（遺憾婚禮）
正文為了忘掉前任换淆，我火速辦了婚禮，結(jié)果婚禮上几颜，老公的妹妹穿的比我還像新娘倍试。我一直安慰自己，他們只是感情好蛋哭，可當(dāng)我...
茶點(diǎn)故事閱讀 64,289評論 5贊 373
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布县习。她就那樣靜靜地躺著，像睡著了一般具壮。火紅的嫁衣襯著肌膚如雪准颓。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,046評論 1贊 285
城市分裂傳說
那天棺妓，我揣著相機(jī)與錄音攘已，去河邊找鬼。笑死怜跑，一個胖子當(dāng)著我的面吹牛样勃，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播性芬，決...
沈念sama閱讀 38,351評論 3贊 400
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼峡眶，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了植锉？” 一聲冷哼從身側(cè)響起辫樱，我...
開封第一講書人閱讀 36,988評論 0贊 259
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎俊庇，沒想到半個月后狮暑，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體鸡挠，經(jīng)...
沈念sama閱讀 43,476評論 1贊 300
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 35,948評論 2贊 324
?白月光啟示錄
正文我和宋清朗相戀三年搬男，在試婚紗的時候發(fā)現(xiàn)自己被綠了拣展。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 38,064評論 1贊 333
活死人
序言：一個原本活蹦亂跳的男人離奇死亡缔逛，死狀恐怖备埃，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情褐奴，我是刑警寧澤按脚，帶...
沈念sama閱讀 33,712評論 4贊 323
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站歉糜，受9級特大地震影響乘寒，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜匪补，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,261評論 3贊 307
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望烂翰。院中可真熱鬧夯缺，春花似錦、人聲如沸甘耿。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,264評論 0贊 19
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽佳恬。三九已至捏境，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間毁葱，已是汗流浹背垫言。一陣腳步聲響...
開封第一講書人閱讀 31,486評論 1贊 262
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留倾剿，地道東北人筷频。一個月前我還...
沈念sama閱讀 45,511評論 2贊 354
代替公主和親
正文我出身青樓，卻偏偏與公主長得像前痘，于是被迫代替她去往敵國和親凛捏。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 42,802評論 2贊 345

Hive窗口分析函數(shù)

1泻帮、概念

2雹锣、sum, avg, max, min

3次泽、ntile, row_number, rank, dense_rank

4、cume_dist, percent_rank

5、lag, lead, frist_value, last_value

6朱庆、 grouping sets, grouping__id, cube,rollup

推薦閱讀更多精彩內(nèi)容