GROUP BY 절은 SELECT 쿼리를 집계 모드로 전환하며, 동작 방식은 다음과 같습니다.
GROUP BY절에는 표현식 목록(또는 길이가 1인 목록으로 간주되는 단일 표현식)이 포함됩니다. 이 목록은 “그룹화 키” 역할을 하며, 각 개별 표현식은 “키 표현식”이라고 합니다.- SELECT, HAVING, ORDER BY 절의 모든 표현식은 반드시 키 표현식을 기반으로 계산되거나, 키가 아닌 표현식(일반 컬럼 포함)에 대한 집계 함수를 기반으로 계산되어야 합니다. 다시 말해, 테이블에서 선택한 각 컬럼은 키 표현식에 사용되거나 집계 함수 내부에서 사용되어야 하며, 둘 다에 동시에 사용될 수는 없습니다.
SELECT쿼리를 집계한 결과에는 원본 테이블의 “그룹화 키”에서 고유한 값의 개수만큼 행이 포함됩니다. 일반적으로 이로 인해 행 수가 크게 줄어들며, 종종 몇 자릿수 수준으로 감소합니다. 하지만 항상 그런 것은 아닙니다. 모든 “그룹화 키” 값이 서로 달랐다면 행 수는 그대로 유지됩니다.
테이블에 대해 집계를 수행하는 추가 방법도 있습니다. 쿼리에서 테이블 컬럼이 집계 함수 내부에만 포함되어 있다면
GROUP BY 절은 생략할 수 있으며, 이 경우 빈 키 집합에 대한 집계가 수행된다고 가정합니다. 이러한 쿼리는 항상 정확히 1개의 행을 반환합니다.NULL 처리
NULL==NULL로 간주합니다. 이는 대부분의 다른 문맥에서의 NULL 처리와는 다릅니다.
이것이 의미하는 바를 보여주는 예시를 살펴보겠습니다.
다음과 같은 테이블이 있다고 가정하겠습니다:
SELECT sum(x), y FROM t_null_big GROUP BY y를 실행하면 다음과 같은 결과가 나옵니다:
GROUP BY에서 y = NULL인 경우, NULL을 하나의 값처럼 취급하여 x가 합산된 것을 확인할 수 있습니다.
여러 키를 GROUP BY에 전달하면 결과에 선택된 항목의 모든 조합이 표시되며, 이때도 NULL은 특정 값인 것처럼 취급됩니다.
ROLLUP 수정자
ROLLUP 수정자는 GROUP BY 목록의 순서를 기준으로 키 표현식의 소계를 계산하는 데 사용됩니다. 소계 행은 결과 테이블 뒤에 추가됩니다.
소계는 역순으로 계산됩니다. 먼저 목록의 마지막 키 표현식에 대한 소계를 계산한 다음, 그 이전 표현식에 대해 계산하며, 이런 방식으로 첫 번째 키 표현식까지 진행합니다.
소계 행에서는 이미 “그룹화된” 키 표현식의 값이 0 또는 빈 문자열로 설정됩니다.
HAVING 절이 소계 결과에 영향을 줄 수 있다는 점에 유의하십시오.
Query
GROUP BY 절에는 3개의 키 표현식이 있으므로, 결과에는 오른쪽에서 왼쪽으로 “롤업”된 소계를 포함하는 4개의 테이블이 생성됩니다:
GROUP BY year, month, day;GROUP BY year, month(day컬럼은 0으로 채워짐);GROUP BY year(이제month,day컬럼이 모두 0으로 채워짐);- 그리고 합계 (세 개의 키 표현식 컬럼이 모두 0임).
Response
WITH 키워드를 사용해 작성할 수도 있습니다.
Query
- SQL 표준과의 호환성을 위한 group_by_use_nulls 설정을 참조하십시오.
CUBE 수정자
CUBE 수정자는 GROUP BY 목록에 있는 키 표현식의 모든 조합에 대한 소계를 계산하는 데 사용됩니다. 소계 행은 결과 테이블 뒤에 추가됩니다.
소계 행에서는 모든 “그룹화된” 키 표현식의 값이 0 또는 빈 문자열로 설정됩니다.
HAVING 절이 소계 결과에 영향을 줄 수 있다는 점에 유의하십시오.
Query
GROUP BY 절에 3개의 키 표현식이 있으므로, 결과에는 가능한 모든 키 표현식 조합에 대한 소계가 포함된 8개의 테이블이 생성됩니다:
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- 그리고 합계.
GROUP BY에 포함되지 않은 컬럼은 0으로 채워집니다.
Response
WITH 키워드를 사용해 작성할 수도 있습니다.
Query
- SQL 표준과의 호환성을 위한 group_by_use_nulls 설정
WITH TOTALS 수정자
WITH TOTALS 수정자를 지정하면 행이 하나 더 계산됩니다. 이 행의 키 컬럼에는 기본값(0 또는 빈 문자열)이 들어가고, 집계 함수 컬럼에는 모든 행에 대해 계산한 값(“합계” 값)이 들어갑니다.
이 추가 행은 JSON*, TabSeparated*, Pretty* 포맷에서만 생성되며, 다른 행과는 별도로 출력됩니다.
XML및JSON*포맷에서는 이 행이 별도의totals필드로 출력됩니다.TabSeparated*,CSV*,Vertical포맷에서는 이 행이 기본 결과 뒤에 출력되며, 그 앞에 빈 행이 하나 추가됩니다(다른 데이터 뒤).Pretty*포맷에서는 이 행이 기본 결과 뒤에 별도의 테이블로 출력됩니다.Template포맷에서는 이 행이 지정된 템플릿에 따라 출력됩니다.- 그 밖의 포맷에서는 사용할 수 없습니다.
totals는
SELECT 쿼리 결과에는 출력되지만, INSERT INTO ... SELECT에는 출력되지 않습니다.WITH TOTALS는 여러 방식으로 동작할 수 있습니다. 동작 방식은 totals_mode 설정에 따라 달라집니다.
합계 처리 구성
totals_mode = 'before_having'입니다. 이 경우 ‘totals’는 HAVING 및 max_rows_to_group_by를 통과하지 못한 행까지 포함한 모든 행을 기준으로 계산됩니다.
다른 옵션은 HAVING을 통과한 행만 ‘totals’에 포함하며, max_rows_to_group_by 및 group_by_overflow_mode = 'any' 설정과의 동작 방식도 서로 다릅니다.
after_having_exclusive – max_rows_to_group_by를 통과하지 못한 행은 포함하지 않습니다. 즉, max_rows_to_group_by를 생략했을 때와 비교하면 ‘totals’의 행 수는 더 적거나 같습니다.
after_having_inclusive – max_rows_to_group_by를 통과하지 못한 모든 행을 ‘totals’에 포함합니다. 즉, max_rows_to_group_by를 생략했을 때와 비교하면 ‘totals’의 행 수는 더 많거나 같습니다.
after_having_auto – HAVING을 통과한 행 수를 계산합니다. 그 수가 일정 비율(기본값 50%)을 넘으면 max_rows_to_group_by를 통과하지 못한 모든 행을 ‘totals’에 포함합니다. 그렇지 않으면 포함하지 않습니다.
totals_auto_threshold – 기본값은 0.5입니다. after_having_auto에 사용하는 계수입니다.
max_rows_to_group_by와 group_by_overflow_mode = 'any'를 사용하지 않으면 after_having의 모든 변형은 동일하므로, 아무 것이나 사용할 수 있습니다(예: after_having_auto).
JOIN 절의 서브쿼리를 포함해 서브쿼리에서 WITH TOTALS를 사용할 수 있습니다(이 경우 해당 합계 값이 결합됩니다).
GROUP BY ALL
GROUP BY ALL은 집계 함수가 아닌 모든 SELECT 표현식을 나열하는 것과 동일합니다.
예시:
GROUP BY 키에는 해당 함수에서 추출할 수 있는 비집계 필드가 가능한 한 많이 포함됩니다.
예시는 다음과 같습니다:
예시
GROUP BY는 집계 함수 값 집합을 계산합니다.
GROUPING SETS 수정자
GROUPING SETS로 표현할 수 있습니다.
ROLLUP, CUBE, GROUPING SETS 수정자를 사용하는 쿼리는 구문은 같지만 실행 방식은 다를 수 있습니다.
GROUPING SETS는 모든 작업을 병렬로 실행하려고 하지만, ROLLUP과 CUBE는 집계 결과의 최종 병합을 단일 스레드에서 수행합니다.
소스 컬럼에 기본값이 포함된 경우, 어떤 행이 해당 컬럼을 키로 사용하는 집계에 속하는지 구분하기 어려울 수 있습니다.
이 문제를 해결하려면 GROUPING 함수를 사용해야 합니다.
예시
다음 두 쿼리는 동일합니다.
- SQL 표준과의 호환성을 위한 group_by_use_nulls 설정.
구현 세부 사항
테이블 정렬 키에 따른 GROUP BY 최적화
GROUP BY 표현식에 정렬 키의 접두사(prefix)나 단사 함수가 최소한 포함되어 있으면 집계를 더 효율적으로 수행할 수 있습니다. 이 경우 테이블에서 새 키를 읽을 때 집계의 중간 결과를 확정하여 클라이언트로 전송할 수 있습니다. 이 동작은 optimize_aggregation_in_order 설정으로 활성화됩니다. 이러한 최적화는 집계 중 메모리 사용량을 줄여 주지만, 경우에 따라 쿼리 실행이 더 느려질 수 있습니다.
외부 메모리에서의 GROUP BY
GROUP BY 수행 중 메모리 사용량을 제한하기 위해 임시 데이터를 디스크에 덤프하도록 설정할 수 있습니다.
max_bytes_before_external_group_by 설정은 GROUP BY 임시 데이터를 파일 시스템에 덤프하기 위한 RAM 사용량 임계값을 결정합니다. 0(기본값)으로 설정하면 비활성화됩니다.
또는 max_bytes_ratio_before_external_group_by를 설정할 수도 있습니다. 이 설정을 사용하면 쿼리의 메모리 사용량이 특정 임계값에 도달한 경우에만 외부 메모리에서 GROUP BY를 사용할 수 있습니다.
max_bytes_before_external_group_by를 사용할 때는 max_memory_usage를 약 2배로 설정하는 것을 권장합니다(또는 max_bytes_ratio_before_external_group_by=0.5). 이는 집계에 두 단계가 있기 때문입니다. 첫 번째는 데이터를 읽고 중간 데이터를 만드는 단계(1)이고, 두 번째는 중간 데이터를 병합하는 단계(2)입니다. 파일 시스템으로 데이터를 덤프할 수 있는 시점은 1단계뿐입니다. 임시 데이터가 덤프되지 않으면 2단계에서 1단계와 거의 같은 양의 메모리가 필요할 수 있습니다.
예를 들어 max_memory_usage를 10000000000으로 설정했고 외부 집계를 사용하려는 경우, max_bytes_before_external_group_by를 10000000000으로, max_memory_usage를 20000000000으로 설정하는 것이 적절합니다. 외부 집계가 트리거되면(즉, 임시 데이터가 한 번 이상 덤프된 경우) RAM 최대 사용량은 max_bytes_before_external_group_by를 약간 초과하는 수준에 그칩니다.
분산 쿼리 처리에서는 외부 집계가 원격 서버에서 수행됩니다. 요청 서버가 적은 양의 RAM만 사용하도록 하려면 distributed_aggregation_memory_efficient를 1로 설정하십시오.
디스크에 플러시된 데이터를 병합할 때와 distributed_aggregation_memory_efficient 설정이 활성화된 상태에서 원격 서버의 결과를 병합할 때는 전체 RAM 용량 중 최대 1/256 * the_number_of_threads를 사용합니다.
외부 집계가 활성화되어 있어도 데이터 양이 max_bytes_before_external_group_by보다 적다면(즉, 데이터가 플러시되지 않았다면) 쿼리는 외부 집계를 사용하지 않을 때와 동일한 속도로 실행됩니다. 임시 데이터가 하나라도 플러시되면 실행 시간은 몇 배 정도 길어집니다(대략 3배).
GROUP BY 뒤에 ORDER BY와 LIMIT가 있으면 사용되는 RAM 양은 전체 테이블이 아니라 LIMIT의 데이터 양에 따라 결정됩니다. 하지만 ORDER BY에 LIMIT가 없다면 외부 정렬(max_bytes_before_external_sort)도 활성화해야 합니다.