Hive分析窗口函数 GROUPING SETS、GROUPING__ID、CUBE、ROLLUP使用说明和示例

朱雀 2023-01-13 06:14 176阅读 0赞

**Hive分析窗口函数 GROUPING SETS、GROUPING\_\_ID、CUBE、ROLLUP使用说明和示例**

## 0、概述 ##

*  GROUPING SETS：根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
 *  GROUPING\_\_ID：表示结果属于哪一个分组集合，属于虚字段
 *  CUBE：根据GROUP BY的维度的所有组合进行聚合。
 *  ROLLUP：为CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数,数量。

首先要弄明白rollup 和cube，就要知道group by的用法，group by 为对列进行分组，只展现分组统计的值，而rollup 为分层次展现，cube为展现列中所有层次，听我娓娓道来。

## 1、GROUPING SETS和GROUPING\_\_ID ##

说明：在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALLGROUPING\_\_ID，表示结果属于哪一个分组集合。  
grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

> select  
>     A,  
>     B,  
>     C,  
>     GROUPING\_\_ID,   
>     count(A)  
> from  
>     tableName  
> group by  --declare columns  
>     A,  
>     B,  
>     C  
> grouping sets  
> (  
>    (A,C),  
>    (A,B),  
>    (B,C),  
>    (C)  
> )

**其中grouping sets中的(A,C),   (A,B),   (B,C),   (C) 代表4个group by 组合， 相当于写了四个sql查询语句使用了四个不同的group by策略。**  
       group\_id是为了区分每条输出结果是属于哪一个group by的数据。它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据。 比如（A,C）的 GROUPING\_\_ID： GROUPING\_\_ID(A,C) = GROUPING\_\_ID(A)+GROUPING\_\_ID(B)+GROUPING\_\_ID(C) 的结果就是：二进制： 010 也就是2.  
       select中的字段是完整的A,B,C，但是我们知道由于group by的存在，select 字段本不应该出现非group by字段的，所以这里我们要特别说明，如果解释器发现group by A,C 但是select A,B,C 那么运行时会将所有from 表取出的结果复制一份，B都置为null，也就是在结果中，B都为null。

### 1.1、原始数据准备 ###

测试数据生成参见博客 [spark-sql创建时间维度表][spark-sql]

spark-sql> select * from xj_dim.dim_date_df where year_name=2021 limit 10;
    dt    new_dt    dt_name    year_name    month_name    half_month    day_name    year_month    month_first_day    month_last_day    quarter    cn_quarter    en_quarter    week_number    year_week_number    week_first_day    week_last_day    daynumber_of_week    en_dayname_of_week    cn_dayname_of_week    daynumber_of_year
    20210525    2021-05-25    2021年5月25日    2021    5    2021年05月下半月    25    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    2    Tuesday    星期二    145
    20210526    2021-05-26    2021年5月26日    2021    5    2021年05月下半月    26    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    3    Wednesday    星期三    146
    20210527    2021-05-27    2021年5月27日    2021    5    2021年05月下半月    27    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    4    Thursday    星期四    147
    20210528    2021-05-28    2021年5月28日    2021    5    2021年05月下半月    28    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    5    Friday    星期五    148
    20210529    2021-05-29    2021年5月29日    2021    5    2021年05月下半月    29    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    6    Saturday    星期六    149
    20210530    2021-05-30    2021年5月30日    2021    5    2021年05月下半月    30    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    21    2021年第21周    2021-05-24    2021-05-30    7    Sunday    星期日    150
    20210531    2021-05-31    2021年5月31日    2021    5    2021年05月下半月    31    202105    2021-05-01    2021-05-31    2    2021年第2季度    2021Q2    22    2021年第22周    2021-05-31    2021-06-06    1    Monday    星期一    151
    20210601    2021-06-01    2021年6月1日    2021    6    2021年06月上半月    1    202106    2021-06-01    2021-06-30    2    2021年第2季度    2021Q2    22    2021年第22周    2021-05-31    2021-06-06    2    Tuesday    星期二    152
    20210602    2021-06-02    2021年6月2日    2021    6    2021年06月上半月    2    202106    2021-06-01    2021-06-30    2    2021年第2季度    2021Q2    22    2021年第22周    2021-05-31    2021-06-06    3    Wednesday    星期三    153
    20210603    2021-06-03    2021年6月3日    2021    6    2021年06月上半月    3    202106    2021-06-01    2021-06-30    2    2021年第2季度    2021Q2    22    2021年第22周    2021-05-31    2021-06-06    4    Thursday    星期四    154
    Time taken: 2.314 seconds, Fetched 10 row(s)

### 1.2、对所有明细数据按年份、月份、年月，三者分别汇总，获取其汇总结果 ###

