2023.12.14 hive sql的聚合增强函数 grouping set

2023-12-14 00:20
文章标签 sql 函数 set 14 hive 增强 聚合 database grouping 2023.12

本文主要是介绍2023.12.14 hive sql的聚合增强函数 grouping set，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

 

目录

 

1.建库建表

 2.需求

 3.使用union all来完成需求

 4.聚合函数增强 grouping set

 5.聚合增强函数cube ,rollup

 6.rollup翻滚

7.聚合函数增强 -- grouping判断

1.建库建表

-- 建库
create database if not exists test;
use test;
-- 建表
create table test.t_cookie(month string, day string, cookieid string) 
row format delimited fields terminated by ',';
-- 数据样例内容
insert into test.t_cookie values
('2015-03','2015-03-10','cookie1'),
('2015-03','2015-03-10','cookie5'),
('2015-03','2015-03-12','cookie7'),
('2015-04','2015-04-12','cookie3'),
('2015-04','2015-04-13','cookie2'),
('2015-04','2015-04-13','cookie4'),
('2015-04','2015-04-16','cookie4'),
('2015-03','2015-03-10','cookie2'),
('2015-03','2015-03-10','cookie3'),
('2015-04','2015-04-12','cookie5'),
('2015-04','2015-04-13','cookie6'),
('2015-04','2015-04-15','cookie3'),
('2015-04','2015-04-15','cookie2'),
('2015-04','2015-04-16','cookie1');

建表完成后

 2.需求

分别按照月,天,月和天统计来访用户cookieid个数,并获取三者的结果集,一起插到目标宽表中

 3.使用union all来完成需求

  select month,null as day, count(cookieid) cnt from test.t_cookie 
group by monthunion allselect null as month, day,  count(cookieid) cnt from test.t_cookiegroup by dayunion allselect month,day, count(cookieid) cnt from test.t_cookie 
group by month,day;

使用union all的表

--这些指标都是来源from来自了一个表,因为是3个查询后的结果集进行合并
--这样的好处是可以在一个表中直观的看到多个结果
-- union all也单独占用了资源,当维度与指标多的时候,效率会很低
--group month ,day属于一个维度,因为是一起的

 4.聚合函数增强 grouping set

==grouping sets函数解释==:

  1. 就是通过指定的多个维度进行查询的. 即: 你写了哪些维度, 它就按照哪些维度进行聚合计算.

  2. 细节: 维度要用小括号括起来, 如果是1个维度, 小括号可以省略, 但是建议写上.

  3. grouping sets函数在hive中 和 presto中的写法略有不同

  4. 如果是在hive中, group by后边必须要写分组字段, 将来我们可以根据这些 分组字段的不同组合, 形成不同的维度. 如果是在Presto中, group by后边什么都不写, 因为它(presto)会根据你写的 维度, 自动根据字段进行分组.

==grouping sets函数优点==:

  1. 使用grouping sets==只会对表进行一次扫描==。

  2. 使用grouping sets==查询速度吊打==多个分组查询结果union all。

  3. 使用grouping sets==执行结果==与多个分组查询结果union all的结果集==一样==；

--grouping sets聚合增强函数
--依然是查询每月,每天,和月和天来统计用户个数 ,使用hive

        select month,day,count(cookieid)  cntfrom hive_test.t_cookiegroup by month, daygrouping sets ((month,day),(month),(day));select month,day,count(cookieid)  cntfrom hive_test.t_cookiegroup by month, daygrouping sets ((month,day),month,day); --括号可以省略

-- 下面这个是Presto SQL语法支持
 

  select month,day,count(cookieid) from test.t_cookie group bygrouping sets (month,day,(month,day));

总结:

--group by后面需要加维度字段名字

--维度要用小括号括起来,如果grouping set后面不写维度,默认就是所有维度,题目中就会count全部数量,最后结果是14

--grouping set的速度快了5倍,(month),day,month,(),单个指标括号可以省略,

--union all需要从上到下一个一个运行,grouping set 则是并行

 5.聚合增强函数cube ,rollup

以上的grouping set已经可以自定想要分组的维度了,但还是需要自己手动输入分组,那么cube可以只输入指定的原始维度字段,然后他就会考虑到所有维度的组合方式,自动生成所有排列组合情况,

例如: 你传入month, day, 就相当于写了 (), (month), (day), (month,day) 这四个维度

公式：假如说有==N个维度，那么所有维度的组合的个数：2^N==

-- 使用cube函数生成指定维度的所有组合
select month,day,count(cookieid)
from test.t_cookie
group by
cube (month, day);-- 上述sql等价于
select month,day,count(cookieid)
from test.t_cookie
group by
grouping sets ((month,day), month, day, ());

总结:

cube可以自动生成所有排列组合

但是cube默认所有组合,无法自己决定想组合的部分
group by后面不用加上原始维度 ,只有hive中grouping set需要在group by后面加维度字段

 6.rollup翻滚

  • rollup的功能：实现==从右到左多级递减==的统计,显示统计某一层次结构的聚合。

  • ==rollup函数解释==:

    按照你指定的字段, 进行维度组合查询, 它相当于是 cube的子集, cube是所有维度, rollup是部分维度. -- 例如: 你写的维度是a,b, 则组合后的维度有: (a,b), (a), ()

    即: 从右往左多级递减(结论, 记忆)

    写的维度假如是c,b,a 则组合后的维度有(c,b,a) , (c,b) , (c)  , ()

