Spark SQL 的 HAVING 子句

更新时间：2021-06-29 22:50:56 标签：spark sql having

提示

Spark SQL 教程正在筹划编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。

HAVING 子句用于根据指定的条件过滤 GROUP BY 生成的结果。它通常与 GROUP BY 子句一起使用。

语法

HAVING boolean_expression

boolean_expression 是：

指定计算结果类型为布尔值的任何表达式。可以使用逻辑运算符（AND、OR）将两个或多个表达式组合在一起。

注：

HAVING 子句中指定的表达式只能引用：

常量 Constants
GROUP BY 出现的表达式
聚合函数 Aggregate functions

示例

构建数据：

-- 构建数据
CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer VALUES
    (100, 'Fremont', 'Honda Civic', 10),
    (100, 'Fremont', 'Honda Accord', 15),
    (100, 'Fremont', 'Honda CRV', 7),
    (200, 'Dublin', 'Honda Civic', 20),
    (200, 'Dublin', 'Honda Accord', 10),
    (200, 'Dublin', 'Honda CRV', 3),
    (300, 'San Jose', 'Honda Civic', 5),
    (300, 'San Jose', 'Honda Accord', 8);

使用示例如下：

-- `HAVING` 引用中列的  `GROUP BY` 聚合内容
SELECT city, sum(quantity) AS sum
FROM dealer
GROUP BY city
HAVING city = 'Fremont';
+-------+---+
|   city|sum|
+-------+---+
|Fremont| 32|
+-------+---+

-- `HAVING` 指定聚合函数的子句
SELECT city, sum(quantity) AS sum
FROM dealer
GROUP BY city
HAVING sum(quantity) > 15;
+-------+---+
|   city|sum|
+-------+---+
| Dublin| 33|
|Fremont| 32|
+-------+---+

-- `HAVING` 通过其别名引用聚合函数
SELECT city, sum(quantity) AS sum
FROM dealer
GROUP BY city
HAVING sum > 15;
+-------+---+
|   city|sum|
+-------+---+
| Dublin| 33|
|Fremont| 32|
+-------+---+

-- `HAVING` 与 “SELECT” 列表中的聚合函数不同的聚合函数。
SELECT city, sum(quantity) AS sum
FROM dealer
GROUP BY city
HAVING max(quantity) > 15;
+------+---+
|  city|sum|
+------+---+
|Dublin| 33|
+------+---+

-- 表示常量表达式
SELECT city, sum(quantity) AS sum
FROM dealer
GROUP BY city
HAVING 1 > 0
ORDER BY city;
+--------+---+
|    city|sum|
+--------+---+
|  Dublin| 33|
| Fremont| 32|
|San Jose| 13|
+--------+---+

-- `HAVING` 子句而没有 ` GROUP BY`子句
SELECT sum(quantity) AS sum
FROM dealer
HAVING sum(quantity) > 10;
+---+
|sum|
+---+
| 78|
+---+

<< GROUP BY 子句 Spark SQL 教程 ORDER BY 子句 >>