select GROUPING__ID,year_name,month_name,count(*) as num 
    from xj_dim.dim_date_df 
    where year_name=2021 
    group by year_name,month_name 
    grouping sets (year_name
                  ,month_name
                  ,(year_name,month_name)       
                  ) 
    order by GROUPING__ID;
    
    grouping__id    year_name    month_name    num
    0    2021    9    30
    0    2021    6    30
    0    2021    11    30
    0    2021    12    31
    0    2021    10    31
    0    2021    2    28
    0    2021    7    31
    0    2021    5    31
    0    2021    4    30
    0    2021    8    31
    0    2021    3    31
    0    2021    1    31
    1    2021    NULL    365
    2    NULL    10    31
    2    NULL    2    28
    2    NULL    12    31
    2    NULL    1    31
    2    NULL    7    31
    2    NULL    9    30
    2    NULL    8    31
    2    NULL    6    30
    2    NULL    3    31
    2    NULL    5    31
    2    NULL    4    30
    2    NULL    11    30
    Time taken: 11.01 seconds, Fetched 25 row(s)

### 1.3、对所有明细数据按年份+月份、年份+季度、季度+月份，月份 四者分别汇总，获取其汇总结果 ###

select GROUPING__ID,year_name,cn_quarter,month_name,count(*) as num 
    from dim_date_df 
    where year_name=2021 
    group by year_name,cn_quarter,month_name 
    grouping sets ((year_name,month_name)
                  ,(year_name,cn_quarter)
                  ,(cn_quarter,month_name)
                  ,(month_name)
                  ,(year_name,cn_quarter,month_name)
                  ) 
    order by GROUPING__ID;
    
    grouping__id    year_name    cn_quarter    month_name    num
    0    2021    2021年第3季度    8    31
    0    2021    2021年第3季度    7    31
    0    2021    2021年第4季度    11    30
    0    2021    2021年第4季度    12    31
    0    2021    2021年第2季度    4    30
    0    2021    2021年第2季度    5    31
    0    2021    2021年第1季度    2    28
    0    2021    2021年第2季度    6    30
    0    2021    2021年第1季度    1    31
    0    2021    2021年第3季度    9    30
    0    2021    2021年第4季度    10    31
    0    2021    2021年第1季度    3    31
    1    2021    2021年第3季度    NULL    92
    1    2021    2021年第1季度    NULL    90
    1    2021    2021年第2季度    NULL    91
    1    2021    2021年第4季度    NULL    92
    2    2021    NULL    1    31
    2    2021    NULL    2    28
    2    2021    NULL    11    30
    2    2021    NULL    3    31
    2    2021    NULL    4    30
    2    2021    NULL    12    31
    2    2021    NULL    10    31
    2    2021    NULL    9    30
    2    2021    NULL    7    31
    2    2021    NULL    6    30
    2    2021    NULL    5    31
    2    2021    NULL    8    31
    4    NULL    2021年第3季度    9    30
    4    NULL    2021年第4季度    11    30
    4    NULL    2021年第4季度    12    31
    4    NULL    2021年第3季度    8    31
    4    NULL    2021年第3季度    7    31
    4    NULL    2021年第1季度    3    31
    4    NULL    2021年第1季度    1    31
    4    NULL    2021年第2季度    6    30
    4    NULL    2021年第2季度    4    30
    4    NULL    2021年第1季度    2    28
    4    NULL    2021年第4季度    10    31
    4    NULL    2021年第2季度    5    31
    6    NULL    NULL    11    30
    6    NULL    NULL    5    31
    6    NULL    NULL    10    31
    6    NULL    NULL    4    30
    6    NULL    NULL    3    31
    6    NULL    NULL    12    31
    6    NULL    NULL    6    30
    6    NULL    NULL    8    31
    6    NULL    NULL    9    30
    6    NULL    NULL    1    31
    6    NULL    NULL    7    31
    6    NULL    NULL    2    28
    Time taken: 5.746 seconds, Fetched 52 row(s)

**GROUPING\_\_ID 解释**  
**GROUPING\_\_ID是hive生成的，是标识维度组合的十进制数字。**

以上面的例子说明 Grouping\_\_ID 的生成规则：

group by 所有字段中，如果在 grouping sets 的组合中存在的字段使用 0 表示，没有使用则使用 1 表示

year\_name,cn\_quarter,month\_name

*  year\_name,cn\_quarter,month\_name  0 000
 *  year\_name,cn\_quarter                       1 001
 *  year\_name,month\_name                   2 010
 *  cn\_quarter,month\_name                    4 100
 *  month\_name                                      6 110

### 1.4、grouping sets例子 ###

