Hive高级分组grouping\rollup\cube # 背景介绍 > **注：以下代码均在Apache Hive2.3.7版本测试** **在数据仓库中进行各种ETL任务时，经常需要对数据进行各种维度的分组来计算各种指标、标签数据。分组使用group by语句这个大家应该都了解，但是我们有时候需要多种维度的组合进行不同粒度的汇总，这时候有些高级的用法就比较有用了。我们举个栗子：** **现有如下商场销售数据（自行编撰）** | **品牌** | **商品名称** | **价格** | | -------- | ------------ | -------- | | **小米** | **冰箱** | **890** | | **海尔** | **冰箱** | **1290** | | **海尔** | **冰箱** | **1099** | | **小米** | **洗衣机** | **569** | | **海尔** | **洗衣机** | **2599** | | **海尔** | **洗衣机** | **1299** | | **小米** | **微波炉** | **459** | | **小米** | **微波炉** | **249** | | **海尔** | **微波炉** | **388** | | **小米** | **电视机** | **1599** | | **小米** | **电视机** | **1599** | | **海尔** | **电视机** | **1599** | **想要统计这几个信息：** * **各个品牌各个商品的销售总金额** * **各种商品销售总金额** * **各个品牌销售总金额** * **商场总的销售金额** # group by实现 **那么该怎么做呢？首先大家想到的一定是group by去进行聚合，但是我们不能通过一个语句同时得到这四种结果，需要使用union来拼接：** ``` select brand,mc,sum(price) from test.tb_gp group by brand,mc  -- 各个品牌各个商品的销售总金额 union select brand,null `mc`,sum(price) from test.tb_gp group by brand  -- 各个品牌销售总金额 union select null,mc,sum(price) from test.tb_gp group by mc -- 各种商品销售总金额 union select null,null,sum(price) from test.tb_gp ; -- 商场总的销售金额 ``` **查询结果如下所示：**  # grouping sets()实现 **这样代码写起来比较复杂，并且执行效率还低下。其实在Hive中有grouping sets() 函数，可以自由配置聚合的维度，实现高效的聚合。还是上面的需求，我们可以这样写代码：** ``` select brand      ,mc      ,sum(price) from test.tb_gp group by brand,mc grouping sets((brand,mc),brand,mc,()); ``` **查询结果如下：**  **通过对比我们可以发现，相同的功能，使用后面的方法代码要简洁了许多，其实不仅仅是代码简洁了，执行效率也有很大的提升。同样的运行环境上面采用多个union的方法执行了3分38秒，而使用了grouping sets()的代码只用了34秒。试想一下如果你的数据量很多的情况下原本三天半的任务可能半天就跑出来了是不是很爽。** **那么刚才的代码是怎么实现的？原理是什么？我们通过Hive官方博客的内容给大家介绍一下：** **GROUP BY 中的 GROUPING SETS 子句允许我们在同一记录集中指定多个 GROUP BY 选项。所有 GROUPING SET 子句都可以在逻辑上用由 UNION 连接的几个 GROUP BY 查询来表示。下表显示了几个这样的等效语句，这有助于大家的理解。分组集子句中的空白集 （ ）表示计算总体的聚合。** | **使用grouping sets** | **使用group by的等效方法** | | ------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )** | **SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b** | | **SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)** | **SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b** **UNION** **SELECT a, null, SUM( c ) FROM tab1 GROUP BY a** | | **SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)** | **SELECT a, null, SUM( c ) FROM tab1 GROUP BY a** **UNION** **SELECT null, b, SUM( c ) FROM tab1 GROUP BY b** | | **SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )** | **SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b** **UNION** **SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null** **UNION** **SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b** **UNION** **SELECT null, null, SUM( c ) FROM tab1** | # 其他实现方法 **grouping sets 虽然好用，但是每次查询都需要自己手动配置聚合时维度的组合还是比较麻烦的，对于一些常见的场景，Hive提供了两个运算符cube 与 rollup 这样能使代码的书写更简便。** ## cube ** CUBE仅与 GROUP BY 一起使用。CUBE 创建在其参数中列集的所有可能的组合方式。换句话说一旦我们在一组维度上计算了一个 CUBE，我们就可以得到这些维度上所有可能的聚合答案。** ** 数据cube也称数据立方体，cube在大数据领域应用的还是比较多的，通过提前进行预先运算，这样当后续数据应用用到某种维度组合，直接查询这种组合即可得到相应结果。进行cube运算后得到的结果一般会存储到Hbase这种数据库中，目的就是可以实现快速的查询。** **我们再通过一个代码让大家理解cube做了什么：** ``` select brand      ,mc      ,sum(price) from test.tb_gp group by brand,mc with cube; ``` **等效于：** ``` select brand      ,mc      ,sum(price) from test.tb_gp group by brand,mc grouping sets((brand,mc),brand,mc,()); ``` ## rollup ** rollup与cube类似，也是一种对数据的聚合操作，ROLLUP 子句仅与 GROUP BY 一起使用，以计算维度层次结构级别的聚合。** **可以通过如下代码来理解** ``` select brand      ,mc      ,sum(price) from test.tb_gp group by brand,mc with rollup; ``` **与之等效的grouping sets语句：** ``` select brand      ,mc      ,sum(price) from test.tb_gp group by brand,mc grouping sets((brand,mc),brand,()); ``` # 注意事项 ** 如果数据中包含null值，那么计算的结果就不太容易分辨出来了（因为聚合后会产生null），因此有必要在进行聚合运算前将数据中的null值进行预处理。比如可以使用非空值查找函数 coalesce(col,0) 将null值替换为0。具体的处理策略需要结合实际业务来做。**