【spark】spark sql中grouping sets新增纬度怎么使得分组id不改变

男娘i 2021-09-25 05:36 307阅读 0赞

最近在项目中用spark sql做离线开发遇到许多纬度组合去重统计的需求，第一想法就是用grouping sets进行纬度组合，grouping\_id()做为分组id实现需求。  
spark sql的grouping sets和hive的区别主要在于分组id计算方式不一样：

1.  spark sql中用grouping\_id()获取分组id，而hive通过grouping\_\_id(两个\_)获取分组id
2.  spark sql分组id是纬度被选中则为0，没被选中则为1，而hive则刚好相反，选中为1，没被选中为0  
    举例说明：

--spark sql
    select grouping_id() group_id
      from temp_tb
    group by a, b, c, d
    grouping sets (
      (a, b),  --0011=1+2=3
      (a, c),  --0101=1+4=5
      (b, d),  --1010=2+8=10
      (a, d)   --0110=2+4=6
    )
    ;
    
    --hive sql
    select grouping_id() group_id
      from temp_tb
    group by a   --1
            ,b   --2
            ,c   --4
            ,d   --8
    grouping sets (
      (a, b),  --0011=1+2=3
      (a, c),  --0101=1+4=5
      (b, d),  --1010=2+8=10
      (a, d)   --1001=1+8=9
    )
    ;

会发现(a, d)组合id不一样，其他一样的是刚好这么算下来相同。hive的grouping sets用法详情请看我以前博客。

[hive中group by的增强特性：grouping\_\_id,grouping,groupin sets,cube,rollup][hive_group by_grouping_id_grouping_groupin sets_cube_rollup]

从上面的例子中会发现hive在group by**已有的字段后面再新增字段**(在以前的字段前面或后面新增以前组合id就会改变)和纬度组合，以前组合id始终不会改变，但spark sq则在group by后无论哪个字段后新增字段都会改变以前的组合id，那下游任务所用的的条件就需要跟随修改，这样新增纬度会很麻烦。

遇到这种问题怎么样才能使分组id不随着字段的增加而改变呢？

思路：重新拼成二进制码再转换为十进制id，二进制码拼成方式和hive的一样

直接上代码解释

-----首先四个字段进行纬度组合
    select --为保证前期group_id不变，后续添加的纬度字段请在最前面添加，顺序和group by字段的顺序相反
           conv(cast(
                  concat(if(d is not null, 1, 0), if(c is not null, 1, 0)
                        ,if(b is not null, 1, 0), if(a is not null, 1, 0))
           as int), 2, 10) as group_id
      from 
      ( --先将纬度字段空值处理未非空
        select nvl(a, '') as a
              ,nvl(b, '') as b
              ,nvl(c, '') as c
              ,nvl(d, '') as d
          from temp_tb
      ) tt
    group by a    --1
            ,b    --2
            ,c    --4
            ,d    --8
    grouping sets (
      (a, b),  --1+2=3
      (a, c),  --1+4=5
      (b, d),  --2+8=10
      (a, d)   --1+8=9
    )
    ;

在原四个字段的基础上新增字段

-----在原四个字段的基础上新增字段
    select --为保证前期group_id不变，后续添加的纬度字段请在最前面添加，顺序和group by字段的顺序相反
           conv(cast(
                  concat(if(f is not null, 1, 0), if(e is not null, 1, 0)
                        ,if(d is not null, 1, 0), if(c is not null, 1, 0)
                        ,if(b is not null, 1, 0), if(a is not null, 1, 0))
           as int), 2, 10) as group_id
      from 
      ( --先将纬度字段空值处理未非空，因为后续拼二进制码是需要通过非空判断
        select nvl(a, '') as a
              ,nvl(b, '') as b
              ,nvl(c, '') as c
              ,nvl(d, '') as d
              ,nvl(e, '') as e
              ,nvl(f, '') as f
          from temp_tb
      ) tt
    group by a    --1
            ,b    --2
            ,c    --4
            ,d    --8
            ,e    --16
            ,f    --32
    grouping sets (
      (a, b),  --1+2=3
      (a, c),  --1+4=5
      (b, d),  --2+8=10
      (a, d),  --1+8=9
      (a, e),  --1+16=17
      (e, f),  --16+32=48
      (d, f)   --8+32=40
    )
    ;

这样就算后续需求要新增纬度，下游使用分组id也不需要同步修改过滤条件，因为以前的分组id并没有改变。

[hive_group by_grouping_id_grouping_groupin sets_cube_rollup]: https://blog.csdn.net/lz6363/article/details/88095541