Hive cube / rollup / grouping sets/GROUPING__ID用法

Love The Way You Lie 2021-09-25 03:56 556阅读 0赞

表结构如下：

CREATE EXTERNAL TABLE \`tmp.shop\_detail\_tb\`(  
\`id\` string COMMENT '',  
\`name\` string COMMENT '',  
\`type\` string COMMENT '',  
\`amt\` string COMMENT '',  
\`month\` string COMMENT ''  
)  
PARTITIONED BY (  
\`dt\` string)  
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '\\t'  
NULL DEFINED AS ''  
STORED AS INPUTFORMAT  
'org.apache.hadoop.mapred.TextInputFormat'  
OUTPUTFORMAT  
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

1    李一包子    外卖    34000    201909  
1    李一包子    堂食    30000    201909  
2    李二面条    外卖    29000    201909  
2    李二面条    堂食    40000    201909  
3    李三火锅    外卖    12000    201909  
3    李三火锅    堂食    90000    201909  
4    李四火锅    外卖    40000    201909  
5    李五火锅    堂食    50000    201909

group by a,b grouping sets((a,b),(a),(b),()) 等价于 group by a,b union all  group by a union all  group by b union all  sum()无group by

group by a,b with cube 等价于 group by a,b union all  group by a union all  group by b union all  sum()无group by  【cube 2个字段 ，是 union all group by 4次；cube 3个字段 ，是 union all group by 8次；指数级的】

group by a,b with rollup 等价于 group by a,b union all  group by a  union all  sum()无group by  --【无union all  group by b】【rollup 2个字段 ，是 union all group by 3次;rollup 3个字段 ，是 union all group by 4次; 线性的】

cube 是 grouping sets的特例；

rollup是cube的特例【rollup只group by 第一个字段开头的字段组合】【rullup函数是cube的子集,以最左侧维度为主,按照顺序依次进行聚合.】

### 原表：  ###

select * from tmp.shop_detail_tb where dt = '2021-01-30' order by id ; --  res5

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70][]

### 1 grouping sets 用法： ###

select 
        name,
        type,
        sum(amt)
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  
        grouping sets((name),(type)) ; --  res3

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1][]

### 2 GROUPING\_\_ID 用法: ###

select 
          name,
          type,
          sum(amt),
          GROUPING__ID
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  
        grouping sets((name),(type)) 
        order by GROUPING__ID ; -- 两个_   ,res4

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 2][] ###

### 3 cube用法 第一次： NULL是总计 ###

select 
        grouping__id,
        name,
        type,
        sum(amt)
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  WITH CUBE;  -- res11

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 3][]

### 3 cube用法 第二次： ###

select 
          grouping__id as id,
          COALESCE(name,'总计') as name,
          COALESCE(type,'总计') as type,
          sum(amt)
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  WITH CUBE
        order by id ; -- res10

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 4][]

### 4 rollup用法： ###

select 
        name,
        type,
        sum(amt)
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  WITH ROLLUP; -- res9

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 5][]

### 以下用法是错误的： ###

select 
    if(grouping(name)=1, 'ALL', name) name, -- Hive2.0 没有grouping 用法
    if(grouping(type)=1, 'ALL', type) type,
          sum(amt)
    from tmp.shop_detail_tb
    where dt = '2021-01-30' group by name,type  
    grouping sets((name),(type)) ; --  FAILED: SemanticException Line 0:-1 Invalid function 'grouping'
    
    
    
    select 
          name,
          type,
          sum(amt),
          grouping_id -- 是两个_
        from tmp.shop_detail_tb
        where dt = '2021-01-30' group by name,type  
        grouping sets((name),(type)) ; -- FAILED: SemanticException [Error 10025]: Line 5:6 Expression not in GROUP BY key 'grouping_id'
    
    
     select 
          name,
          type,
          sum(amt),
          GROUPING__ID
        from tmp.shop_detail_tb
        where dt = '2021-01-30'
        grouping sets((name),(type)) 
        group by name,type   -- 先写group by ，后写grouping sets
        order by GROUPING__ID ; -- FAILED: ParseException line 8:4 missing EOF at 'grouping' near ''2021-01-30''
    
    
    
    select 
        if(grouping(name)=1, 'ALL', name) name,
        if(grouping(type)=1, 'ALL', type) type,
        count(1)
        from tmp.shop_detail_tb
    where dt = '2021-01-30' 
    group by
        cube(name, type) --- cube无此用法， FAILED: SemanticException Line 0:-1 Invalid function 'cube'

Hive 2.0.0

\--------------------

1 GROUPING SETS

该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包含()数据集，则表示整体聚合。

示例：

select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
    from employee
    group by name, work_space[0]
    GROUPING SETS((name,work_space[0]), name, ());
    
    // 上面语句与下面语句等效
    
    select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
    from employee
    group by name, work_space[0]
    UNION ALL
    select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
    from employee
    group by name
    UNION ALL
    select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
    from employee;

2 ROLLUP

扩展了GROUTING SETS。

select a, b, c from table group by a, b, c WITH ROLLUP;
    // 等价于下面语句
    select a, b, c from table group by a, b, c
    GROUPING SETS((a,b,c),(a,b),(a),());

3 CUBE

扩展了GROUTING SETS，对各种条件进行聚合。

select a, b, c from table group by a, b, c WITH ROLLUP;
    // 等价于下面语句
    select a, b, c from table group by a, b, c
    GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),());

参考 https://www.jianshu.com/p/9502e1d58f45

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70]: /images/20210923/15d667782b8443aebddaf72731d8ebcb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1]: /images/20210923/2e79f77b6e16405585665898ae1e4286.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 2]: /images/20210923/8df5683b8db9498384df5237190453d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 3]: /images/20210923/a0de093cff7b468085233151c99de8f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 4]: /images/20210923/84bcdd2e653848619bec38f96ad7c30c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 5]: /images/20210923/eac4946818cb4f6385d36ef1661a0810.png