HiveQL DQL6—基本聚合(GROUP BY)

最新推荐文章于 2022-11-25 10:36:45 发布

看得出的就是

最新推荐文章于 2022-11-25 10:36:45 发布

阅读量1.3k

点赞数

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/CPP_MAYIBO/article/details/104084901

版权

Hive 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

概述

数据聚合是根据特定条件收集和表达数据以获得有关特定组的更多信息的过程。HQL提供了几个内置聚合函数，如 max(…), min(…), 和avg(…)。此外，还支持使用关键字，如GROUPING SETS、ROLLUP、CUBE和不同类型的窗口函数实现高级聚合。
基本的内置聚合函数通常与GROUP BY子句一起使用。如果未指定GROUP BY子句，则默认情况下，它将聚合所有行（所有列）。除了聚合函数外，所有select的列也必须包含在GROUP BY子句中。

使用示例

示例：不使用GROUP BY的聚合

> SELECT count(*) as rowcnt1, count(1) AS rowcnt2 FROM employee;
+----------+----------+
| rowcnt1  | rowcnt2  |
+----------+----------+
| 4        | 4        |
+----------+----------+
1 row selected (0.191 seconds)

说明：有时候，基础聚合函数的调用会立即返回结果，如在上面的示例中，它花费的时间不到0.2秒，原因是Hive直接从收集的统计数据中获取这样的聚合结果。如果想要通过实际运行作业来获取聚合结果，可能需要在查询中添加limit或where子句。

示例：使用GROUP BY的聚合

> SELECT gender_age.gender, count(*) AS row_cnt FROM employee
GROUP BY gender_age.gender;
+--------------------+----------+
| gender_age.gender  | row_cnt  |
+--------------------+----------+
| Female             | 2        |
| Male               | 2        |
+--------------------+----------+

如果select字段不在group by子句中，则会报错

> SELECT gender_age.age, gender_age.gender, count(*) AS row_cnt
FROM employee GROUP BY gender_age.gender;
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'age' (state=42000,code=10025)

如果必须要select字段不在group by子句中，一种方法是使用窗口函数。

示例：同一个SELECT中使用多个聚合函数

聚合函数可以在同一个SELECT语句中和其他的聚合函数一起使用。还可以与其他函数（如条件函数）一起嵌套使用。但是，聚合函数不能嵌套

> SELECT gender_age.gender, AVG(gender_age.age) AS avg_age,
count(*) AS row_cnt FROM employee GROUP BY gender_age.gender;
+--------------------+----------+----------+
| gender_age.gender  | avg_age  | row_cnt  |
+--------------------+----------+----------+
| Female             | 42.0     | 2        |
| Male               | 32.5     | 2        |
+--------------------+----------+----------+

示例：与CASE WHEN THEN ELSE END,coalesce(…), if(…)一起使用聚合函数

> SELECT sum(CASE WHEN gender_age.gender = 'Male' THEN gender_age.age
ELSE 0 END)/count(CASE WHEN gender_age.gender = 'Male' THEN 1
ELSE NULL END) AS male_age_avg FROM employee;
+---------------+
| male_age_avg  |
+---------------+
| 32.5          |
+---------------+

示例：与coalesce(…), if(…)一起使用聚合函数

> SELECT
sum(coalesce(gender_age.age,0)) AS age_sum,
sum(if(gender_age.gender = 'Female',gender_age.age,0))
AS female_age_sum FROM employee;
+----------+-----------------+
| age_sum  | female_age_sum  |
+----------+-----------------+
| 149      | 84              |
+----------+-----------------+

示例：GROUP BY子句中使用表达式

> SELECT
 if(name = 'Will', 1, 0) as name_group,
 count(name) as name_cnt
 FROM employee
 GROUP BY if(name = 'Will', 1, 0);
+-------------+-----------+
| name_group  | name_cnt  |
+-------------+-----------+
| 0           | 3         |
| 1           | 1         |
+-------------+-----------+

示例：不允许聚合函数的嵌套

> SELECT avg(count(*)) AS row_cnt FROM employee; 
Error: Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 1:11 Not yet supported place for UDAF 'count' (state=42000,code=10128)

示例：NULL值的处理

对于NULL值的处理，函数max() 和 min() 会返回NULL，而sum() 和 avg() 会报错，count()会返回0

> SELECT max(null), min(null), count(null);
+-------+-------+------+
|  _c0  |  _c1  | _c2  |
+-------+-------+------+
| NULL  | NULL  | 0    |
+-------+-------+------+

