HiveQL DQL7—高级聚合

最新推荐文章于 2021-04-29 23:40:19 发布
最新推荐文章于 2021-04-29 23:40:19 发布
阅读量240 收藏
点赞数
分类专栏： Hive 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CPP_MAYIBO/article/details/104175674
版权

目录


通过使用 GROUPING SETS, CUBE, 和 ROLLUP，Hive提供了高级聚合功能

Grouping sets

GROUPING SETS可以对同一个数据集执行多个GROUP BY操作。事实上，GROUPING SETS 是一种将多个GROUP BY结果集使用UNION ALL连接起来的简写方式。GROUPING SETS 关键字会在一个作业的一个阶段中执行完成所有的处理，这样会更高效。GROUPING SETS子句中的空白集 () 会计算整个聚合。下面会用几个示例来说明GROUPING SETS 的等价性。为了更好地理解，我们可以说GROUPING SETS的外层括号定义了要执行UNION ALL的数据，内部括号定义了在每个UNION ALL中要执行的GROUP BY 数据。下表是GROUPING SETS查询及其等效的GROUP BY查询的对应表

使用 GROUPING SETS的聚合查询使用GROUP BY的等价的聚合查询
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
UNION
SELECT null, null, SUM( c ) FROM tab1

示例：使用一个字段对元素的GROUPING SETS

> SELECT 
name, 
start_date,
count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name, start_date 
GROUPING SETS((name, start_date));
+----------+-------------+----------+
|   name   | start_date  | sin_cnt  |
+----------+-------------+----------+
| Lucy     | 2010-01-03  | 1        |
| Michael  | 2014-01-29  | 1        |
| Steven   | 2012-11-03  | 1        |
| Will     | 2013-10-02  | 1        |
+----------+-------------+----------+

等价于

> SELECT 
name, 
start_date, 
count(sin_number) AS sin_cnt 
FROM employee_hr
GROUP BY name, start_date;
+----------+-------------+----------+
|   name   | start_date  | sin_cnt  |
+----------+-------------+----------+
| Lucy     | 2010-01-03  | 1        |
| Michael  | 2014-01-29  | 1        |
| Steven   | 2012-11-03  | 1        |
| Will     | 2013-10-02  | 1        |
+----------+-------------+----------+

示例：使用2个元素的GROUPING SETS

> SELECT 
name, start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name, start_date 
GROUPING SETS(name, start_date);
+----------+-------------+----------+
|   name   | start_date  | sin_cnt  |
+----------+-------------+----------+
| NULL     | 2010-01-03  | 1        |
| NULL     | 2012-11-03  | 1        |
| NULL     | 2013-10-02  | 1        |
| NULL     | 2014-01-29  | 1        |
| Lucy     | NULL        | 1        |
| Michael  | NULL        | 1        |
| Steven   | NULL        | 1        |
| Will     | NULL        | 1        |
+----------+-------------+----------+

等价于

> SELECT 
name, null as start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name
UNION ALL
SELECT 
null as name, start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY start_date;
+-----------+-----------------+--------------+
| _u1.name  | _u1.start_date  | _u1.sin_cnt  |
+-----------+-----------------+--------------+
| Lucy      | NULL            | 1            |
| Michael   | NULL            | 1            |
| Steven    | NULL            | 1            |
| Will      | NULL            | 1            |
| NULL      | 2010-01-03      | 1            |
| NULL      | 2012-11-03      | 1            |
| NULL      | 2013-10-02      | 1            |
| NULL      | 2014-01-29      | 1            |
+-----------+-----------------+--------------+

示例：使用2个元素的GROUPING SETS，一个字段对，一个是单独字段

> SELECT 
name, start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name, start_date 
GROUPING SETS((name, start_date), name);
+----------+-------------+----------+
|   name   | start_date  | sin_cnt  |
+----------+-------------+----------+
| Lucy     | NULL        | 1        |
| Lucy     | 2010-01-03  | 1        |
| Michael  | NULL        | 1        |
| Michael  | 2014-01-29  | 1        |
| Steven   | NULL        | 1        |
| Steven   | 2012-11-03  | 1        |
| Will     | NULL        | 1        |
| Will     | 2013-10-02  | 1        |
+----------+-------------+----------+

等价于

> SELECT 
name, start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name, start_date
UNION ALL
SELECT 
name, null as start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name;
+-----------+-----------------+--------------+
| _u1.name  | _u1.start_date  | _u1.sin_cnt  |
+-----------+-----------------+--------------+
| Lucy      | 2010-01-03      | 1            |
| Michael   | 2014-01-29      | 1            |
| Steven    | 2012-11-03      | 1            |
| Will      | 2013-10-02      | 1            |
| Lucy      | NULL            | 1            |
| Michael   | NULL            | 1            |
| Steven    | NULL            | 1            |
| Will      | NULL            | 1            |
+-----------+-----------------+--------------+