select GROUPING__ID,year_name,cn_quarter,month_name,count(*) as num 
    from dim_date_df 
    where year_name=2021 
    group by year_name,cn_quarter,month_name 
    grouping sets (year_name
                  ,cn_quarter
                  ,month_name
                  ) 
    order by GROUPING__ID;
    
    grouping__id    year_name    cn_quarter    month_name    num
    3    2021    NULL    NULL    365
    5    NULL    2021年第3季度    NULL    92
    5    NULL    2021年第4季度    NULL    92
    5    NULL    2021年第1季度    NULL    90
    5    NULL    2021年第2季度    NULL    91
    6    NULL    NULL    11    30
    6    NULL    NULL    5    31
    6    NULL    NULL    10    31
    6    NULL    NULL    4    30
    6    NULL    NULL    3    31
    6    NULL    NULL    12    31
    6    NULL    NULL    6    30
    6    NULL    NULL    8    31
    6    NULL    NULL    9    30
    6    NULL    NULL    1    31
    6    NULL    NULL    7    31
    6    NULL    NULL    2    28
    Time taken: 4.761 seconds, Fetched 17 row(s)

## 2、CUBE 和 ROLLUP ##

**CUBE：根据GROUP BY的维度的所有组合进行聚合。  
ROLLUP：为CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。**

### 2.1、 rollup示例 ###

select GROUPING__ID,year_name,cn_quarter,month_name,count(*) as num 
    from dim_date_df 
    where year_name=2021 
    group by rollup(year_name,cn_quarter,month_name)
    order by GROUPING__ID;
    
    grouping__id    year_name    cn_quarter    month_name    num
    0    2021    2021年第3季度    8    31
    0    2021    2021年第3季度    7    31
    0    2021    2021年第4季度    11    30
    0    2021    2021年第4季度    12    31
    0    2021    2021年第2季度    4    30
    0    2021    2021年第2季度    5    31
    0    2021    2021年第1季度    2    28
    0    2021    2021年第2季度    6    30
    0    2021    2021年第1季度    1    31
    0    2021    2021年第3季度    9    30
    0    2021    2021年第4季度    10    31
    0    2021    2021年第1季度    3    31
    1    2021    2021年第3季度    NULL    92
    1    2021    2021年第1季度    NULL    90
    1    2021    2021年第2季度    NULL    91
    1    2021    2021年第4季度    NULL    92
    3    2021    NULL    NULL    365
    7    NULL    NULL    NULL    365
    Time taken: 2.963 seconds, Fetched 18 row(s)

这是就回到了开始所说的，rollup为分层次展现，怎么个分层次法呢？

*  grouping\_\_id=0 代表 year\_name,cn\_quarter,month\_name
 *  grouping\_\_id=1 代表 year\_name,cn\_quarter
 *  grouping\_\_id=3 代表 year\_name
 *  grouping\_\_id=4 代表 所有

其中第一行为group by出来的，接下来的三行即为分层次汇总统计，而最后的空白的一行为对所有数据进行汇总统计。

### 2.2、cube示例 ###

说完rollup，开始说cube

开始说cube为展现所有层次，这是什么意思呢？就是有这里面所有的分组汇总统计

select GROUPING__ID,year_name,cn_quarter,month_name,count(*) as num 
    from dim_date_df 
    where year_name=2021 
    group by cube(year_name,cn_quarter,month_name)
    order by GROUPING__ID;
    
    grouping__id    year_name    cn_quarter    month_name    num
    0    2021    2021年第3季度    8    31
    0    2021    2021年第3季度    7    31
    0    2021    2021年第4季度    11    30
    0    2021    2021年第4季度    12    31
    0    2021    2021年第2季度    4    30
    0    2021    2021年第2季度    5    31
    0    2021    2021年第1季度    2    28
    0    2021    2021年第2季度    6    30
    0    2021    2021年第1季度    1    31
    0    2021    2021年第3季度    9    30
    0    2021    2021年第4季度    10    31
    0    2021    2021年第1季度    3    31
    1    2021    2021年第3季度    NULL    92
    1    2021    2021年第1季度    NULL    90
    1    2021    2021年第2季度    NULL    91
    1    2021    2021年第4季度    NULL    92
    2    2021    NULL    1    31
    2    2021    NULL    2    28
    2    2021    NULL    11    30
    2    2021    NULL    3    31
    2    2021    NULL    4    30
    2    2021    NULL    12    31
    2    2021    NULL    10    31
    2    2021    NULL    9    30
    2    2021    NULL    7    31
    2    2021    NULL    6    30
    2    2021    NULL    5    31
    2    2021    NULL    8    31
    3    2021    NULL    NULL    365
    4    NULL    2021年第3季度    9    30
    4    NULL    2021年第4季度    11    30
    4    NULL    2021年第4季度    12    31
    4    NULL    2021年第3季度    8    31
    4    NULL    2021年第3季度    7    31
    4    NULL    2021年第1季度    3    31
    4    NULL    2021年第1季度    1    31
    4    NULL    2021年第2季度    6    30
    4    NULL    2021年第2季度    4    30
    4    NULL    2021年第1季度    2    28
    4    NULL    2021年第4季度    10    31
    4    NULL    2021年第2季度    5    31
    5    NULL    2021年第3季度    NULL    92
    5    NULL    2021年第4季度    NULL    92
    5    NULL    2021年第1季度    NULL    90
    5    NULL    2021年第2季度    NULL    91
    6    NULL    NULL    11    30
    6    NULL    NULL    5    31
    6    NULL    NULL    10    31
    6    NULL    NULL    4    30
    6    NULL    NULL    3    31
    6    NULL    NULL    12    31
    6    NULL    NULL    6    30
    6    NULL    NULL    8    31
    6    NULL    NULL    9    30
    6    NULL    NULL    1    31
    6    NULL    NULL    7    31
    6    NULL    NULL    2    28
    7    NULL    NULL    NULL    365
    Time taken: 4.676 seconds, Fetched 58 row(s)

