Hive多维分析函数-GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

猿途漫漫

已于 2023-01-10 08:46:05 修改

阅读量291

点赞数

分类专栏：工作复盘 hive 文章标签： hive 大数据 hadoop Powered by 金山文档

于 2023-01-09 11:12:33 首次发布

本文链接：https://blog.csdn.net/m0_64670604/article/details/128610741

版权

工作复盘同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

hive

5 篇文章 0 订阅

订阅专栏

文章探讨了在多维数据分析中，如何使用cube,rollup和groupingsets进行不同级别的分组和聚合操作。GROUPING_ID用于标识结果所属的分组集合，cube提供所有可能的子集，rollup是其子集，按最左侧维度层次聚合，而groupingsets则允许自定义组合。

摘要由CSDN通过智能技术生成

-- 多维分析时可以标示结果属于哪一个分组集合
-- gid 为 0 的，维度为全表（NULL、NULL）
-- gid 为 1 的，维度为 a
-- gid 为 2 的，维度为 b
-- gid 为 3 的，维度为（a、b）
select
key, value, GROUPING__ID as gid, count(*) as cnt
from
(select 'key01' as key,'value01' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key02' as key,'value02' as value) t
GROUP BY key, value WITH ROLLUP;

-- 多维分析时可以标示结果属于哪一个分组集合
-- gid 为 0 的，维度为全表（NULL、NULL）
-- gid 为 1 的，维度为 a
-- gid 为 2 的，维度为 b
-- gid 为 3 的，维度为（a、b）
select
key, value, GROUPING__ID as gid, count(*) as cnt
from
(select 'key01' as key,'value01' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key02' as key,'value02' as value) t
GROUP BY key, value grouping sets (key,value,(key,value),());

-- 多维分析时可以标示结果属于哪一个分组集合
-- gid 为 0 的，维度为全表（NULL、NULL）
-- gid 为 1 的，维度为 a
-- gid 为 2 的，维度为 b
-- gid 为 3 的，维度为（a、b）
select
key, value, GROUPING__ID as gid, count(*) as cnt
from
(select 'key01' as key,'value01' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key03' as key,'value03' as value
union all
select 'key02' as key,'value02' as value
union all
select 'key02' as key,'value02' as value) t
GROUP BY key, value WITH CUBE ORDER BY gid;