GROUPING SETS特殊用法之grouping__id

原创已于 2024-10-22 21:40:41 修改 · 1k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #hive #grouping sets #多维度分析 #数仓

于 2023-05-11 19:35:01 首次发布

文章介绍了在SQL中使用GROUPINGSETS和grouping__id进行多级商品类目汇总的方法。由于不能使用rollup和cube来避免‘全部’ID和名称不匹配的问题，文章详细解释了如何利用grouping__id的按位运算特性，根据不同的二进制位判断是否显示全部，以保证各级类目信息的同步。

GROUPING SETS用法之grouping__id

场景描述

根据月份，多级商品类目(包括id 和 对应的名称) 汇总商品数

当前场景使用不了 rollup 以及 cube ,原因是这里有同级商品类别的id和name，需要保持同步，如果使用cube或者rollup,''
则会出现id,'全部' 或者 '全部',id 的情况出现，但是用grouping__id可以避免这种情况的发生

具体sql代码及代码解析


具体的sql:
SELECT 
     IF(GROUPING__ID & 4 = 4, 'ALL' ,lv3_goods_id)           AS  lv3_goods_id  
    ,IF(GROUPING__ID & 4 = 4, '全部',MAX(lv3_goods_name))    AS  lv3_goods_name
    ,IF(GROUPING__ID & 2 = 2, 'ALL' ,lv2_goods_id)           AS  lv2_goods_id  
    ,IF(GROUPING__ID & 2 = 2, '全部',MAX(lv2_goods_name))    AS  lv2_goods_name
    ,IF(GROUPING__ID & 1 = 1, 'ALL' ,lv1_goods_id)           AS  lv1_goods_id  
    ,IF(GROUPING__ID & 1 = 1, '全部',MAX(lv1_goods_name))    AS  lv1_goods_name
    ,count(distinct lv4_goods_id) as lv4_goods_num 
FROM 
    (
    SELECT
         lv1_goods_id
        ,lv1_goods_name
        ,lv2_goods_id
        ,lv2_goods_name
        ,lv3_goods_id
        ,lv3_goods_name
        ,lv4_goods_id
    FROM database.goods_info
    ) t1
GROUP BY 
 lv3_goods_id
,lv2_goods_id
,lv1_goods_id
     GROUPING SETS
     (
         (lv1_goods_id)   
        ,(lv1_goods_id,lv2_goods_id)
        ,(lv1_goods_id,lv2_goods_id,lv3_goods_id)
		,()
     ) 

-------------------------------------- 代码解析 --------------------------------------
4的二进制
0000 0100


-- 跟下面的group by 的字段位置有关
 GROUP BY   
     lv3_goods_id   
    ,lv2_goods_id
    ,lv1_goods_id
      GROUPING SETS
      (
         (lv1_goods_id)   
        ,(lv1_goods_id,lv2_goods_id)
        ,(lv1_goods_id,lv2_goods_id,lv3_goods_id)
	,()
      ) 
		 
		 
四种分组对应的二进制  按位取反  取反后的二进制  我认为取反之后 为1的这个位置是汇总列，所以只有这一列为1，那么就是对应的全部，所以采用按位与的方式
001                   ==>                        110
011                   ==>                        100
111                   ==>                        000
000	              ==>                        111


-- 对于下面的判断
 ,IF(GROUPING__ID & 4 = 4, 'ALL' ,lv3_goods_id)           AS  lv3_goods_id  
 ,IF(GROUPING__ID & 4 = 4, '全部',MAX(lv3_goods_name))    AS  lv3_goods_name
 ,IF(GROUPING__ID & 2 = 2, 'ALL' ,lv2_goods_id)           AS  lv2_goods_id  
 ,IF(GROUPING__ID & 2 = 2, '全部',MAX(lv2_goods_name))    AS  lv2_goods_name
 ,IF(GROUPING__ID & 1 = 1, 'ALL' ,lv1_goods_id)           AS  lv1_goods_id  
 ,IF(GROUPING__ID & 1 = 1, '全部',MAX(lv1_goods_name))    AS  lv1_goods_name
		
		
-- 举例说明 GROUPING__ID & 4 = 4 
4的二进制是 0000 0100 

GROUPING__ID & 4 = 4 

下面四种 按位与 4 后 等于 0000 0100 的组合是    (lv1_goods_id),(lv1_goods_id,lv2_goods_id),()         
0110
0100
0111

即对一级，一级+二级，全部汇总 三种角度去汇总数据时，三级类目使用 "全部" 表示

在这里插入图片描述