## 3、处理空值，使用 grouping()函数 ##

**此时有一个方法为grouping() 可以查看该值是否有rollup|cube产生的，若为其值则为1，否则为0，使用一个case when或者if函数判断一下就好。**

### 3.1、空值示例 ###

**下面为使用cube时，可以看出每个有cube产生合计，即空值处，其grouping的值都为1**

select GROUPING__ID,year_name,cn_quarter,month_name,count(*) as num,GROUPING(year_name),GROUPING(cn_quarter),GROUPING(month_name) 
    from dim_date_df 
    where year_name=2021 
    group by rollup(year_name,cn_quarter,month_name)
    order by GROUPING__ID;
    grouping__id    year_name    cn_quarter    month_name    num    grouping(year_name)    grouping(cn_quarter)    grouping(month_name)
    0    2021    2021年第3季度    8    31    0    0    0
    0    2021    2021年第3季度    7    31    0    0    0
    0    2021    2021年第4季度    11    30    0    0    0
    0    2021    2021年第4季度    12    31    0    0    0
    0    2021    2021年第2季度    4    30    0    0    0
    0    2021    2021年第2季度    5    31    0    0    0
    0    2021    2021年第1季度    2    28    0    0    0
    0    2021    2021年第2季度    6    30    0    0    0
    0    2021    2021年第1季度    1    31    0    0    0
    0    2021    2021年第3季度    9    30    0    0    0
    0    2021    2021年第4季度    10    31    0    0    0
    0    2021    2021年第1季度    3    31    0    0    0
    1    2021    2021年第3季度    NULL    92    0    0    1
    1    2021    2021年第1季度    NULL    90    0    0    1
    1    2021    2021年第2季度    NULL    91    0    0    1
    1    2021    2021年第4季度    NULL    92    0    0    1
    3    2021    NULL    NULL    365    0    1    1
    7    NULL    NULL    NULL    365    1    1    1
    Time taken: 6.197 seconds, Fetched 18 row(s)

### 3.2、空值处理例子 ###

select GROUPING__ID,if(GROUPING(year_name)=0,year_name,'year'),if(GROUPING(cn_quarter)=0,cn_quarter,'quarter'),if(GROUPING(month_name)=0,month_name,'month'),count(*) as num
    from dim_date_df 
    where year_name=2021 
    group by rollup(year_name,cn_quarter,month_name)
    order by GROUPING__ID;
    
    grouping__id    (IF((CAST(grouping(year_name) AS INT) = 0), CAST(year_name AS STRING), year))    (IF((CAST(grouping(cn_quarter) AS INT) = 0), cn_quarter, quarter))    (IF((CAST(grouping(month_name) AS INT) = 0), CAST(month_name AS STRING), month))    num
    0    2021    2021年第3季度    8    31
    0    2021    2021年第3季度    7    31
    0    2021    2021年第4季度    11    30
    0    2021    2021年第4季度    12    31
    0    2021    2021年第2季度    4    30
    0    2021    2021年第2季度    5    31
    0    2021    2021年第1季度    2    28
    0    2021    2021年第2季度    6    30
    0    2021    2021年第1季度    1    31
    0    2021    2021年第3季度    9    30
    0    2021    2021年第4季度    10    31
    0    2021    2021年第1季度    3    31
    1    2021    2021年第3季度    month    92
    1    2021    2021年第1季度    month    90
    1    2021    2021年第2季度    month    91
    1    2021    2021年第4季度    month    92
    3    2021    quarter    month    365
    7    year    quarter    month    365
    Time taken: 9.146 seconds, Fetched 18 row(s)

参考：https://www.cnblogs.com/springy/p/9375236.html

[spark-sql]: https://blog.csdn.net/helloxiaozhe/article/details/115760818