hive中的集合函数 hive grouping函数的使用方法

转载

imking 2023-09-28 22:49:39

文章标签 hive中的集合函数 hive grouping sets cube rollup 文章分类 Hive 大数据

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> 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)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：宝塔安装 python 宝塔安装Python插件

下一篇：Dockerfile jvm内存 dockerfile配置jvm参数

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