GROUP BY 절

아티클
04/18/2024

적용 대상: 검사 '예'로 표시 Databricks SQL Databricks 런타임

GROUP BY 절은 지정된 그룹화 식 집합을 기반으로 행을 그룹화하고 하나 이상의 지정된 집계 함수를 기반으로 행 그룹의 집계를 계산하는 데 사용합니다. Databricks SQL은 GROUPING SETS, CUBE, ROLLUP 절을 통해 동일한 입력 레코드 집합에 대해 여러 집계를 수행하는 고급 집계도 지원합니다. 그룹화 식과 고급 집계를 GROUP BY 절에서 혼합하고 GROUPING SETS 절에서 중첩시킬 수 있습니다.

자세한 내용은 혼합/중첩 그룹화 분석 섹션을 참조하세요.

FILTER 절이 집계 함수에 연결되면 일치하는 행만 해당 함수에 전달됩니다.

구문

GROUP BY ALL

GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]

grouping_set
   { expression |
     ( [ expression [, ...] ] ) }

집계 함수는 다음과 같이 정의합니다.

aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]

매개 변수

ALL

적용 대상: Databricks SQL Databricks Runtime 12.2 LTS 이상

집계 함수를 포함하지 않는 모든 SELECT-list 식을 group_expression으로 추가하는 축약형 표기법입니다. 그러한 식이 존재하지 않는 경우 GROUP BY ALL은 GROUP BY 절을 생략하는 것과 동일하여 전역 집계가 됩니다.

GROUP BY ALL은 해결할 수 있는 그룹 식 집합을 생성한다고 보장되지 않습니다. Azure Databricks는 생성된 절의 형식이 올바르지 않은 경우 UNRESOLVED_ALL_IN_GROUP_BY 또는 MISSING_AGGREGATION을 발생시킵니다.
group_expression

행을 그룹화하기 위한 조건을 지정합니다. 행 그룹화는 그룹화 식의 결과 값에 따라 수행됩니다. 그룹화 식은 열 이름(예: GROUP BY a), 열 위치(예: GROUP BY 0) 또는 식(예: GROUP BY a + b)일 수 있습니다. group_expression에 집계 함수가 포함된 경우 Azure Databricks는 GROUP_BY_AGGREGATE 오류를 발생시킵니다.
grouping_set

그룹화 집합은 괄호 안에 쉼표로 구분된 0개 이상의 식으로 지정됩니다. 그룹화 집합에 요소가 하나만 있는 경우 괄호를 생략할 수 있습니다. 예를 들어 GROUPING SETS ((a), (b))은 GROUPING SETS (a, b)과 같습니다.
그룹화 집합

GROUPING SETS 다음에 지정된 각 그룹화 집합의 행을 그룹화합니다. 예시:

GROUP BY GROUPING SETS ((warehouse), (product))는 GROUP BY warehouse 및 GROUP BY product 결과의 합집합과 의미 체계가 동일합니다.

이 절은 UNION ALL의 축약형으로, UNION ALL 연산자의 각 레그는 GROUPING SETS 절에 지정된 각 그룹화 집합을 집계합니다.

마찬가지로 GROUP BY GROUPING SETS ((warehouse, product), (product), ())는 GROUP BY warehouse, product, GROUP BY product 및 전역 집계의 결과의 합집합과 의미 체계가 동일합니다.

참고 항목

Hive 호환성을 위해 Databricks SQL에서는 GROUP BY ... GROUPING SETS (...)를 허용합니다. GROUP BY 식은 일반적으로 무시되지만 GROUPING SETS 식 외에 추가 식을 포함하는 경우 추가 식이 그룹화 식에 포함되고 값은 항상 null입니다. 예를 들어 SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)에서 c 열의 출력은 항상 null입니다.

롤업

단일 문에서 여러 수준의 집계를 지정합니다. 이 절은 여러 그룹화 집합을 기반으로 집계를 계산하는 데 사용됩니다. ROLLUP는 GROUPING SETS의 축약형입니다. 예시:

GROUP BY warehouse, product WITH ROLLUP 또는 GROUP BY ROLLUP(warehouse, product)는 다음과 동일합니다.

GROUP BY GROUPING SETS((warehouse, product), (warehouse), ()).

한편 GROUP BY ROLLUP(warehouse, product, (warehouse, location))는

GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ())와 같습니다.

ROLLUP 사양에 N개 요소를 지정하면 N+1개 GROUPING SETS가 생성됩니다.
큐브

CUBE 절은 GROUP BY 절에 지정된 그룹화 열의 조합을 기반으로 집계를 수행하는 데 사용됩니다. CUBE는 GROUPING SETS의 축약형입니다. 예시:

GROUP BY warehouse, product WITH CUBE 또는 GROUP BY CUBE(warehouse, product)는 다음과 동일합니다.

GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ()).

GROUP BY CUBE(warehouse, product, (warehouse, location)) 는 다음과 같습니다.
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
CUBE 사양에 N개 요소를 지정하면 2^N개 GROUPING SETS가 생성됩니다.
aggregate_name

집계 함수 이름(MIN, MAX, COUNT, SUM, AVG 등)입니다.
DISTINCT

입력 행이 집계 함수에 전달되기 전에 중복 항목을 제거합니다.
필터

WHERE 절의 boolean_expression이 true로 평가되는 입력 행이 필터링되어 집계 함수에 전달되고 다른 행은 삭제됩니다.

혼합/중첩 그룹화 분석

GROUP BY 절은 여러 group_expression 및 여러 CUBE, ROLLUP, GROUPING SETS를 포함할 수 있습니다.

GROUPING SETS는 중첩된 CUBE, ROLLUP, GROUPING SETS 절도 포함할 수 있습니다. 예시:

GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))

CUBE 및 ROLLUP은 GROUPING SETS의 간소화된 구문입니다. CUBE 및 ROLLUP을 GROUPING SETS로 변환하는 방법은 위 섹션을 참조하세요. 이 컨텍스트에서 group_expression은 단일 그룹 GROUPING SETS로 처리할 수 있습니다.

GROUP BY 절에 여러 GROUPING SETS가 있으면 Databricks SQL은 원래 GROUPING SETS의 교차곱을 수행하여 단일 GROUPING SETS를 생성합니다.

절에 GROUPING SETS 중첩된 GROUPING SETS 경우 Databricks SQL은 그룹화 집합을 가져와서 제거합니다. 예를 들어 다음 쿼리는 다음과 같습니다.

GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ());

GROUP BY warehouse, ROLLUP(product), CUBE(location, size);

는 다음과 같습니다.

GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))

한편 GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))는

GROUP BY GROUPING SETS((warehouse), (warehouse, product))와 같습니다.

예제

CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
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);

-- Sum of quantity per dealership. Group by `id`.
> SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Use column position in GROUP by clause.
> SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
> SELECT id, sum(quantity) AS sum, max(quantity) AS max
    FROM dealer GROUP BY id ORDER BY id;
  id sum max
 --- --- ---
 100  32  15
 200  33  20
 300  13   8

-- Count the number of distinct dealers in cities per car_model.
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
> SELECT id,
         sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
    FROM dealer
    GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            17
 200            23
 300             5

-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
    ORDER BY city;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH ROLLUP
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH CUBE
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

--Prepare data for ignore nulls example
> CREATE TEMP VIEW person (id, name, age) AS
   VALUES (100, 'Mary', NULL),
          (200, 'John', 30),
          (300, 'Mike', 80),
          (400, 'Dan' , 50);

--Select the first row in column age
> SELECT FIRST(age) FROM person;
  first(age, false)
 --------------------
  NULL

--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.
> SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
  first(age, true)    last(id, false)    sum(id)
 ------------------- ------------------ ----------
  30                  400                1000

GROUP BY 절

구문

매개 변수

혼합/중첩 그룹화 분석

예제

관련된 문서

추가 리소스