hive 之 Cube, Rollup介绍

最新推荐文章于 2023-04-18 10:28:57 发布
转载 最新推荐文章于 2023-04-18 10:28:57 发布 · 223 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/remainsu/p/11060580.html

本文介绍了Hive SQL中的统计函数和窗口函数。统计函数包括GROUPING SETS、CUBE函数、ROLL UP函数和Grouping_ID函数，GROUPING SETS可指定多个统计维度，CUBE能实现多维度查询，ROLL UP可递减多级统计，Grouping_ID可区分统计与列本身的null值。还提及了窗口函数，可参考相关文档。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1. GROUPING SETS

GROUPING SETS作为GROUP BY的子句，允许开发人员在GROUP BY语句后面指定多个统维度，可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来。

为方便理解，以testdb.test_1为例：

hive> use testdb;
hive> desc test_1;

user_id        string      id                
device_id      string      设备类型：手机、平板             
os_id          string      操作系统类型：ios、android            
app_id         string      手机app_id             
client_v       string      客户端版本             
channel        string      渠道
grouping sets语句等价hive语句
select device_id,os_id,app_id,count(user_id) from  test_1 group by device_id,os_id,app_id grouping sets((device_id)) SELECT device_id,null,null,count(user_id) FROM test_1 group by device_id
select device_id,os_id,app_id,count(user_id) from  test_1 group by device_id,os_id,app_id grouping sets((device_id,os_id))SELECT device_id,os_id,null,count(user_id) FROM test_1 group by device_id,os_id
select device_id,os_id,app_id,count(user_id) from  test_1 group by device_id,os_id,app_id grouping sets((device_id,os_id),(device_id))SELECT device_id,os_id,null,count(user_id) FROM test_1 group by device_id,os_id UNION ALL SELECT device_id,null,null,count(user_id) FROM test_1 group by device_id
select device_id,os_id,app_id,count(user_id) from  test_1 group by device_id,os_id,app_id grouping sets((device_id),(os_id),(device_id,os_id),())SELECT device_id,null,null,count(user_id) FROM test_1 group by device_id UNION ALL SELECT null,os_id,null,count(user_id) FROM test_1 group by os_id UNION ALL SELECT device_id,os_id,null,count(user_id) FROM test_1 group by device_id,os_id  UNION ALL SELECT null,null,null,count(user_id) FROM test_1

2. CUBE函数

cube简称数据魔方，可以实现hive多个任意维度的查询，cube(a,b,c)则首先会对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),(c)，最后在对全表进行group by，cube会统计所选列中值的所有组合的聚合

select device_id,os_id,app_id,client_v,channel,count(user_id) 
from test_1 
group by device_id,os_id,app_id,client_v,channel with cube;

等价于：

