Impala 简明教程

Impala - Having Clause

Impala 中的 Having 子句能够让您指定筛选哪些组结果出现在最终结果中的条件。

通常情况下， Having 子句与 group by 子句一起使用；它规定了 GROUP BY 子句创建的组的条件。

Syntax

以下是 Having 子句的语法。

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

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  |
| 7  | ram      | 25  | chennai     | 23000  |
| 8  | rahim    | 22  | vizag       | 31000  |
| 9  | robert   | 23  | banglore    | 28000  |
+----+----------+-----+-----------+--------+
Fetched 9 row(s) in 0.51s

以下是 Having 子句在 Impala 中的使用示例——

[quickstart.cloudera:21000] > select max(salary) from customers group by age having max(salary) > 20000;

该查询最初按年龄对表进行分组，并选择每个组的最高薪资并显示那些高于 20000 的薪资，如下所示。

20000
+-------------+
| max(salary) |
+-------------+
| 30000       |
| 35000       |
| 40000       |
| 32000       |
+-------------+
Fetched 4 row(s) in 1.30s