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
即对一级,一级+二级,全部汇总 三种角度去汇总数据时,三级类目使用 "全部" 表示