次の方法で共有

HAVING 句

  • [アーティクル]

適用対象: 「はい」のチェック マーク Databricks SQL 「はい」のチェック マーク Databricks Runtime

GROUP BY によって生成される結果を、指定された条件に基づいてフィルター処理します。 多くの場合、GROUP BY 句と組み合わせて使用されます。

構文

HAVING boolean_expression

パラメーター

  • boolean_expression

    結果の型 BOOLEAN に対して評価される任意の式。 論理演算子 (AND、OR) を使用して、2 つ以上の式を組み合わせることができます。

    HAVING 句で指定された式は、次のもののみを参照できます。

例

> 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` clause referring to column in `GROUP BY`.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city = 'Fremont';
  Fremont  32

-- `HAVING` clause referring to aggregate function.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
  Dublin  33
 Fremont  32

-- `HAVING` clause referring to aggregate function by its alias.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
  Dublin  33
 Fremont  32

-- `HAVING` clause referring to a different aggregate function than what is present in
-- `SELECT` list.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
 Dublin  33

-- `HAVING` clause referring to constant expression.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER BY city;
   Dublin  33
  Fremont  32
 San Jose  13

-- `HAVING` clause without a `GROUP BY` clause.
> SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
  78