WITH CUBE、WITH ROLLUP、GOUPING SETS用法

在HIVE实际应用中，常用到多维数据分析功能。

比如我们在统计分省、分城市、分产品的收入和购买人数的时候，想要一次性输出（总的购买人数和总收入、分省的购买人数和收入、分省分产品的购买人数和收入）这些指标，那么要写好多个分组查询语句才能得到最终的结果，有了数据魔方使查询变得简单方便，并提高了效率。

cube是数据魔方，提供全部维度数据；

rollup是数据底层到上层分层维度数据；

grouping sets提供自定义维度的数据。

示例语法为：

--提供全维度排列组合selectprovince,city,product_name,count(distinctbuy_uid),sum(price)fromsaleswheredt=2022-03-01groupbyprovince,city,product_namewithcube;--提供分层维度排列组合selectprovince,city,product_name,,count(distinctbuy_uid)sum(price)fromsaleswheredt=2022-03-01groupbyprovince,city,product_namewithrollup;--提供自定义维度排列组合selectprovince,city,product_name,count(distinctbuy_uid),sum(price)fromsaleswheredt=2022-03-01groupbyprovince,city,product_namegroupingsets((),(province),(province,city));

我们再把grouping_id打出来，如

select province,city,product_name,GROUPING_ID,rpad(reverse(bin(cast(GROUPING_ID as bigint))),3,0) as bin_id ,count(distinct buy_uid),sum(price) from sales where dt=2022-03-01 group by province,city,product_name with cube;

以上面示例来说 with cube 实现了八种维度组合，其中0代表此维度值不存在。

with rollup 实现了4种组合（000、 100 、110 、111），详见上方表格中解释。

grouping sets可以任意定制维度，如示例中定制了000，100，110四个维度。

以上三种语法，rollup和grouping sets常用，cube全维度组合提供的数据量偏大，有时根据实际需求，有些组合并没有实际上的含义，但还被统计出来，结果数据集会变得很大尤其是维度特别多时，还会产生维度爆炸。