使用explode函數(shù)將hive表中的Map和Array字段數(shù)據(jù)進(jìn)行拆分
lateral view用于和split、explode等UDTF一起使用的剔氏，能將一行數(shù)據(jù)拆分成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分的數(shù)據(jù)進(jìn)行聚合积仗，lateral view首先為原始表的每行調(diào)用UDTF疆拘，UDTF會把一行拆分成一行或者多行蜕猫，lateral view在把結(jié)果組合，產(chǎn)生一個支持別名表的虛擬表哎迄。
其中explode還可以用于將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行
需求：現(xiàn)在有數(shù)據(jù)格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
字段之間使用\t分割回右，需求將所有的child進(jìn)行拆開成為一列
+----------+--+
| mychild |
+----------+--+
| child1 |
| child2 |
| child3 |
| child4 |
| child5 |
| child6 |
| child7 |
| child8 |
+----------+--+
將map的key和value也進(jìn)行拆開隆圆，成為如下結(jié)果
+-----------+-------------+--+
| mymapkey | mymapvalue |
+-----------+-------------+--+
| k1 | v1 |
| k2 | v2 |
| k3 | v3 |
| k4 | v4 |
+-----------+-------------+--+
- 創(chuàng)建hive數(shù)據(jù)庫
創(chuàng)建hive數(shù)據(jù)庫
hive (default)> create database hive_explode;
hive (default)> use hive_explode;
- 創(chuàng)建hive表，然后使用explode拆分map和array
hive (hive_explode)> create table t3(
name string,
children array<string>,
address Map<string,string>
) row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
stored as textFile;
- 加載數(shù)據(jù)
node03執(zhí)行以下命令創(chuàng)建表數(shù)據(jù)文件
mkdir -p /export/servers/hivedatas/
cd /export/servers/hivedatas/
vim maparray
內(nèi)容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive表當(dāng)中加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;
- 使用explode將hive當(dāng)中數(shù)據(jù)拆開
將array當(dāng)中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;
將map當(dāng)中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;
使用explode拆分json字符串
需求: 需求：現(xiàn)在有一些數(shù)據(jù)格式如下：
a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
其中字段與字段之間的分隔符是 |
我們要解析得到所有的monthSales對應(yīng)的值為以下這一列（行轉(zhuǎn)列）
4900
2090
6987
- 創(chuàng)建hive表
hive (hive_explode)> create table explode_lateral_view
> (`area` string,
> `goods_id` string,
> `sale_info` string)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '|'
> STORED AS textfile;
- 準(zhǔn)備數(shù)據(jù)并加載數(shù)據(jù)
準(zhǔn)備數(shù)據(jù)如下
cd /export/servers/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
加載數(shù)據(jù)到hive表當(dāng)中去
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json'
> overwrite into table explode_lateral_view;
- 使用explode拆分Array
hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;
- 使用explode拆解Map
hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;
- 拆解json字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,
> '\\[\\{',''),'}]',''),'},\\{')) as sale_info from explode_lateral_view;
然后我們想用get_json_object來獲取key為monthSales的數(shù)據(jù)：
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,
> '\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as sale_info from explode_lateral_view;
然后掛了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能寫在別的函數(shù)內(nèi)
如果你這么寫翔烁，想查兩個字段渺氧，select explode(split(area,',')) as area,good_id from explode_lateral_view;
會報錯FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's.
Error encountered near token 'good_id'
使用UDTF的時候，只支持一個字段蹬屹，這時候就需要LATERAL VIEW出場了
配合LATERAL VIEW使用
配合lateral view查詢多個字段
select
goods_id2,sale_info
from
explode_lateral_view LATERAL VIEW explode(split(goods_id,',')) goods as goods_id2;
其中LATERAL VIEW explode(split(goods_id,','))goods相當(dāng)于一個虛擬表侣背，與原表explode_lateral_view笛卡爾積關(guān)聯(lián)
也可以多重使用
hive (hive_explode)> select goods_id2,sale_info,area2
from explode_lateral_view
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2
LATERAL VIEW explode(split(area,','))area as area2;也是三個表笛卡爾積的結(jié)果
最終，我們可以通過下面的句子慨默，把這個json格式的一行數(shù)據(jù)贩耐，完全轉(zhuǎn)換成二維表的方式展現(xiàn)
select
get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
from
explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,
'\\[\\{',''),'}]',''),'},\\{')) sale_info as sale_info_1;
總結(jié)：
Lateral View通常和UDTF一起出現(xiàn)，為了解決UDTF不允許在select字段的問題厦取。Multiple Lateral View可以實現(xiàn)類似笛卡爾乘積潮太。Outer關(guān)鍵字可以把不輸出的UDTF的空結(jié)果，輸出成NULL虾攻，防止丟失數(shù)據(jù)铡买。
行轉(zhuǎn)列
相關(guān)參數(shù)說明:
CONCAT(string A/col, string B/col…)：返回輸入字符串連接后的結(jié)果，支持任意個輸入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一個特殊形式的 CONCAT()霎箍。第一個參數(shù)剩余參數(shù)間的分隔符奇钞。分隔符可以是與剩余參數(shù)一樣的字符串。如果分隔符是 NULL漂坏，返回值也將為 NULL蛇券。這個函數(shù)會跳過分隔符參數(shù)后的任何 NULL 和空字符串。分隔符將被加到被連接的字符串之間;
COLLECT_SET(col)：函數(shù)只接受基本數(shù)據(jù)類型樊拓，它的主要作用是將某字段的值進(jìn)行去重匯總纠亚，產(chǎn)生array類型字段。
數(shù)據(jù)準(zhǔn)備:
| name | constellation | blood_type |
|---|---|---|
| 孫悟空 | 白羊座 | A |
| 老王 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 豬八戒 | 白羊座 | A |
| 鳳姐 | 射手座 | A |
需求: 把星座和血型一樣的人歸類到一起筋夏。結(jié)果如下：
射手座,A 老王|鳳姐
白羊座,A 孫悟空|豬八戒
白羊座,B 宋宋
實現(xiàn)步驟:
- 創(chuàng)建本地constellation.txt蒂胞，導(dǎo)入數(shù)據(jù)
node03服務(wù)器執(zhí)行以下命令創(chuàng)建文件，注意數(shù)據(jù)使用\t進(jìn)行分割
cd /export/servers/hivedatas
vim constellation.txt
數(shù)據(jù)如下:
孫悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
豬八戒 白羊座 A
鳳姐 射手座 A
- 創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)
創(chuàng)建hive表并加載數(shù)據(jù)
hive (hive_explode)> create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/constellation.txt' into table person_info
- 按需求查詢數(shù)據(jù)
hive (hive_explode)> select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, "," , blood_type) base
from
person_info) t1
group by
t1.base;
列轉(zhuǎn)行
所需函數(shù):
EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行条篷。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用于和split, explode等UDTF一起使用骗随，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進(jìn)行聚合赴叹。
數(shù)據(jù)準(zhǔn)備:
cd /export/servers/hivedatas
vim movie.txt
文件內(nèi)容如下: 數(shù)據(jù)字段之間使用\t進(jìn)行分割
《疑犯追蹤》 懸疑,動作,科幻,劇情
《Lie to me》 懸疑,警匪,動作,心理,劇情
《戰(zhàn)狼2》 戰(zhàn)爭,動作,災(zāi)難
需求: 將電影分類中的數(shù)組數(shù)據(jù)展開鸿染。結(jié)果如下：
《疑犯追蹤》 懸疑
《疑犯追蹤》 動作
《疑犯追蹤》 科幻
《疑犯追蹤》 劇情
《Lie to me》 懸疑
《Lie to me》 警匪
《Lie to me》 動作
《Lie to me》 心理
《Lie to me》 劇情
《戰(zhàn)狼2》 戰(zhàn)爭
《戰(zhàn)狼2》 動作
《戰(zhàn)狼2》 災(zāi)難
實現(xiàn)步驟:
- 創(chuàng)建hive表
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
- 加載數(shù)據(jù)
load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;
- 按需求查詢數(shù)據(jù)
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
reflect函數(shù)
reflect函數(shù)可以支持在sql中調(diào)用java中的自帶函數(shù)，秒殺一切udf函數(shù)乞巧。
需求1: 使用java.lang.Math當(dāng)中的Max求兩列中最大值
實現(xiàn)步驟:
- 創(chuàng)建hive表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';
- 準(zhǔn)備數(shù)據(jù)并加載數(shù)據(jù)
cd /export/servers/hivedatas
vim test_udf
文件內(nèi)容如下:
1,2
4,3
6,4
7,5
5,6
- 加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;
- 使用java.lang.Math當(dāng)中的Max求兩列當(dāng)中的最大值
hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;
需求2: 文件中不同的記錄來執(zhí)行不同的java的內(nèi)置函數(shù)
實現(xiàn)步驟:
- 創(chuàng)建hive表
hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';
- 準(zhǔn)備數(shù)據(jù)
cd /export/servers/hivedatas
vim test_udf2
文件內(nèi)容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3
- 加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;
- 執(zhí)行查詢
hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;
需求3: 判斷是否為數(shù)字
實現(xiàn)方式:
使用apache commons中的函數(shù)涨椒，commons下的jar已經(jīng)包含在hadoop的classpath中，所以可以直接使用。
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")
Hive 窗口函數(shù)
窗口函數(shù)最重要的關(guān)鍵字是 partition by 和 order by
具體語法如下：XXX over (partition by xxx order by xxx)
特別注意：over()里面的 partition by 和 order by 都不是必選的蚕冬，over()里面可以只有partition by免猾，也可以只有order by，也可以兩個都沒有囤热，大家需根據(jù)需求靈活運用猎提。
窗口函數(shù)我劃分了幾個大類，我們一類一類的講解旁蔼。
1. SUM锨苏、AVG、MIN蚓炬、MAX
講解這幾個窗口函數(shù)前躺屁，先創(chuàng)建一個表，以實際例子講解大家更容易理解驯击。
首先創(chuàng)建用戶訪問頁面表：user_pv
create table user_pv(
cookieid string, -- 用戶登錄的cookie耐亏，即用戶標(biāo)識
createtime string, -- 日期
pv int -- 頁面訪問量
);
給上面這個表加上如下數(shù)據(jù)：
cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
- SUM()使用
執(zhí)行如下查詢語句：
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from user_pv;
執(zhí)行如下查詢語句：
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid ) as pv1
from user_pv;
第一條SQL的over()里面加 order by ，第二條SQL沒加order by 择吊，結(jié)果差別很大
所以要注意了：
over()里面加 order by 表示：分組內(nèi)從起點到當(dāng)前行的pv累積李根，如，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號几睛；
over()里面不加 order by 表示：將分組內(nèi)所有值累加房轿。
AVG，MIN所森，MAX囱持，和SUM用法一樣，這里就不展開講了焕济，但是要注意 AVG纷妆，MIN，MAX 的over()里面加不加 order by 也和SUM一樣晴弃，如 AVG 求平均值掩幢，如果加上 order by逊拍，表示分組內(nèi)從起點到當(dāng)前行的平局值，不是全部的平局值粒蜈。MIN，MAX 同理旗国。
2. ROW_NUMBER枯怖、RANK、DENSE_RANK度硝、NTILE
還是用上述的用戶登錄日志表：user_pv蕊程，里面的數(shù)據(jù)換成如下所示：
cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
cookie2,2021-05-10,2
cookie2,2021-05-11,3
cookie2,2021-05-12,5
cookie2,2021-05-13,6
cookie2,2021-05-14,3
cookie2,2021-05-15,9
cookie2,2021-05-16,7
- ROW_NUMBER()使用：
ROW_NUMBER()從1開始，按照順序辨赐，生成分組內(nèi)記錄的序列。
SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM user_pv;
- RANK 和 DENSE_RANK 使用：
RANK() 生成數(shù)據(jù)項在分組中的排名不恭，排名相等會在名次中留下空位。
DENSE_RANK()生成數(shù)據(jù)項在分組中的排名式散，排名相等會在名次中不會留下空位暴拄。
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 user_pv
WHERE cookieid = 'cookie1';
- NTILE的使用：
有時會有這樣的需求:如果數(shù)據(jù)排序后分為三部分透且，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢?NTILE函數(shù)即可以滿足琳骡。
ntile可以看成是：把有序的數(shù)據(jù)集合平均分配到指定的數(shù)量（num）個桶中, 將桶號分配給每一行。如果不能平均分配炫狱，則優(yōu)先分配較小編號的桶，并且各個桶中能放的行數(shù)最多相差1酷含。
然后可以根據(jù)桶號第美，選取前或后 n分之幾的數(shù)據(jù)。數(shù)據(jù)會完整展示出來别威，只是給相應(yīng)的數(shù)據(jù)打標(biāo)簽；具體要取幾分之幾的數(shù)據(jù)豺妓，需要再嵌套一層根據(jù)標(biāo)簽取出琳拭。
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 user_pv
ORDER BY cookieid,createtime;
3. LAG、LEAD鲁沥、FIRST_VALUE画恰、LAST_VALUE
講解這幾個窗口函數(shù)時還是以實例講解，首先創(chuàng)建用戶訪問頁面表：user_url
CREATE TABLE user_url (
cookieid string,
createtime string, --頁面訪問時間
url string --被訪問頁面
);
表中加入如下數(shù)據(jù)：
cookie1,2021-06-10 10:00:02,url2
cookie1,2021-06-10 10:00:00,url1
cookie1,2021-06-10 10:03:04,1url3
cookie1,2021-06-10 10:50:05,url6
cookie1,2021-06-10 11:00:00,url7
cookie1,2021-06-10 10:10:00,url4
cookie1,2021-06-10 10:50:01,url5
cookie2,2021-06-10 10:00:02,url22
cookie2,2021-06-10 10:00:00,url11
cookie2,2021-06-10 10:03:04,1url33
cookie2,2021-06-10 10:50:05,url66
cookie2,2021-06-10 11:00:00,url77
cookie2,2021-06-10 10:10:00,url44
cookie2,2021-06-10 10:50:01,url55
- 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）
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 user_url;
解釋：
last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'
cookie1第一行谜洽，往上1行為NULL,因此取默認(rèn)值 1970-01-01 00:00:00
cookie1第三行，往上1行值為第二行值敌呈，2021-06-10 10:00:02
cookie1第六行磕洪，往上1行值為第五行值鲫咽，2021-06-10 10:50:01
last_2_time: 指定了往上第2行的值分尸，為指定默認(rèn)值
cookie1第一行，往上2行為NULL
cookie1第二行材蛛，往上2行為NULL
cookie1第四行卑吭，往上2行為第二行值，2021-06-10 10:00:02
cookie1第七行掷邦，往上2行為第五行值，2021-06-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）
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 user_url;
- FIRST_VALUE的使用：
取分組內(nèi)排序后，截止到當(dāng)前行疆偿，第一個值。
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 user_url;
- LAST_VALUE的使用：
取分組內(nèi)排序后溉愁，截止到當(dāng)前行，最后一個值堂污。
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 user_url
如果想要取分組內(nèi)排序后最后一個值，則需要變通一下：
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 user_url
ORDER BY cookieid,createtime;
注意上述SQL式镐，使用的是 FIRST_VALUE 的倒序取出分組內(nèi)排序最后一個值娘汞！
結(jié)果如下：此處要特別注意order by
如果不指定ORDER BY惊豺，則進(jìn)行排序混亂扮叨，會出現(xiàn)錯誤的結(jié)果
SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2
FROM user_url;
上述 url2 和 url55 的createtime即不屬于最靠前的時間也不屬于最靠后的時間，所以結(jié)果是混亂的衷蜓。
4. CUME_DIST
先創(chuàng)建一張員工薪水表：staff_salary
CREATE EXTERNAL TABLE staff_salary (
dept string,
userid string,
sal int
);
表中加入如下數(shù)據(jù)：
d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
- CUME_DIST的使用：
此函數(shù)的結(jié)果和order by的排序順序有關(guān)系。
CUME_DIST：小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)置吓。order默認(rèn)順序 ：正序
比如衍锚，統(tǒng)計小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例告匠。
SELECT
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM staff_salary;
解釋：
rn1: 沒有partition,所有數(shù)據(jù)均為1組，總行數(shù)為5戚哎，
第一行：小于等于1000的行數(shù)為1建瘫，因此啰脚，1/5=0.2
第三行：小于等于3000的行數(shù)為3粒梦，因此匀们，3/5=0.6
rn2: 按照部門分組，dpet=d1的行數(shù)為3,
第二行：小于等于2000的行數(shù)為2祖灰，因此局扶，2/3=0.6666666666666666
5. GROUPING SETS、GROUPING__ID畴蒲、CUBE狞玛、ROLLUP
這幾個分析函數(shù)通常用于OLAP中锭亏，不能累加慧瘤，而且需要根據(jù)不同維度上鉆和下鉆的指標(biāo)統(tǒng)計锅减，比如，分小時每瞒、天剿骨、月的UV數(shù)挤庇。
還是先創(chuàng)建一個用戶訪問表：user_date
CREATE TABLE user_date (
month STRING,
day STRING,
cookieid STRING
);
表中加入如下數(shù)據(jù)：
2021-03,2021-03-10,cookie1
2021-03,2021-03-10,cookie5
2021-03,2021-03-12,cookie7
2021-04,2021-04-12,cookie3
2021-04,2021-04-13,cookie2
2021-04,2021-04-13,cookie4
2021-04,2021-04-16,cookie4
2021-03,2021-03-10,cookie2
2021-03,2021-03-10,cookie3
2021-04,2021-04-12,cookie5
2021-04,2021-04-13,cookie6
2021-04,2021-04-15,cookie3
2021-04,2021-04-15,cookie2
2021-04,2021-04-16,cookie1
- GROUPING SETS的使用：
grouping sets是一種將多個group by 邏輯寫在一個sql語句中的便利寫法。
等價于將不同維度的GROUP BY結(jié)果集進(jìn)行UNION ALL淘菩。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM user_date
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
注：上述SQL中的GROUPING__ID，是個關(guān)鍵字汇在，表示結(jié)果屬于哪一個分組集合，根據(jù)grouping sets中的分組條件month阿蝶，day羡洁，1是代表month，2是代表day爽丹。
結(jié)果如下：上述SQL等價于：
SELECT month,
NULL as day,
COUNT(DISTINCT cookieid) AS uv,
1 AS GROUPING__ID
FROM user_date
GROUP BY month
UNION ALL
SELECT NULL as month,
day,
COUNT(DISTINCT cookieid) AS uv,
2 AS GROUPING__ID
FROM user_date
GROUP BY day;
- CUBE的使用：
根據(jù)GROUP BY的維度的所有組合進(jìn)行聚合筑煮。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM user_date
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
上述SQL等價于：
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM user_date
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM user_date GROUP BY month,day;
- ROLLUP的使用：
是CUBE的子集，以最左側(cè)的維度為主粤蝎，從該維度進(jìn)行層級聚合真仲。
比如，以month維度進(jìn)行層級聚合：
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM user_date
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
把month和day調(diào)換順序初澎，則以day維度進(jìn)行層級聚合：
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM user_date
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
這里霉囚，根據(jù)日和月進(jìn)行聚合，和根據(jù)日聚合結(jié)果一樣，因為有父子關(guān)系番刊，如果是其他維度組合的話，就會不一樣磁滚。
