Impala 简明教程

Impala - Group By Clause

Impala GROUP BY 子句与 SELECT 语句结合使用，用于将相同数据组织成组。

Syntax

下面是 GROUP BY 子句的语法。

select data from table_name Group BY col_name;

Example

假设数据库 my_db 中有一个名为 customers 的表，其内容如下 −

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 3  | kaushik  | 23  | Kota      | 30000  |
| 4  | Chaitali | 25  | Mumbai    | 35000  |
| 5  | Hardik   | 27  | Bhopal    | 40000  |
| 6  | Komal    | 22  | MP        | 32000  |
+----+----------+-----+-----------+--------+
Fetched 6 row(s) in 0.51s

您可以使用 GROUP BY 查询获取每个客户的总工资，如下所示。

[quickstart.cloudera:21000] > Select name, sum(salary) from customers Group BY name;

在执行后，上述查询给出以下输出。

Query: select name, sum(salary) from customers Group BY name
+----------+-------------+
| name     | sum(salary) |
+----------+-------------+
| Ramesh   | 20000       |
| Komal    | 32000       |
| Hardik   | 40000       |
| Khilan   | 15000       |
| Chaitali | 35000       |
| kaushik  | 30000       |
+----------+-------------+
Fetched 6 row(s) in 1.75s

假设此表包含多个记录，如下所示。

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Ramesh   | 32  | Ahmedabad | 1000|  |
| 3  | Khilan   | 25  | Delhi     | 15000  |
| 4  | kaushik  | 23  | Kota      | 30000  |
| 5  | Chaitali | 25  | Mumbai    | 35000  |
| 6  | Chaitali | 25  | Mumbai    | 2000   |
| 7  | Hardik   | 27  | Bhopal    | 40000  |
| 8  | Komal    | 22  | MP        | 32000  |
+----+----------+-----+-----------+--------+

现在，您可以再次使用 Group By 子句获取所有员工的总工资，同时考虑记录的重复条目，如下所示。

Select name, sum(salary) from customers Group BY name;

在执行后，上述查询给出以下输出。

Query: select name, sum(salary) from customers Group BY name
+----------+-------------+
| name     | sum(salary) |
+----------+-------------+
| Ramesh   | 21000       |
| Komal    | 32000       |
| Hardik   | 40000       |
| Khilan   | 15000       |
| Chaitali | 37000       |
| kaushik  | 30000       |
+----------+-------------+
Fetched 6 row(s) in 1.75s