GROUP BY 절

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

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

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

Syntax

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 ) ]

매개 변수

  • group_expression

    행이 함께 그룹화되는 조건을 지정합니다. 행 그룹화는 그룹화 식의 결과 값에 따라 수행됩니다. 그룹화 식은 와 같은 열 이름, 와 같은 열 위치 또는 와 같은 식일 수 GROUP BY a, aGROUP BY 0GROUP BY a + b 있습니다.

  • grouping_set

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

  • GROUPING SETS

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

    GROUP BY GROUPING SETS ((warehouse), (product)) 는 의미상 및 결과의 합과 GROUP BY warehouseGROUP BY product 동일합니다.

    이 절은 UNION ALL 연산자의 각 레그가 UNION ALL 절에 지정된 각 그룹화 집합의 집계를 수행하는 에 대한 GROUPING SETS 약식입니다.

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

참고

Hive 호환성을 위해 Databricks Runtime 을 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

    단일 문에서 여러 수준의 집계를 지정합니다. 이 절은 여러 그룹화 집합을 기반으로 집계를 계산하는 데 사용됩니다. 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), ())와 같습니다.

    사양의 N 요소는 ROLLUP N+1을 GROUPING SETS 생성합니다.

  • CUBE

    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), ())와 같습니다.

    사양의 N 요소는 CUBE 2^N GROUPING SETS 입니다.

  • aggregate_name

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

  • DISTINCT

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

  • FILTER

    절의 가 true로 평가되는 입력 행을 boolean_expressionWHERE 필터링하여 집계 함수에 전달하고 다른 행은 삭제됩니다.

###m 혼합/중첩 그룹화 분석

GROUP BY절에는 여러 group_expressions, 여러 , 및 s가 포함될 수 CUBEROLLUPGROUPING SETS 있습니다.

GROUPING SETS 에는 중첩된 , 또는 절이 있을 수도 CUBEROLLUPGROUPING SETS 있습니다. 예:

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

CUBEROLLUP 는 에 대한 구문 sugar일 GROUPING SETS 뿐입니다. 및 를 로 변환하는 방법은 위의 CUBEROLLUP 섹션을 GROUPING SETS 참조하세요. group_expression 는 이 컨텍스트에서 단일 그룹으로 처리될 수 GROUPING SETS 있습니다.

절의 여러 에 대해 GROUPING SETSGROUP BY Databricks Runtime 원래 GROUPING SETS 의 교차곱을 수행하여 단일 를 GROUPING SETS 생성합니다.

절에 중첩된 경우 GROUPING SETSGROUPING SETS Databricks Runtime 그룹화 집합을 가져와서 제거합니다. 예:

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 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);

-- 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

-- 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 TABLE person (id INT, name STRING, age INT);
> INSERT INTO person 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