【Hive报错】Grouping sets aggregations (with rollups or cubes) are not allowed if aggregation function..

最近遇到一个关于Grouping sets的报错，为了不涉密，这边用模拟了一条sql，如下：

报错的sql语句：

select 
    class
    ,info['type'] as type
    ,GROUPING__ID
    ,count(distinct case when info['num'] <> 0 then id end) as cnt
from
    (
    select
        class
        ,info
        ,id
    from table_xxx
    ) a
group by 
    class
    ,info['type']
grouping sets ( (class, info['type']), (class) 

报错信息：

然后报错了：Grouping sets aggregations (with rollups or cubes) are not allowed if aggregation function parameters overlap with the aggregation functions columns

翻译：如果聚合函数参数与聚合函数列重叠，则不允许分组集聚合（使用汇总或多维数据集）

其实就是说 Grouping sets 里面的字段info和sum case when 里面的info字段重叠了，导致报错。

解决方法：

可以把重叠的字段写到子查询里面。

sql如下：

select 
    class
    ,type
    ,GROUPING__ID
    ,count(distinct case when info['num'] <> 0 then id end) as cnt
from
    (
    select
        class
        ,info['type'] as type
        ,info
        ,id
    from table_xxx
    ) a
group by 
    class
    ,type
grouping sets ( (class, type), (class) )