presto和hive中grouping sets的格式不一致问题

背景

遇到的问题,在presto中使用hive中的grouping sets报错

• 报错信息如下

  [1] Query failed (#20220811_003524_00009_hzxre): line 5:5: mismatched input 'grouping'. Expecting: '%', '*', '+', '-', '.', '/', 'AT', '[', '||', <expression> org.antlr.v4.runtime.InputMismatchException
  

• 解决方法
  • 见方式3

需求

• 现有一个t_cookie表,其结构和数据如图
  
• 需求:分别按照月（month）、天（day）、月和天（month,day）统计 来访用户cookieid个数
• 结果如图
  

解决需求

方式1: 写三个SQl语句

select month,count(1) cnt_num from t_cookie group by month;
select day,count(1) cnt_num from t_cookie group by day;
select month,day,count(1) cnt_num from t_cookie group by month,day;

方式2: 用 union all 把三个表的数据联合到一起

注意: union all 要求字段的类型,个数要完全一致. 所以在前两条SQl中用null来补齐一个字段

select month, null day, count(1) cnt_num from t_cookie group by month    -- null 后面为列的别名
union all
select null month, day,count(1) cnt_num from t_cookie group by day
union all
select month,day,count(1) cnt_num from t_cookie group by month,day;

方式3: 使用增强函数 grouping sets

grouping sets功能, 根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL

• 其语法格式,可以在Hive官网中查看 增强函数
  

Hive SQL的写法

select month,day,count(1) cnt_num
from t_cookie
group by
    month, day        
grouping sets(month, day, (month, day));
-- 注意这里 "group by month, day " 的day后面没有逗号, 正如官方文档中: "group by a,b" 的b后面没有逗号
-- 加了逗号会报错

presto的写法

一般hive中的sql都可以直接在presto中运行,但是上面grouping sets的代码如果在presto直接运行会报错

写法1: group后面什么都不用写.

SQL会按照grouping sets函数中写的维度组合情况来进行分组查询

select
    month,day,count(cookieid)
from test.t_cookie
group by     -- 后面什么都不用写,  按照grouping sets函数中写的维度组合情况来进行分组查询
grouping sets (month,day,(month,day));

写法2: group by month, day后面加个逗号

不清楚是什么原理,反正这样就不报错了. 建议使用写法1中的方式

select
    month,day,count(cookieid)
from test.t_cookie
group by month,day,  
    grouping sets (month,day,(month,day));

方式4: 使用增强函数cube

select
    month,day,count(1) cnt_num
from test.t_cookie
group by     
cube(month, day);   -- 实现多个维度的任意方式组合, 组合方式共有 2的维度数的次方