Hive分析窗口函数 GROUPING SETS、GROUPING |
您所在的位置：网站首页 › 大数据cube是什么意思呀 › Hive分析窗口函数 GROUPING SETS、GROUPING |
|
Hive分析窗口函数 GROUPING SETS、GROUPING__ID、CUBE、ROLLUP使用说明和示例 0、概述 GROUPING SETS：根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALLGROUPING__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 000year_name,cn_quarter 1 001year_name,month_name 2 010cn_quarter,month_name 4 100month_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 和 ROLLUPCUBE：根据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_namegrouping__id=1 代表 year_name,cn_quartergrouping__id=3 代表 year_namegrouping__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 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |