目录 1. 问题讨论1.1 数据准备1.2 问题描述1.3 其它方法多维度聚合（union、with cube） 2. Hive中的grouping sets函数2.1 grouping sets方法多维度聚合2.2 grouping sets在联结join中使用的踩坑点2.3 grouping sets函数使用补充事项 3. Presto中的grouping sets函数3.1 函数grouping sets使用及坑点（5点说明）3.2 函数grouping sets在hive与presto中的区别 本文详细记录了函数grouping sets使用时遇到的坑，全文代码基于Hive和Presto实现。 1. 问题讨论 1.1 数据准备
首先建立商品销售表：
CREATE TABLE temp.goods_sale_info( `province` string comment '省份', `city` string comment '城市', `goodsid` string comment '商品编号', `goodsname` string comment '商品名称', `sales_qty` decimal(38,5) comment '销量', `sales_amt` decimal(38,5) comment '销售额' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; insert into table temp.goods_sale_info values ('江西省','南昌市','1000','可口可乐','10','40') ,('福建省','福州市','1000','可口可乐','200','1000') ,('福建省','厦门市','1000','可口可乐','300','1500') ,('福建省','厦门市','1200','百事可乐','200','1000') ,('福建省','厦门市','2000','伊利安慕希','300','21000'); select * from temp.goods_sale_info再建立一个商品信息表：
CREATE TABLE temp.goods_info( `goodsid` string comment '商品编号', `goodsname` string comment '商品名称', `catgory_id` string comment '商品种类编号', `catgory_name` string comment '商品种类' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; insert into table temp.goods_info values ('1000','可口可乐','11','饮料') ,('1200','百事可乐','11','饮料') ,('2000','伊利安慕希','12','乳制品') select * from temp.goods_info 1.2 问题描述根据不同字段分组聚合实现各个维度的销量和销售额
1.3 其它方法多维度聚合（union、with cube）要实现城市’'city’维度聚合求商品销量、销售额以及从省份’province’维度聚合求商品销量、销售额，并放入一个表中，一般可以采用下面两种方法：
-- 方法1：分开聚合 select province ,NULL as city ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,'prov_dim' dim_flag from temp.goods_sale_info group by province,goodsid,goodsname union all select NULL as province ,city ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,'city_dim' dim_flag from temp.goods_sale_info group by city,goodsid,goodsname运行结果：
-- 方法2：with cube高级别聚合 create table temp.goods_sales_info_cube as -- 要生成表才可以进行下一步筛选，起表别名后嵌套筛选不出来 select province ,city ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,grouping__id --,可写可不写 from temp.goods_sale_info group by province ,city ,goodsid ,goodsname with cube -- rollup 是以最左侧指标为主进行组合聚合,是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。更换province和city的前后会出来不同结果 -- rollup是cube的一种特殊情况，cube对所有的维度进行聚合，会出现city,province和province,city的结果 -- 对生成的结果进行筛选，即可得到与方法1同样的结果 select * from temp.goods_sales_info_cube where grouping__id in ('13','14') -- 不想生成grouping__id字段的话可以用下面条件判断 -- where (province is null and city is not null and goodsid is not null and goodsname is not null) -- or (province is not null and city is null and goodsid is not null and goodsname is not null)运行结果： 相较于with rollup 和with cube，grouping sets可以实现分组字段的自由组合，当分组字段变多，想要按需分组时候用grouping sets更方便。
2. Hive中的grouping sets函数对于多个维度聚合问题，grouping sets不用像cube方式将分组字段排列组合列出全部维度的结果，能够实现更灵活的组合。
2.1 grouping sets方法多维度聚合 select province ,city ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,GROUPING__ID from temp.goods_sale_info group by province ,city ,goodsid ,goodsname grouping sets( (), -- 1、全国销量、销售额 (goodsid,goodsname), -- 2、各商品的全国销量、销售额 (province,goodsid,goodsname), -- 3、各省各商品的销量、销售额 (province,goodsid), -- 4、各省各商品的销量、销售额 (city) -- 5、各城市销量、销售额 )运行结果： 函数及运行结果讲解： 1、group by后面放的字段表示要分组聚合的全部字段 2、grouping sets 后面放的是 group by 后面各种字段的组合，根据实际需求进行组合，组合字段用小括号括起来（上面代码中的第2、3、4组），也可以是单一字段（上面代码中sets里面的第5组） 3、在求取全国的成交量的时候其实是不需要分组聚合的，但是为了使用 grouping sets，所以在求取全国成交量的时候用 group by null（上面代码中sets里面的第1组） 4、sets中第3组和第4组的区别在于有没有写goodsname，没有写goodsname的第4组生成的结果中goodsname列为空值
2.2 grouping sets在联结join中使用的踩坑点在具有表联结语句中grouping sets函数使用有个踩坑点，初次使用很可能都报错找不到原因。。总结一下就是： 表联结的结果出来之后再用grouping sets，分组字段组合sets里面用表别名会报错。
报错语句见下：
-- 报错语句 select t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname ,sum(t1.sales_qty) as sales_qty ,sum(t1.sales_amt) as sales_amt ,GROUPING__ID from temp.goods_sale_info t1 left join temp.goods_info t2 on t1.goodsid=t2.goodsid group by t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname grouping sets( (t1.province,t2.catgory_id,t2.catgory_name) )正确语句见下：
-- 正确语句 select province ,city ,catgory_id ,catgory_name ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,GROUPING__ID from ( select t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname ,sales_qty ,sales_amt from temp.goods_sale_info t1 left join temp.goods_info t2 on t1.goodsid=t2.goodsid ) t --在表t的基础上使用grouping sets函数 group by province ,city ,catgory_id ,catgory_name ,goodsid ,goodsname grouping sets( (province,catgory_id,catgory_name) --这里仅有3个字段，但select中列有不在sets中的非分组字段city、goodsid、goodsname，hive不报错，presto会报错 )运行结果：
2.3 grouping sets函数使用补充事项1、select子句中的GROUPING__ID是两个下划线 2、执行语句后GROUPING__ID的结果是数字，如果sets中有5种组合，GROUPING__ID会生成5个不等的数字，具体哪个数字对应哪个维度，需要根据生成的表进行测试判断，用于自定义划分维度 3、GROUPING__ID的数字虽然是随机产生的，但是只要不改变表以及查询条件，每次运行都是这些数字
3. Presto中的grouping sets函数 3.1 函数grouping sets使用及坑点（5点说明）用法： 1、函数grouping要与group by、grouping sets配合使用 2、函数grouping()中列出sets中所有分组涉及的字段，运行后grouing()列生成结果为二进制转化来的十进制数字
-- 比如 select column1,column2,column3, grouping(column1,column2,column3) group by grouping sets（ (column1), (column2,column3) ） -- 如果分组中包含相应的列，则将位设置为0，否则将其设置为1 -- 第一组 中 三个字段的 为 0 1 1 -- 第二组 中 三个字段的 为 1 0 0 -- grouping函数的值即为011和100对应的十进制数字参考链接：presto grouping操作
3、group by后面只跟grouping sets()，不加select中的单一字段，否则函数grouping sets无作用
-- 如果group by写上单一字段 select province ,city ,catgory_id ,catgory_name ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,grouping(province,city,catgory_id,catgory_name,goodsid,goodsname) from ( select t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname ,sales_qty ,sales_amt from temp.goods_sale_info t1 left join temp.goods_info t2 on t1.goodsid=t2.goodsid ) t --也是要在表t的基础上使用grouping sets函数 group by province ,city ,catgory_id ,catgory_name ,goodsid ,goodsname ,grouping sets( --这里记得加上逗号, (province,catgory_id,catgory_name), (province,catgory_id,catgory_name,goodsid,goodsname), (province,city), (province) )运行结果： 表t结果有5条记录，grouping sets中有4种组合。按照上面语句执行，结果记录数为 5×4=20条记录，同理1种组合结果为5×1，2种组合为5×2，3种组合为5×3 4、不用的分组字段不要在select子句中写出
-- 与hive不同，如果不出现在grouping sets中的字段，select子句写上会报错 -- 比如sets中不涉及city、goodsid、goodsname，select子句中写出来报错 select province -- ,city ,catgory_id ,catgory_name -- ,goodsid -- ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,grouping(province,catgory_id,catgory_name) from ( select t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname ,sales_qty ,sales_amt from temp.goods_sale_info t1 left join temp.goods_info t2 on t1.goodsid=t2.goodsid ) t group by grouping sets( (province,catgory_id,catgory_name) )5、函数grouping中要将grouping sets所有分组组合用到的字段取并集列出
select province ,city ,catgory_id ,catgory_name ,goodsid ,goodsname ,sum(sales_qty) as sales_qty ,sum(sales_amt) as sales_amt ,grouping(province,city,catgory_id,catgory_name,goodsid,goodsname) -- grouping里面一定要把sets中用到的字段写全，不然生成的数字会缺 -- 比如下面sets中有3种组合，如果grouping()种缺少字段，数字会不是3个 from ( select t1.province ,t1.city ,t2.catgory_id ,t2.catgory_name ,t1.goodsid ,t1.goodsname ,sales_qty ,sales_amt from temp.goods_sale_info t1 left join temp.goods_info t2 on t1.goodsid=t2.goodsid ) t --也是要在表t的基础上使用grouping sets函数 group by grouping sets( (province,catgory_id,catgory_name), (province,catgory_id,catgory_name,goodsid,goodsname), (province,city) ) 3.2 函数grouping sets在hive与presto中的区别综上可见，可以对比出presto与hive在grouping sets函数使用上的区别： 1、Hive中select子句中用GROUPING__ID，GROUPING__ID不是函数；Presto的select子句中grouping是一个函数，要采用grouping(column_1,column_2,…)，列出分组涉及到的所有字段。不过两者运行后的结果都是数字，可以用于后面的维度测试 2、Hive的group by子句中要列出单一字段，然后加上grouping sets，并且grouping sets 前面不加逗号“,”；Presto的group by子句中仅有grouping sets
 1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。 |