> SELECT sum(null), avg(null); 
Error: Error while compiling statement: FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but void is passed. (state=42000,code=40000)

在处理具有NULL值的列之间的聚合时，可能会遇到非常特殊的行为。整行（如果行中有一列的值为NULL）将被忽略。为了避免这种情况，我们可以使用coalesce(…) 在列值为NULL时分配默认值。如下：
首先创建一个测试表

> CREATE TABLE t (val1 int, val2 int);
> INSERT INTO TABLE t VALUES (1, 2),(null,2),(2,3);

查看测试表中的数据

> SELECT * FROM t;
+---------+---------+
| t.val1  | t.val2  |
+---------+---------+
| 1       | 2       |
| NULL    | 2       |
| 2       | 3       |
+---------+---------+

执行聚合操作，第二行的数据将会被忽略

> SELECT sum(val1), sum(val1+val2) FROM t; 
+------+------+
| _c0  | _c1  |
+------+------+
| 3    | 8    |
+------+------+

使用coalesce将NULL值设为0

> SELECT sum(coalesce(val1,0)), sum(coalesce(val1,0)+val2) FROM t;
+------+------+
| _c0  | _c1  |
+------+------+
| 3    | 10   |
+------+------+

示例：与DISTINCT一起使用

聚合函数还可以与DISTINCT关键字一起使用，这样可以对唯一值聚合

> SELECT 
count(distinct gender_age.gender) AS gender_uni_cnt, 
count(distinct name) AS name_uni_cnt 
FROM employee;
+-----------------+---------------+
| gender_uni_cnt  | name_uni_cnt  |
+-----------------+---------------+
| 2               | 4             |
+-----------------+---------------+

当同时使用COUNT和DISTINCT时，它总是会忽略reducer参数的设置（如mapred.reduce.tasks = 20），而只会使用一个reducer。这种情况下，reducer就成了处理大量数据时的瓶颈，这可以通过使用子查询来解决。

> SELECT count(distinct gender_age.gender) as gender_uni_cnt FROM employee;
+-----------------+
| gender_uni_cnt  |
+-----------------+
| 2               |
+-----------------+

在聚合之前使用子查询过滤到重复数据

> SELECT
count(*) as gender_uni_cnt
FROM (
SELECT DISTINCT gender_age.gender FROM employee
) a;
+-----------------+
| gender_uni_cnt  |
+-----------------+
| 2               |
+-----------------+

在这种情况中，查询的第一阶段会执行DISTINCT,这会使用多于一个的reducer。第二阶段 count会处理较少量的数据，因为第一阶段会把重复的数据过滤掉。这样就会降低reducer的压力了。

示例：查找特定列的最大或最小值

有时，可能需要找到特定列和其他列的最大值或最小值。比如要查询这样的信息：员工表中年龄最大的男性和女性是谁，为此可以在struct上使用max/min函数，而不是使用子查询/窗口函数。

> SELECT gender_age.gender,
 max(struct(gender_age.age, name)).col1 as age,
 max(struct(gender_age.age, name)).col2 as name
 FROM employee
 GROUP BY gender_age.gender;
+--------------------+------+-------+
| gender_age.gender  | age  | name  |
+--------------------+------+-------+
| Female             | 57   | Lucy  |
| Male               | 35   | Will  |
+--------------------+------+-------+

尽管上述语句依然使用了 GROUP BY子句，但是这个查询还是比常规的 GROUP BY 和子查询更高效，因为它只触发了一个job。

示例：在map侧进行group by聚合

hive.map.aggr属性控制是否在map任务中使用聚合。默认值是true，这样Hive就可以在map任务中执行聚合操作从而可以获得更好的性能，但会消耗更多的内存。如果在map阶段发生了OOM，可以将该功能关闭。

set hive.map.aggr=true;
SELECT COUNT(*) FROM employee;

参考

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy
书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 6

看得出的就是

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
HiveQL DQL6—基本聚合(GROUP BY)

目录概述使用示例示例：不使用GROUP BY的聚合示例：使用GROUP BY的聚合示例：同一个SELECT中使用多个聚合函数示例：与CASE WHEN THEN ELSE END,coalesce(...), if(...)一起使用聚合函数示例：与coalesce(...), if(...)一起使用聚合函数示例：GROUP BY子句中使用表达式示例：不允许聚合函数的嵌套示例：NULL值的处理示...
复制链接

扫一扫