-- rollup的功能：实现从右到左递减多级的统计
select month,day,count(cookieid)
from test.t_cookie
group by
rollup (month,day);  -- (month,day),month,()-- 等价于
select month,day,count(cookieid)
from test.t_cookie
group by
grouping sets ((month,day), (month), ());

7.聚合函数增强 -- grouping判断

grouping的功能,判断当前数据是按照哪个字段来进行分组的,

grouping(维度字段1,维度字段2)

如果分组中有相应字段,则将位设置为0,否则将其设置为1,总之就是有0没1

在语法上,grouping 要求group by后面不能有分组字段,grouping set在hive上运行的时候要求加分组字段,所以 要想grouping和grouping set配合使用,必须在presto上运行,hive不行.

-- 在Presto引擎中进行执行
select month,day,count(cookieid),grouping(month)      as m,grouping(day)        as d,grouping(month, day) as m_d
from test.t_cookie
group by grouping sets (month, day, (month, day));

 有是0,没有是1,group by 
   grouping sets (month, day, (month, day));

第一行中month 和 day都是0,代表这分组没有这两个字段

第二行中,month为1,day为0,说明月是没有的,天是有的, 10是二进制,转为十进制后得到数字2

第三行中,月0日1,说明月有,01是二进制,转为十进制后得到数字1

grouping(month)列为0时，可以看到month列都是有值的，为1时则相反，证明当前行是按照month来进行分组统计的

grouping(day)列为0时,也看到day列有值，为1时day则相反，证明当前行时按照day来进行分组统计的

grouping(month, day)是grouping(month)、grouping(day)二进制数值组合后转换得到的数字

a. 按照month分组，则month=0，day=1，组合后为01，二进制转换为十进制得到数字1；
b. 按照day分组，则month=1，day=0，组合后为10，二进制转换为十进制得到数字2；
c. 同时按照month和day分组，则month=0，day=0，组合后为00，二进制转换为十进制得到数字0。

因此可以使用grouping操作来判断当前数据是按照哪个字段来分组的。

 grouping(日期、城市、商圈、店铺) = 1010(二进制) = 10(十进制)      证明有(城市, 店铺)维度

grouping(日期、城市、商圈、店铺) = 1001 = 9(十进制) 证明有( 城市, 商圈)维度 ​ grouping(日期、城市、商圈、店铺) = 0100 = 4(十进制) 证明有(日期, 商圈, 店铺)维度

这篇关于2023.12.14 hive sql的聚合增强函数 grouping set的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/490483。

相关文章

MySQL中的交叉连接、自然连接和内连接查询详解

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》：本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接（cross join）三、自然连接（naturalandroid join）四
阅读更多...
Mysql如何将数据按照年月分组的统计

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》：本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA
阅读更多...
Mysql表如何按照日期字段的年月分区

Mysql表如何按照日期字段的年月分区

《Mysql表如何按照日期字段的年月分区》：本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区
阅读更多...
mysql的基础语句和外键查询及其语句详解(推荐)

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》：本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键
阅读更多...
MySQL更新某个字段拼接固定字符串的实现

MySQL更新某个字段拼接固定字符串的实现

《MySQL更新某个字段拼接固定字符串的实现》在MySQL中,我们经常需要对数据库中的某个字段进行更新操作,本文就来介绍一下MySQL更新某个字段拼接固定字符串的实现,感兴趣的可以了解一下... 目录1. 查看字段当前值2. 更新字段拼接固定字符串3. 验证更新结果mysql更新某个字段拼接固定字符串 -
阅读更多...
C++ Sort函数使用场景分析

C++ Sort函数使用场景分析

《C++Sort函数使用场景分析》sort函数是algorithm库下的一个函数,sort函数是不稳定的,即大小相同的元素在排序后相对顺序可能发生改变,如果某些场景需要保持相同元素间的相对顺序,可使... 目录C++ Sort函数详解一、sort函数调用的两种方式二、sort函数使用场景三、sort函数排序
阅读更多...
python连接本地SQL server详细图文教程

python连接本地SQL server详细图文教程

《python连接本地SQLserver详细图文教程》在数据分析领域,经常需要从数据库中获取数据进行分析和处理,下面：本文主要介绍python连接本地SQLserver的相关资料,文中通过代码... 目录一.设置本地账号1.新建用户2.开启双重验证3,开启TCP/IP本地服务二js.python连接实例1.
阅读更多...
Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

《SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能》：本文主要介绍SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能,本文分步骤给大家介绍的... 目录原理解析1. mysql主从复制（Master-Slave Replication）2. 读写分离3.
阅读更多...
C语言函数递归实际应用举例详解

C语言函数递归实际应用举例详解

《C语言函数递归实际应用举例详解》程序调用自身的编程技巧称为递归,递归做为一种算法在程序设计语言中广泛应用,：本文主要介绍C语言函数递归实际应用举例的相关资料,文中通过代码介绍的非常详细,需要的朋... 目录前言一、递归的概念与思想二、递归的限制条件 三、递归的实际应用举例（一）求 n 的阶乘（二）顺序打印
阅读更多...
C/C++错误信息处理的常见方法及函数

C/C++错误信息处理的常见方法及函数

《C/C++错误信息处理的常见方法及函数》C/C++是两种广泛使用的编程语言,特别是在系统编程、嵌入式开发以及高性能计算领域,：本文主要介绍C/C++错误信息处理的常见方法及函数,文中通过代码介绍... 目录前言1. errno 和 perror()示例：2. strerror()示例：3. perror(
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2