SELECT device_id,null,null,null,null ,count(user_id) FROM test_1 group by device_id
UNION ALL
SELECT null,os_id,null,null,null ,count(user_id) FROM test_1 group by os_id
UNION ALL
SELECT device_id,os_id,null,null,null ,count(user_id) FROM test_1 group by device_id,os_id
UNION ALL
SELECT null,null,app_id,null,null ,count(user_id) FROM test_1 group by app_id
UNION ALL
SELECT device_id,null,app_id,null,null ,count(user_id) FROM test_1 group by device_id,app_id
UNION ALL
SELECT null,os_id,app_id,null,null ,count(user_id) FROM test_1 group by os_id,app_id
UNION ALL
SELECT device_id,os_id,app_id,null,null ,count(user_id) FROM test_1 group by device_id,os_id,app_id
UNION ALL
SELECT null,null,null,client_v,null ,count(user_id) FROM test_1 group by client_v
UNION ALL
SELECT device_id,null,null,client_v,null ,count(user_id) FROM test_1 group by device_id,client_v
UNION ALL
SELECT null,os_id,null,client_v,null ,count(user_id) FROM test_1 group by os_id,client_v
UNION ALL
SELECT device_id,os_id,null,client_v,null ,count(user_id) FROM test_1 group by device_id,os_id,client_v
UNION ALL
SELECT null,null,app_id,client_v,null ,count(user_id) FROM test_1 group by app_id,client_v
UNION ALL
SELECT device_id,null,app_id,client_v,null ,count(user_id) FROM test_1 group by device_id,app_id,client_v
UNION ALL
SELECT null,os_id,app_id,client_v,null ,count(user_id) FROM test_1 group by os_id,app_id,client_v
UNION ALL
SELECT device_id,os_id,app_id,client_v,null ,count(user_id) FROM test_1 group by device_id,os_id,app_id,client_v
UNION ALL
SELECT null,null,null,null,channel ,count(user_id) FROM test_1 group by channel
UNION ALL
SELECT device_id,null,null,null,channel ,count(user_id) FROM test_1 group by device_id,channel
UNION ALL
SELECT null,os_id,null,null,channel ,count(user_id) FROM test_1 group by os_id,channel
UNION ALL
SELECT device_id,os_id,null,null,channel ,count(user_id) FROM test_1 group by device_id,os_id,channel
UNION ALL
SELECT null,null,app_id,null,channel ,count(user_id) FROM test_1 group by app_id,channel
UNION ALL
SELECT device_id,null,app_id,null,channel ,count(user_id) FROM test_1 group by device_id,app_id,channel
UNION ALL
SELECT null,os_id,app_id,null,channel ,count(user_id) FROM test_1 group by os_id,app_id,channel
UNION ALL
SELECT device_id,os_id,app_id,null,channel ,count(user_id) FROM test_1 group by device_id,os_id,app_id,channel
UNION ALL
SELECT null,null,null,client_v,channel ,count(user_id) FROM test_1 group by client_v,channel
UNION ALL
SELECT device_id,null,null,client_v,channel ,count(user_id) FROM test_1 group by device_id,client_v,channel
UNION ALL
SELECT null,os_id,null,client_v,channel ,count(user_id) FROM test_1 group by os_id,client_v,channel
UNION ALL
SELECT device_id,os_id,null,client_v,channel ,count(user_id) FROM test_1 group by device_id,os_id,client_v,channel
UNION ALL
SELECT null,null,app_id,client_v,channel ,count(user_id) FROM test_1 group by app_id,client_v,channel
UNION ALL
SELECT device_id,null,app_id,client_v,channel ,count(user_id) FROM test_1 group by device_id,app_id,client_v,channel
UNION ALL
SELECT null,os_id,app_id,client_v,channel ,count(user_id) FROM test_1 group by os_id,app_id,client_v,channel
UNION ALL
SELECT device_id,os_id,app_id,client_v,channel ,count(user_id) FROM test_1 group by device_id,os_id,app_id,client_v,channel
UNION ALL
SELECT null,null,null,null,null ,count(user_id) FROM test_1

3. ROLL UP函数

rollup可以实现从右到左递减多级的统计，显示统计某一层次结构的聚合

select device_id,os_id,app_id,client_v,channel,count(user_id) 
from test_1 
group by device_id,os_id,app_id,client_v,channel with rollup;

等价于：

select device_id,os_id,app_id,client_v,channel,count(user_id) 
from test_1 
group by device_id,os_id,app_id,client_v,channel 
grouping sets ((device_id,os_id,app_id,client_v,channel),(device_id,os_id,app_id,client_v),(device_id,os_id,app_id),(device_id,os_id),(device_id),());

4.Grouping_ID函数

当我们没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null。（写一个排列组合的算法，就马上理解了，grouping_id其实就是所统计各列二进制和）

例子如下：

Column1 (key)Column2 (value)
1NULL
11
22
33
3NULL
45

hql统计：

  SELECT key, value, GROUPING_ID, count(*) from T1 GROUP BY key, value WITH ROLLUP

结果如下：

 keyvalueGROUPING_ID count(*) 
NULLNULL0     006
1NULL1     102
1NULL3     111
113     111
2NULL1     101
223     111
3NULL1     102
3NULL3     111
333     111
4NULL1     101
453     111

GROUPING_ID转变为二进制，如果对应位上有值为null，说明这列本身值就是null。（通过类DataFilterNull.py 扫描，可以筛选过滤掉列中null、“”统计结果),

5. 窗口函数

hive窗口函数，感觉大部分都是在模仿oracle，有对oracle熟悉的，应该看下就知道怎么用。

具体参见：http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hive/language_manual/ptf-window.html

参考文章

  1. https://blog.youkuaiyun.com/gua___gua/article/details/52523698

转载于:https://www.cnblogs.com/remainsu/p/11060580.html