示例：使用4个元素的GROUPING SETS

> SELECT 
name, start_date, count(sin_number) as sin_cnt 
FROM employee_hr
GROUP BY name, start_date 
GROUPING SETS((name, start_date), name, start_date, ());
+----------+-------------+----------+
|   name   | start_date  | sin_cnt  |
+----------+-------------+----------+
| NULL     | NULL        | 4        |
| NULL     | 2010-01-03  | 1        |
| NULL     | 2012-11-03  | 1        |
| NULL     | 2013-10-02  | 1        |
| NULL     | 2014-01-29  | 1        |
| Lucy     | NULL        | 1        |
| Lucy     | 2010-01-03  | 1        |
| Michael  | NULL        | 1        |
| Michael  | 2014-01-29  | 1        |
| Steven   | NULL        | 1        |
| Steven   | 2012-11-03  | 1        |
| Will     | NULL        | 1        |
| Will     | 2013-10-02  | 1        |
+----------+-------------+----------+

等价于

> SELECT 
name, start_date, count(sin_number) AS sin_cnt 
FROM employee_hr
GROUP BY name, start_date
UNION ALL
SELECT 
name, null as start_date, count(sin_number) AS sin_cnt 
FROM employee_hr
GROUP BY name
UNION ALL
SELECT 
null as name, start_date, count(sin_number) AS sin_cnt 
FROM employee_hr
GROUP BY start_date
UNION ALL
SELECT 
null as name, null as start_date, count(sin_number) AS sin_cnt 
FROM employee_hr;
+-----------+-----------------+--------------+
| _u1.name  | _u1.start_date  | _u1.sin_cnt  |
+-----------+-----------------+--------------+
| Lucy      | 2010-01-03      | 1            |
| Michael   | 2014-01-29      | 1            |
| Steven    | 2012-11-03      | 1            |
| Will      | 2013-10-02      | 1            |
| Lucy      | NULL            | 1            |
| Michael   | NULL            | 1            |
| Steven    | NULL            | 1            |
| Will      | NULL            | 1            |
| NULL      | 2010-01-03      | 1            |
| NULL      | 2012-11-03      | 1            |
| NULL      | 2013-10-02      | 1            |
| NULL      | 2014-01-29      | 1            |
| NULL      | NULL            | 4            |
+-----------+-----------------+--------------+

Rollup 和 Cube

ROLLUP语句使SELECT语句能够跨指定的维度组计算多级聚合。ROLLUP语句是GROUP BY子句的简单扩展，具有高效性和最小的查询开销。与创建指定聚合级别的GROUPING SETS相比，ROLLUP创建n+1级别的聚合，其中n是分组列数。首先，它会计算GROUP BY子句中指定的标准聚合值。然后，创建一个更高级别的小计（subtotals）,通过分组列的组合列表从右向左移动。如，GROUP BY a,b,c WITH ROLLUP 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(a),())。
CUBE语句接受一组指定的分组列，并为其所有可能的组合创建聚合。如果为CUBE指定了n列，则会返回2n个聚合组合。如，GROUP BY a,b,c WITH CUBE 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),©,())。
GROUPING__ID函数作为扩展来区分整个行和其他行。它返回GROUP BY后指定的每列的BIT向量的十进制值。返回的十进制数是从1和0的二进制数转换而来的，表示列是(1)否(0)在行中聚合。另一方面，grouping(…)函数还通过直接返回二进制1或0来指示GROUP BY子句中的列是否聚合。在下面的示例中，列的顺序从GROUP BY中最近的列（如name）开始计算。结果集中的第一行表明GROUP BY中没有使用任何列。

> SELECT
name, start_date, count(employee_id) as emp_id_cnt,
GROUPING__ID,
grouping(name) as gp_name,
grouping(start_date) as gp_sd
FROM employee_hr
GROUP BY name, start_date
WITH CUBE ORDER BY name, start_date;
+----------+-------------+-------------+---------------+----------+--------+
|   name   | start_date  | emp_id_cnt  | grouping__id  | gp_name  | gp_sd  |
+----------+-------------+-------------+---------------+----------+--------+
| NULL     | NULL        | 4           | 3             | 1        | 1      |
| NULL     | 2010-01-03  | 1           | 2             | 1        | 0      |
| NULL     | 2012-11-03  | 1           | 2             | 1        | 0      |
| NULL     | 2013-10-02  | 1           | 2             | 1        | 0      |
| NULL     | 2014-01-29  | 1           | 2             | 1        | 0      |
| Lucy     | NULL        | 1           | 1             | 0        | 1      |
| Lucy     | 2010-01-03  | 1           | 0             | 0        | 0      |
| Michael  | NULL        | 1           | 1             | 0        | 1      |
| Michael  | 2014-01-29  | 1           | 0             | 0        | 0      |
| Steven   | NULL        | 1           | 1             | 0        | 1      |
| Steven   | 2012-11-03  | 1           | 0             | 0        | 0      |
| Will     | NULL        | 1           | 1             | 0        | 1      |
| Will     | 2013-10-02  | 1           | 0             | 0        | 0      |
+----------+-------------+-------------+---------------+----------+--------+

