HIVE grouping sets分组聚合

最新推荐文章于 2024-05-07 12:44:53 发布

朱李寒水

最新推荐文章于 2024-05-07 12:44:53 发布

阅读量638

点赞数

分类专栏：大数据开发文章标签： SQL GroupingSets 分组聚合数据处理聚合函数

本文链接：https://blog.csdn.net/weixin_43178315/article/details/126678977

版权

大数据开发专栏收录该内容

11 篇文章 0 订阅

订阅专栏

使用grouping sets进行分组聚合。

造数据

WITH t1 AS (
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item1' AS item, 100 AS amount, 0 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华北' AS area, 'item2' AS item, 110 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item3' AS item, 120 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华北' AS area, 'item4' AS item, 130 AS amount, 0 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item5' AS item, 140 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item6' AS item, 150 AS amount, 0 AS type
)

需求：按组聚合求sum(amount)，组1：years,months,area,type；组2：years,months,type
实现代码：

SELECT years,months,type,sum(amount) 
FROM t1 
GROUP BY 
	years,months,area,type
grouping sets
(
	(years,months,area,type),
	(years,months,type)
)`WITH t1 AS (
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item1' AS item, 100 AS amount, 0 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华北' AS area, 'item2' AS item, 110 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item3' AS item, 120 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华北' AS area, 'item4' AS item, 130 AS amount, 0 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item5' AS item, 140 AS amount, 1 AS type
UNION ALL
SELECT 2022 AS years, 9 AS months, '华南' AS area, 'item6' AS item, 150 AS amount, 0 AS type
)

-- 这里select与groupby的字段不用完全一样都可以，如area没在select里
SELECT years,months,type,sum(amount) 
FROM t1 
GROUP BY 
	years,months,area,type
grouping sets
(
	(years,months,area,type),
	(years,months,type)
)`