Hive cube / rollup / grouping sets/GROUPING__ID用法
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
01-31 767
表结构如下： CREATE EXTERNAL TABLE `tmp.shop_detail_tb`( `id` string COMMENT '', `name` string COMMENT '', `type` string COMMENT '', `amt` string COMMENT '', `month` string COMMENT '' ) PARTITIONED BY ( `dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t
mysql中的cube和rollup_【hive】cube和rollup函数
weixin_42468901的博客
01-27 816
cube数据立方体(Data Cube)，是多维模型的一个形象的说法.(关于多维模型这里不讲述,在数据仓库设计过程中还挺重要的,有兴趣自行查阅)立方体其本身只有三维，但多维模型不仅限于三维模型，可以组合更多的维度为什么叫数据立方体?一方面是出于更方便地解释和描述，同时也是给思维成像和想象的空间；另一方面是为了与传统关系型数据库的二维表区别开来下图为数据立方体的形象图其实并不用把cube理解得很高大...
Hive新功能 Cube, Rollup介绍
weixin_33862993的博客
01-07 318
说明：Hive之cube、rollup，还有窗口函数，在传统关系型数据（Oracle、sqlserver）中都是有的，用法都很相似。 GROUPING SETS GROUPING SETS作为GROUP BY的子句，允许开发人员在GROUP BY语句后面指定多个统计选项，可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来，下面是几个实例可以帮助我们了解，...
hive新功能 Cube, Rollup介绍
热门推荐
扫大街的程序员
12-08 2万+
说明：hive之cube、rollup，还有窗口函数，在传统关系型数据（oracle、sqlserver）中都是有的，用法都很相似。 GROUPING SETS GROUPING SETS作为GROUP BY的子句，允许开发人员在GROUP BY语句后面指定多个统计选项，可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来，下面是几个实例可以帮助我们了
hive中rollup、cube使用方法
TangYuG的博客
10-09 1522
当我们业务中需要根据多个维度或者多个粒度统计分析数据的时候，最笨的办法是多次group by 然后union all到一起，这个时候其实可以用rollup、cube来实现，当然要了解这两个函数的原理，如果觉得结果比我们想要的结果多一些，那可以通过grouping sets 灵活的设置分组字段。......
图解Hive中group by时cube和rollup的区别
JiajunBernoulli的博客
05-22 837
前言 Jack,male,unmarried Tom,male,unmarried Jim,male,unmarried James,male,married Tony,male,married Jessica,female,married Alice,female,married Chris,female,married Rose,female,unmarried Anny,female,unmarried create table test_table(name string,gender strin
一文看懂hive中group by rollup/cube group by sets用法及区别（附案例）
Yuki的博客
11-17 6353
一文看懂group by rollup/cube group by sets用法及区别（附案例）
hive新功能cube和rollup
yang灬仔
09-16 1496
1、cube cube简称数据魔方，可以实现hive多个任意维度的查询，cube(a,b,c)则首先会对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后在对全表进行group by，他会统计所选列中值的所有组合的聚合 select device_id,os_id,app_id,client_version,from_id,count(user_id) from test_xinyan_reg group by device_id,os_
Hive开窗函数测试-cube，rollup
04-19
NULL 博文链接：https://wankunde.iteye.com/blog/1879565
hive Cube, Rollup介绍
u011500419的专栏
04-11 286
https://blog.youkuaiyun.com/moon_yang_bj/article/details/17200367 参考资料 apache hive窗口函数官方介绍：http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hive/language_manual/ptf-window.html apache h...
Hive分析函数详解：GROUPING SETS/CUBE/ROLLUP
空城的博客
04-18 2146
cube和rollup的区别是CUBE会根据指定列进行所有组合，rollup会根据顺序从左到右进行组合可以使用grouping进行空值判断可以使用grouping__id进行粒度区分。
Hive - Cube, Rollup, GroupingId 示例与详解
BITDDD小栈
05-06 3417
​上篇文章讲到了Grouping Sets 的使用方法，Grouping Sets 可以看做是将 group by 的内容进行 union 整合，这篇文章将基于同一思想进行扩展介绍两个方法 Cube 以及 Rollup，同时给出辅助函数 GroupingId 的生成方法与使用方法。...
Hive高级聚合之GROUPING SETS/ROLLUP/CUBE
yanzhelee
08-21 1860
GROUPING SETS该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅仅使用一个stage完成这些操作。ROLLUP和CUBE是GROUPING SETS的组合。
hive学习之grouping set /cube/rollup
cclovezbf的博客
09-05 1197
就是在我们根据多个维度分组的时候，例如group by a,b,c,d 有时候我们也需要这几个维度中部分维度的聚合值 例如group by a,b。这个时候通过group by a,b,c,d grouping sets((a,b,c,d),(a,b))即可，看起来简洁，使用方便。a=1，b=null ，sum(c)=1 这个到底是根据a 分的组 b默认是null ，还是根据ab 分组，a=1 b=null。需求2，同时算出需求1也就是根据a，b分组的sum(c)，和根据a分组后的sum(c)
【hive】 rollup的空值过滤
SmellyKitty
01-24 1697
rollup rollup 用于hive在group by的顺序分组聚合，举例如下： select mac, stat_date, avg(cast(cpuOccupancyRate as int)) cpu_avg from bidata.t_ods_pp_uplink_status_id where stat_date>=20220119 and stat_date<=20220119 group by rollup(mac,stat_date) 输出结果： ma
hive功能 Cube, Rollup介绍
kf_panda
09-13 6207
GROUPING SETS GROUPING SETS作为GROUP BY的子句，允许开发人员在GROUP BY语句后面指定多个统计选项，可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来，下面是几个实例可以帮助我们了解， 以acorn_3g.test_xinyan_reg为例： [dp@YZSJHL19-87 xjob]$ hive -e
SQL-rollup、cube（hive）
newas3的博客
06-19 347
先举个例： group by WITH ROLLUP mysql> select dep,pos,avg(sal) from employee group by dep,pos with rollup; +------+------+-----------+ | dep | pos | avg(sal) | +------+------+-----------+ | 01 | 01 | 1500.0000 | | 01 | 02 | 1950.0000 | | 01 | NU
hiveSql cube使用demo
最新发布
04-15
### Hive SQL Cube 使用示例 Cube 是一种强大的聚合工具，在 Hive 中用于生成多维分析的结果集。通过 `CUBE` 关键字，可以自动计算所有可能的组合维度上的聚合结果。 #### 示例 1：基本 CUBE 使用 假设有一个销售数据表 `sales`，包含字段 `region`（地区）、`product`（产品）和 `amount`（销售额）。以下是使用 `CUBE` 的简单例子： ```sql SELECT region, product, SUM(amount) AS total_sales FROM sales GROUP BY region, product WITH CUBE; ``` 上述查询不仅会返回按 `region` 和 `product` 组合的聚合结果，还会额外提供以下几种情况下的总和： - 每个地区的总计销售额（忽略产品） - 每种产品的总计销售额（忽略地区） - 整体的总计销售额（忽略地区和产品） 这相当于手动执行多个分组操作并将其结果合并在一起的效果[^1]。 --- #### 示例 2：带有过滤条件的 CUBE 查询 如果希望在应用 `CUBE` 之前先筛选部分数据，则可以在 `WHERE` 子句中指定条件。例如，仅统计金额大于 100 的记录： ```sql SELECT region, product, SUM(amount) AS total_sales FROM sales WHERE amount > 100 GROUP BY region, product WITH CUBE; ``` 此语句会在满足条件的基础上继续扩展所有的维度组合[^2]。 --- #### 示例 3：结合 ROLLUP 进行更灵活控制 虽然 `CUBE` 提供了全面的维度组合，但在某些情况下只需要特定层次结构的汇总而非全部可能性时，可以选择 `ROLLUP` 或混合两者来达到目的。比如下面的例子展示了如何优先考虑区域层面的整体趋势再深入具体商品表现： ```sql -- 结合 rollup 只关注更高层面上的变化规律 SELECT region, product, SUM(amount) AS total_sales FROM sales GROUP BY region, product WITH ROLLUP; -- 如果想保留 cube 功能又不想完全展开则可这样写 SELECT COALESCE(region,'All_Regions') as reg, COALESCE(product,'All_Products') prod, sum(amount) FROM sales GROUP BY GROUPING SETS ((region), (product), (), (region, product)); ``` 这里利用了 `COALESCE()` 函数处理 NULL 值以便于阅读最终报表中的超级合计项名称；而 `GROUPING SETS` 则允许精确定义哪些集合应该被包括进来从而避免不必要的冗余运算过程发生[^3]。 --- #### 性能调优建议 当面对大规模数据集运行涉及复杂聚集操作如立方体构建之类的任务时需要注意性能问题。可以通过以下几个方面来进行改善: - **压缩输入文件大小**: 对原始日志进行预处理去掉无关列减少传输开销。 - **调整mapreduce参数设置**, 如增大split size让mapper数量更加合理分布防止过多小job启动消耗资源. - 应用前面提到过的那些关于joins以及filters的最佳实践同样适用于此处因为它们直接影响到底层mr框架的工作效率.[^4] ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值