聚合条件过滤 HAVING

Hive从 0.7.0中开始添加了HAVING语句用以支持聚合结果的过滤。通过使用HAVING，可以避免在GROUP BY语句之后使用子查询。

> SELECT
 gender_age.age
 FROM employee
 GROUP BY gender_age.age
 HAVING count(*)=1;
+-----------------+
| gender_age.age  |
+-----------------+
| 27              |
| 30              |
| 35              |
| 57              |
+-----------------+

HAVING语句中也支持使用别名

> SELECT 
gender_age.age, 
count(*) as cnt 
FROM employee 
GROUP BY gender_age.age 
HAVING cnt=1
+-----------------+------+
| gender_age.age  | cnt  |
+-----------------+------+
| 27              | 1    |
| 30              | 1    |
| 35              | 1    |
| 57              | 1    |
+-----------------+------+

TIP：HAVING 也支持对常规字段进行过滤，但是为了能获得更好的性能，对于这样的过滤类型还是建议使用WHERE子句，而不是HAVING

如果不使用HAVING，就需要使用子查询来代替

> SELECT a.age
FROM
(SELECT count(*) as cnt, gender_age.age
FROM employee GROUP BY gender_age.age
) a WHERE a.cnt<=1;
+--------+
| a.age  |
+--------+
| 27     |
| 30     |
| 35     |
| 57     |
+--------+

参考

https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup
书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 6

