hive窗口函數/分析函數詳細剖析
hive窗口函數/分析函數
在sql中有一類函數叫做聚合函數,例如sum()、avg()、max()等等,這類函數可以將多行數據按照規則聚集為一行,一般來講聚集後的行數是要少於聚集前的行數的。但是有時我們想要既顯示聚集前的數據,又要顯示聚集後的數據,這時我們便引入了窗口函數。窗口函數又叫OLAP函數/分析函數,窗口函數兼具分組和排序功能。
窗口函數最重要的關鍵字是 partition by 和 order by。
具體語法如下:over (partition by xxx order by xxx)
sum,avg,min,max 函數
準備數據
建表語句:
create table bigdata_t1(
cookieid string,
createtime string, --day
pv int
) row format delimited
fields terminated by ',';
載入數據:
load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_t1;
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
開啟智慧本地模式
SET hive.exec.mode.local.auto=true;
SUM函數和窗口函數的配合使用:結果和ORDER BY相關,默認為升序。
#pv1
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from bigdata_t1;
#pv2
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from bigdata_t1;
#pv3
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from bigdata_t1;
#pv4
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from bigdata_t1;
#pv5
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from bigdata_t1;
#pv6
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from bigdata_t1;
pv1: 分組內從起點到當前行的pv累積,如,11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號
pv2: 同pv1
pv3: 分組內(cookie1)所有的pv累加
pv4: 分組內當前行+往前3行,如,11號=10號+11號, 12號=10號+11號+12號,
13號=10號+11號+12號+13號, 14號=11號+12號+13號+14號
pv5: 分組內當前行+往前3行+往後1行,如,14號=11號+12號+13號+14號+15號=5+7+3+2+4=21
pv6: 分組內當前行+往後所有行,如,13號=13號+14號+15號+16號=3+2+4+4=13,
14號=14號+15號+16號=2+4+4=10
如果不指定rows between,默認為從起點到當前行;
如果不指定order by,則將分組內所有值累加;
關鍵是理解rows between含義,也叫做window子句:
preceding:往前
following:往後
current row:當前行
unbounded:起點
unbounded preceding 表示從前面的起點
unbounded following:表示到後面的終點
AVG,MIN,MAX,和SUM用法一樣。
row_number,rank,dense_rank,ntile 函數
準備數據
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,2
cookie2,2018-04-11,3
cookie2,2018-04-12,5
cookie2,2018-04-13,6
cookie2,2018-04-14,3
cookie2,2018-04-15,9
cookie2,2018-04-16,7
CREATE TABLE bigdata_t2 (
cookieid string,
createtime string, --day
pv INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;
載入數據:
load data local inpath '/root/hivedata/bigdata_t2.dat' into table bigdata_t2;
-
ROW_NUMBER()使用
ROW_NUMBER()從1開始,按照順序,生成分組內記錄的序列。
SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM bigdata_t2;
-
RANK 和 DENSE_RANK使用
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 bigdata_t2
WHERE cookieid = 'cookie1';
-
NTILE
有時會有這樣的需求:如果數據排序後分為三部分,業務人員只關心其中的一部分,如何將這中間的三分之一數據拿出來呢?NTILE函數即可以滿足。
ntile可以看成是:把有序的數據集合平均分配到指定的數量(num)個桶中, 將桶號分配給每一行。如果不能平均分配,則優先分配較小編號的桶,並且各個桶中能放的行數最多相差1。
然後可以根據桶號,選取前或後 n分之幾的數據。數據會完整展示出來,只是給相應的數據打標籤;具體要取幾分之幾的數據,需要再嵌套一層根據標籤取出。
SELECT
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM bigdata_t2
ORDER BY cookieid,createtime;
其他一些窗口函數
lag,lead,first_value,last_value 函數
- LAG
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 bigdata_t4;
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
與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 bigdata_t4;
-
FIRST_VALUE
取分組內排序後,截止到當前行,第一個值
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 bigdata_t4;
-
LAST_VALUE
取分組內排序後,截止到當前行,最後一個值
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 bigdata_t4;
如果想要取分組內排序後最後一個值,則需要變通一下:
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 bigdata_t4
ORDER BY cookieid,createtime;
特別注意order by
如果不指定ORDER BY,則進行排序混亂,會出現錯誤的結果
SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2
FROM bigdata_t4;
cume_dist,percent_rank 函數
這兩個序列分析函數不是很常用,注意: 序列函數不支援WINDOW子句
- 數據準備
d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
CREATE EXTERNAL TABLE bigdata_t3 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;
載入數據:
load data local inpath '/root/hivedata/bigdata_t3.dat' into table bigdata_t3;
-
CUME_DIST 和order by的排序順序有關係
CUME_DIST 小於等於當前值的行數/分組內總行數 order 默認順序 正序 升序
比如,統計小於等於當前薪水的人數,所佔總人數的比例
SELECT
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM bigdata_t3;
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
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 bigdata_t3;
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數。
- 數據準備
2018-03,2018-03-10,cookie1
2018-03,2018-03-10,cookie5
2018-03,2018-03-12,cookie7
2018-04,2018-04-12,cookie3
2018-04,2018-04-13,cookie2
2018-04,2018-04-13,cookie4
2018-04,2018-04-16,cookie4
2018-03,2018-03-10,cookie2
2018-03,2018-03-10,cookie3
2018-04,2018-04-12,cookie5
2018-04,2018-04-13,cookie6
2018-04,2018-04-15,cookie3
2018-04,2018-04-15,cookie2
2018-04,2018-04-16,cookie1
CREATE TABLE bigdata_t5 (
month STRING,
day STRING,
cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile;
載入數據:
load data local inpath '/root/hivedata/bigdata_t5.dat' into table bigdata_t5;
-
GROUPING SETS
grouping sets是一種將多個group by 邏輯寫在一個sql語句中的便利寫法。
等價於將不同維度的GROUP BY結果集進行UNION ALL。
GROUPING__ID,表示結果屬於哪一個分組集合。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
grouping_id表示這一組結果屬於哪個分組集合,
根據grouping sets中的分組條件month,day,1是代表month,2是代表day
等價於
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;
再如:
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;
等價於
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
-
CUBE
根據GROUP BY的維度的所有組合進行聚合。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
等價於
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
-
ROLLUP
是CUBE的子集,以最左側的維度為主,從該維度進行層級聚合。
比如,以month維度進行層級聚合:
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM bigdata_t5
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
--把month和day調換順序,則以day維度進行層級聚合:
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM bigdata_t5
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
(這裡,根據天和月進行聚合,和根據天聚合結果一樣,因為有父子關係,如果是其他維度組合的話,就會不一樣)