HAVING 절

적용 대상:예로 표시된 확인 Databricks SQL 예로 표시된 확인 Databricks Runtime

지정된 조건에 따라 GROUP BY에 의해 생성된 결과를 필터링합니다. 종종 GROUP BY 절과 함께 사용됩니다.

구문

HAVING boolean_expression

매개 변수

  • boolean_expression

    표현식은 BOOLEAN 결과 형식으로 계산됩니다. 논리 연산자(AND 또는 OR)를 사용하여 둘 이상의 식을 결합할 수 있습니다.

    HAVING 절에 지정된 식은 다음만 참조할 수 있습니다.

    식이 GROUP BY 절에 없는 열을 참조하거나 집계 함수에 래핑되지 않은 경우 Azure Databricks MISSING_AGGREGATION 발생합니다.

일반적인 오류 조건

예제

> 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

-- Column not wrapped in aggregate function.
> SELECT city, quantity FROM dealer GROUP BY city HAVING sum(quantity) > 15;
  Error: MISSING_AGGREGATION
  • Last updated on