看得出的就是
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL DQL - 聚合函数.md
07-31
此Markdown文档提供了MySQL数据库中聚合函数的DQL操作示例代码和说明。通过文档，您可以学习如何使用不同的聚合函数对表中的数据进行统计和计算。 示例代码演示了使用`SUM`、`AVG`、`MAX`和`MIN`等聚合函数的方法。...
SQL（DQL）
01-27
1.字段(列)控制 1)查询所有列 SELECT*FROM表名; SELECT*FROMemp;-->其中“*”表示查询所有列2)查询指定列 SELECT列1[,列2,...列N]FROM表名;SELECTempno,ename,sal,commFROM
HiveQL DQL9—窗口函数
MAYIBO—BG的专栏
02-06 311
目录概述窗口聚合函数窗口排序函数窗口分析函数窗口表达式Row Typerange type参考 概述 Hive从0.11.0开始支持窗口函数，这是一组特殊的函数，可以扫描多个输入行来计算每个输出值。窗口函数通常和OVER, PARTITION BY, ORDER BY 以及 windowing specification一起使用。不同于和GROUP BY一起使用的常规聚合函数（每组只有一个结果值）...
hive中的高阶函数:with cube函数,Grouping sets函数,With rollup函数
Fresh_man888的博客
12-19 3557
1.应用背景: 实际生产中，各种指标的报表统计，往往都会涉及到多维分析,比如，统计日活数，日会话次数，日回头访客数，日新，日用户平均访问时长，访问深度……都需要从不同维度,各种角度去分析,如果上述维度分析需求，都逐个开发计算sql（逐个去group by聚合），工作繁冗！ 那么，如何解决这个问题呢？ 2.实现过程: 2.1 关键要点: 创建一个统一的目标维度分析聚合结果表，这个表应该包含所有的维度字段 利用hive的高阶聚合函数，在一个sql中，即可计算出所有可能的维度组合 2.2 C..
Elasticsearch 7.3.0 翻译 - 《Aggregations》 度量聚合 值统计聚合
肥嫩的羊排
09-09 250
值统计聚合（Value Count Aggregation）Value Count Aggregation Value Count Aggregation 单值度量标准聚合，用于计算从聚合文档中提取的值的数量。可以从文档中的特定字段提取这些值，也可以通过提供的脚本生成这些值。通常，此聚合器将与其他单值聚合一起使用。例如，当计算平均值时，可能对计算平均值的值的数量感兴趣。 POST /sales/_...
oracle中NVL函数的使用方法
weixin_33937778的博客
07-22 193
以下的文章主要介绍的是Oracle Nvl函数，本文主要介绍的是其实际应用语法，以及实际应用的参数的介绍，以下就是相关的具体内容的相关描述，希望在你浏览之后会对你今后的学习中会有所帮助。nvl( ) 函数 从两个表达式返回一个非 null 值。 语法NVL(eExpression1,eExpression2) 参数eExpression1,eExpression2...
Hive中with cube、with rollup、grouping sets用法
Hello Word
12-09 1万+
利用with cube、with rollup、grouping sets等进行分组查询
DQL数据查询语言.xmind
06-07
数据库SQL语句总结 DQL数据查询语言
DQL DML细刻度审计.txt
11-01
oracle细粒度审计(FGA) 是oracle提供的对表细粒度的级别的审计功能，可以捕获DML、SELECT等客户端发起的操作。
linux配置聚合链路
朱海燕的博客日记
09-04 1204
聚合连接（team）：指的是网络连接的捆绑/组队，通过将多个实际网卡（team-slave）整和为逻辑上的单个连接，实现负载均衡，热备份等单块网卡难以完成的特殊功能。 聚合连接的类型：热备份activebackup，轮询负载均衡roundrobin 定义聚合连接的类型配置时，采用JSON语法，主要特点如下： 标记一个对象--（对象） 每个对象  ---名称：值 每个字符串---“字符串”...
linux聚合网卡--聚合网卡状态查询（3）
热门推荐
01-05 2万+
查看/etc/modprobe.d/bonding.conf是否存在 若存在，说明：bond is runing! 若不存在，说明：bond is not runing! 完！
web前端开发小工具集合
king的专栏
07-25 2464
收集的一些轻量级非常实用的前端开发小工具，以后还会不定期进行更新，现将目前收集的信息列表如下：   CSS 3相关 1.CSS3样式生成器：http://www.css88.com/tool/css3Preview/ 2.CSS3渐变样式生成器，类似Photoshop中的渐变界面：http://www.colorzilla.com/gradient-editor/ 3.CSS3动画制作工
Hive 配置——认证和授权
MAYIBO—BG的专栏
03-03 9367
原文：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-AuthenticationandAuthorization 限制的/隐藏的/内部列表和白名单（Restricted/Hidden/Internal List and Whitelist） * hi...
Hive优化—skew join优化原理详解
MAYIBO—BG的专栏
12-17 6687
目录优化原理适用范围测试验证 优化原理 JOIN中倾斜键的处理思路最早是在HIVE-964中提出的，整体思路是使用独立的作业和mapjoin来处理倾斜的键。 用以处理倾斜键的MR作业数是表的数量减一(we can stream the last table, so big keys in the last table will not be a problem) 在执行JOIN的过程中，会将一个表中的大key（也就是倾斜的那部分数据，判断是否倾斜由配置项hive.skewjoin.key指定，默认是1000
HiveQL函数7—数据脱敏函数（Data Masking）
MAYIBO—BG的专栏
01-21 4664
目录1.mask(string str[, string upper[, string lower[, string number]]])2.mask_first_n(string str[, int n])3.mask_last_n(string str[, int n])4.mask_show_first_n(string str[, int n])5.mask_show_last_n(str...
HiveQL DDL—物化视图
MAYIBO—BG的专栏
10-05 2041
目录 概述 创建物化视图 查看物化视图 使用SHOW查看 使用DESCRIBE查看 修改物化视图 删除物化视图 概述   物化视图是Apache Hive 3.0.0新加入的特性。使用物化视图加速查询速度是传统数据库常用的技术之一。Apache Hive3.0.0中引入的初始实现侧重于引入物化视图和基于这些物化视图的自动查询重写。物化视图可以存储在Hive本地或者其他系统中，如Druid...
Hive源码分析——Alter Table操作执行流程分析
MAYIBO—BG的专栏
04-29 1893
目录概述过程分析参数介绍初始化类型兼容性检查修改表名操作其他修改操作异常处理总结 概述 Alter Table是非常常见的一种DDL操作，在Hive中，使用Alter Table可以修改表名，修改表属性，添加字段，修改字段数据类型等等。需要注意的是，ALTER TABLE修改的只是MySQL中的相关元数据，不会对表数据产生影响，用户需要手动确保实际数据符合元数据的定义，否则查询不会返回预期的结果。但在分区数比较多的情况下，修改表名这类逻辑上非常简单的操作时间开销却很大。因此对Alter Table语句的具体
掌握MySQL DQL查询：从基础到高级实战
5. **分组与聚合**：GROUP BY用于根据特定字段对数据进行分组，而HAVING则是在分组后过滤结果，只保留满足额外条件的记录。 6. **排序与限制**：ORDER BY用于按照一个或多个字段对查询结果进行排序，而LIMIT语句则...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值