적용 대상:
Databricks SQL Databricks Runtime
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 ) ]
매개 변수
전체
적용 대상:
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 오류를 발생시킵니다.그룹화_세트
그룹화 집합은 괄호 안에 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, 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, location)는 다음과 같습니다.
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, location), (warehouse), (product), (location), ())CUBE사양에 N개 요소를 지정하면 2^N개GROUPING SETS가 생성됩니다.aggregate_name
집계 함수 이름(MIN, MAX, COUNT, SUM, AVG 등)입니다.
DISTINCT
입력 행이 집계 함수에 전달되기 전에 중복 항목을 제거합니다.
필터
boolean_expression절의WHERE이 true로 평가되는 입력 행이 필터링되어 집계 함수에 전달되고 다른 행은 삭제됩니다.
혼합/중첩 그룹화 분석
GROUP BY 절에는 여러 group_expressions과 여러 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로 처리할 수 있습니다.
GROUPING SETS 절에 여러 GROUP BY가 있으면 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