关键字:
GROUPING SETS: 根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL
GROUPING__ID:表示结果属于哪一个分组集合,属于虚字段
简单示例:
关于grouping sets的使用,通俗的说,grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
create table temp.score_grouping as
select
grouping__id, ---grouping__id是两个下划线
class,
sex,
course,
avg(score)
from
tableName
group by --group by包含所需所有维度字段
class,
sex,
course -------此处无逗号
grouping sets
(
(class, course),
( class,sex),
(sex,course),
(course)
)
select *
from temp.score_grouping
where grouping__id in ('2','6')
说明:
1、select子句中的GROUPING__ID是两个下划线;
2、group by后面放的字段表示要分组聚合的全部字段;
3、grouping sets前没有逗号;
4、grouping_id的计算:
它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据,若组合中出现即为1,反正则为0,group by 后字段先出现的放在最低位,依次排开:
比如 group by class,sex,course,则二进制的顺序为:course sex class ,grouping sets字段出现则为1,反之则为0,
比如(class, course), 二进制为 101,十进制则为5,则grouping__id为5,同理grouping__id为6,则组合为(sex,course),二进制为110;
实例一:
-- 正确语句
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会报错
)
注意:
1.双表联结的结果出来之后再用grouping sets,即先有t 表,在表t的基础上使用grouping sets函数;
2.sets中的字段不应该含表名;
presto中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)
)
注意:
1、函数grouping要与group by、grouping sets配合使用
2、函数grouping()中列出sets中所有分组涉及的字段,运行后grouing()列生成结果为二进制转化来的十进制数字;,出现为0,不出现为1,按照顺序,早出现的放高位,依次类推;
SELECT origin_state, origin_zip, destination_state, sum(package_weight),
grouping(origin_state, origin_zip, destination_state)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state)---011 3,
(origin_state, origin_zip) --001 1,
(destination_state));---110 6
origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California | NULL | NULL | 1397 | 3
New Jersey | NULL | NULL | 225 | 3
New York | NULL | NULL | 3 | 3
California | 94131 | NULL | 60 | 1
New Jersey | 7081 | NULL | 225 | 1
California | 90210 | NULL | 1337 | 1
New York | 10002 | NULL | 3 | 1
NULL | NULL | New Jersey | 58 | 6
NULL | NULL | Connecticut | 1562 | 6
NULL | NULL | Colorado | 5 | 6
(10 rows)
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)
)
4、不用的分组字段不要在select子句中写出文章来源:https://www.toymoban.com/news/detail-786829.html
-- 与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所有分组组合用到的字段取并集列出文章来源地址https://www.toymoban.com/news/detail-786829.html
到了这里,关于Hive之grouping sets用法详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!