查找企業(yè)信息的網站青島網站制作推廣
目錄
1、什么是增強聚合和多維分析函數(shù)？
2、grouping sets - 指定維度組合
3、with rollup - 上卷維度組合
4、with cube - 全維度組合
5、Grouping__ID、grouping()?的使用場景
6、使用 增強聚合 會不會對查詢性能有提升呢？
7、對grouping sets、with cube、with rollup 的優(yōu)化
1、什么是增強聚合和多維分析函數(shù)？
? ? ? ? 增強聚合指的是：
??????????????? 在SQL中使用分組聚合查詢時，使用?grouping sets、rollup、cube 語句進行操作
? ? ? ? ? ? ? ? 在常見的數(shù)據(jù)引擎中都支持這種語法，比如hive、spark、presto、ck、flinkSQL
? ? ? ? ? ? ? ? 使用增強聚合不僅可以簡化SQL代碼，而且還能對SQL語句的性能有所提升
? ? ? ? 多維分析指的是：
? ? ? ? ? ? ? ? SQL語法中的多維分析指的是 多種維度組合的分析，而不是多種維度的分析
? ? ? ? ?hive官網鏈接：hive官網
? ? ? ? ? ? ? ??
2、grouping sets - 指定維度組合
功能說明：
? ? ? ? 對指定的分組字段進行多種維度組合的聚合計算
hive-語法：
-- TODO 必須開區(qū)map端合并
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C
grouping sets( (A),(A,B),(A,B,C),..維度組合 )
presto、FlinkSQL、SparkSQL-語法：
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
grouping sets( (A),(A,B),(A,B,C),..維度組合 )語法區(qū)別：
????????1、hiveSQL中 group by 后面必須添加分組的字段
? ? ? ? ? ? ?presto、flinksql、sparksql group by 后面不需要指定分組字段
????????2、hiveSQL中 可以使用?grouping__id字段
??????????????presto、flinksql、sparksql 中并沒有提供?grouping__id字段，需要使用grouping(a,b,c) 函數(shù)來計算
代碼示例(HiveSQL):
-- TODO 必須開區(qū)map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','鄭州市','高開區(qū)','張3' union all select '河南省','鄭州市','高開區(qū)','張4' union all select '河南省','鄭州市','高開區(qū)','張5' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov)
)
;代碼示例(presto、flinkSQL、sparkSQL):
SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES ('河北省','石家莊市','新華區(qū)','張1'),('河北省','石家莊市','新華區(qū)','張2'),('河南省','鄭州市','高開區(qū)','張3'),('河南省','鄭州市','高開區(qū)','張4'),('河南省','鄭州市','高開區(qū)','張5'),('河南省','新鄉(xiāng)市','中華區(qū)','張6')) AS person_info_df (prov,city,area,name)
group by grouping sets ((prov,city,area),(prov)
)
;3、with rollup - 上卷維度組合
功能說明：
? ? ? ? 上卷維度組合，較grouping sets相比，不需要指定維度組合
?????????GROUP BY a, b, c, WITH ROLLUP? 等價于??
? ? ? ? ?GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))
hive-語法：
-- TODO 必須開區(qū)map端合并
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C
with rollup
presto、FlinkSQL、SparkSQL-語法：
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
rollup(A,B,C) 代碼示例(HiveSQL):
-- 1.必須開區(qū)map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','鄭州市','高開區(qū)','張3' union all select '河南省','鄭州市','高開區(qū)','張4' union all select '河南省','鄭州市','高開區(qū)','張5' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov,city,area with rollup
;代碼示例(presto、flinkSQL、sparkSQL):
SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES ('河北省','石家莊市','新華區(qū)','張1'),('河北省','石家莊市','新華區(qū)','張2'),('河南省','鄭州市','高開區(qū)','張3'),('河南省','鄭州市','高開區(qū)','張4'),('河南省','鄭州市','高開區(qū)','張5'),('河南省','新鄉(xiāng)市','中華區(qū)','張6')) AS person_info_df (prov,city,area,name)
group by rollup(prov,city,area)
;4、with cube - 全維度組合
功能說明：
? ? ? ? 多維度組合，會計算所有分組字段的維度組合，較grouping sets相比，不需要指定維度組合
?????????GROUP BY a, b, c, WITH CUBE? 等價于??
? ? ? ? ?GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))
cube(key1,key2...) 維度組合數(shù)：
hive-語法：
-- TODO 必須開區(qū)map端合并
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C
with cube
presto、FlinkSQL、SparkSQL-語法：
select 維度A,維度B,維度C,聚合函數(shù)(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
cube(A,B,C) 代碼示例(HiveSQL):
-- 1.必須開區(qū)map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','鄭州市','高開區(qū)','張3' union all select '河南省','鄭州市','高開區(qū)','張4' union all select '河南省','鄭州市','高開區(qū)','張5' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov,city,area with cube
;代碼示例(presto、flinkSQL、sparkSQL):
SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES ('河北省','石家莊市','新華區(qū)','張1'),('河北省','石家莊市','新華區(qū)','張2'),('河南省','鄭州市','高開區(qū)','張3'),('河南省','鄭州市','高開區(qū)','張4'),('河南省','鄭州市','高開區(qū)','張5'),('河南省','新鄉(xiāng)市','中華區(qū)','張6')) AS person_info_df (prov,city,area,name)
group by cube(prov,city,area)
;5、Grouping__ID、grouping()?的使用場景
功能說明：
? ? ? ? 可以用來判斷分組字段是否參與聚合，下面為?Grouping__ID 、grouping() 計算邏輯
使用場景：
? ? ? ? 當使用 grouping sets、with rollup、with cube進行聚合時，對不參與聚合的字段會使用null進行填充，這就導致查詢結果中分組字段為null時，無法區(qū)分是填充的null還是分組字段本身的null
? ? ? ? 遇到上述情況，可以使用下面兩種解決方式
? ? ? ? ? ? ? ? 1、將分組字段中的null進行替換處理，比如9999、other、其他
? ? ? ? ? ? ? ? 2、使用?Grouping__ID 或者?grouping() 進行區(qū)分
6、使用 增強聚合 會不會對查詢性能有提升呢？
測試用例-grouping sets：
-- TODO 必須開區(qū)map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov,city),(prov)
)
;測試用例-group by + union all：
set hive.map.aggr=true;
SELECT prov,city,area,count(1)
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6'
) AS person_info_df
group by prov,city,areaunion all SELECT prov,city,null as area,count(1)
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov,cityunion all SELECT prov,null as city,null as area,count(1)
FROM (select '河北省' as prov,'石家莊市' as city,'新華區(qū)' as area,'張1' as name union all select '河北省','石家莊市','新華區(qū)','張2' union all select '河南省','新鄉(xiāng)市','中華區(qū)','張6') AS person_info_df
group by prov對比執(zhí)行計劃：
對比運行時長：
結論：
通過上面執(zhí)行計劃和運行時長的對比，使用 grouping sets、with cube、with rollup 確實比
group by + union all 方式的性能要好，因為?增強group by避免了多次讀取底表，降低生成
job的個數(shù)，從而減輕了磁盤和網絡I/O時的壓力。
7、對grouping sets、with cube、with rollup 的優(yōu)化
由于在使用增強group by時，會在同一個job中完成多種維度組合的聚合(2的N次方)，當?shù)妆頂?shù)據(jù)量太大 或 維度過多時，可能造成計算資源不夠而導致任務失敗。
在 Hive中可以使用 set hive.new.job.grouping.set.cardinality=30?來對job進行拆分。
參數(shù)說明：
驗證SQL-實驗組：
驗證SQL-對照組：
