GROUP BY

GROUP BY

GROUP BY 절은 SELECT 쿼리를 집계 모드로 전환하며 다음과 같이 동작합니다:

• GROUP BY 절은 표현식 목록(또는 길이 1 목록으로 간주되는 단일 표현식)을 포함합니다. 이 목록은 "grouping key"로 작동하며, 각 개별 표현식은 "key expression"이라고 합니다.
• SELECT, HAVING, ORDER BY 절의 모든 표현식은 키 표현식 또는 키가 아닌 표현식(일반 컬럼 포함)에 대한 aggregate functions을 기반으로 계산되어야 합니다. 즉 테이블에서 선택된 각 컬럼은 키 표현식으로 사용되거나 집계 함수 안에서 사용되어야 하며, 둘 다는 안 됩니다.
• 집계 SELECT 쿼리의 결과는 원본 테이블에서 "grouping key"의 고유 값이 있던 만큼 많은 행을 포함합니다. 보통 이는 행 수를 크게 줄이며, 종종 몇 자릿수이지만 반드시 그런 것은 아닙니다: 모든 "grouping key" 값이 구별되면 행 수는 그대로입니다.

컬럼 이름 대신 컬럼 번호로 테이블의 데이터를 그룹화하려면 enable_positional_arguments 설정을 활성화하세요.

테이블에 대해 집계를 실행하는 추가 방법이 있습니다. 쿼리가 집계 함수 안에서만 테이블 컬럼을 포함하면 GROUP BY clause를 생략할 수 있고, 빈 키 집합에 의한 집계로 가정됩니다. 이러한 쿼리는 항상 정확히 한 행을 반환합니다.

NULL Processing

그룹화를 위해 ClickHouse는 NULL을 값으로 해석하고, NULL==NULL입니다. 이는 대부분의 다른 컨텍스트의 NULL 처리와 다릅니다.

무엇을 의미하는지 보여주는 예시입니다.

다음 테이블이 있다고 가정합니다:

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

SELECT sum(x), y FROM t_null_big GROUP BY y 쿼리는 다음과 같은 결과를 냅니다:

┌─sum(x)─┬────y─┐
│      4 │    2 │
│      3 │    3 │
│      5 │ ᴺᵁᴸᴸ │
└────────┴──────┘

y = NULL에 대한 GROUP BY는 NULL이 그 값인 것처럼 x를 합산했음을 볼 수 있습니다.

GROUP BY에 여러 키를 전달하면, NULL이 특정 값인 것처럼 선택의 모든 조합을 결과로 줍니다.

ROLLUP Modifier

ROLLUP modifier는 GROUP BY 목록에서의 순서에 따라 키 표현식에 대한 소계를 계산하는 데 사용됩니다. 소계 행은 결과 테이블 뒤에 추가됩니다.

소계는 역순으로 계산됩니다: 먼저 목록의 마지막 키 표현식에 대한 소계가 계산되고, 그다음 이전 것, 그리고 첫 번째 키 표현식까지 계속됩니다.

소계 행에서 이미 "grouped"된 키 표현식의 값은 0 또는 빈 줄로 설정됩니다.

HAVING 절이 소계 결과에 영향을 줄 수 있음을 명심하세요.

Example

테이블 t를 고려해 보세요:

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);

GROUP BY 섹션에 세 개의 키 표현식이 있으므로, 결과는 오른쪽에서 왼쪽으로 "rolled up"된 소계가 있는 네 개의 테이블을 포함합니다:

• GROUP BY year, month, day;
• GROUP BY year, month (그리고 day 컬럼은 0으로 채워짐);
• GROUP BY year (이제 month, day 컬럼 모두 0으로 채워짐);
• 그리고 totals(그리고 세 키 표현식 컬럼 모두 0).

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

같은 쿼리는 WITH 키워드를 사용해 쓸 수도 있습니다.

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

See also

• SQL 표준 호환을 위한 group_by_use_nulls 설정.

CUBE Modifier

CUBE modifier는 GROUP BY 목록에서 키 표현식의 모든 조합에 대해 소계를 계산하는 데 사용됩니다. 소계 행은 결과 테이블 뒤에 추가됩니다.

소계 행에서 모든 "grouped" 키 표현식의 값은 0 또는 빈 줄로 설정됩니다.

HAVING 절이 소계 결과에 영향을 줄 수 있음을 명심하세요.

Example

테이블 t를 고려해 보세요:

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);

GROUP BY 섹션에 세 개의 키 표현식이 있으므로, 결과는 모든 키 표현식 조합에 대한 소계가 있는 여덟 개의 테이블을 포함합니다:

• GROUP BY year, month, day
• GROUP BY year, month
• GROUP BY year, day
• GROUP BY year
• GROUP BY month, day
• GROUP BY month
• GROUP BY day
• 그리고 totals.

GROUP BY에서 제외된 컬럼은 0으로 채워집니다.

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │     0 │   5 │       2 │
│ 2019 │     0 │   5 │       1 │
│ 2020 │     0 │  15 │       2 │
│ 2019 │     0 │  15 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   5 │       2 │
│    0 │    10 │  15 │       1 │
│    0 │    10 │   5 │       1 │
│    0 │     1 │  15 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   0 │       4 │
│    0 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   5 │       3 │
│    0 │     0 │  15 │       3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

같은 쿼리는 WITH 키워드를 사용해 쓸 수도 있습니다.

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;

See also

• SQL 표준 호환을 위한 group_by_use_nulls 설정.

WITH TOTALS Modifier

WITH TOTALS modifier가 지정되면 다른 행이 계산됩니다. 이 행은 기본 값을 포함하는 키 컬럼(0 또는 빈 줄)과 모든 행에 걸쳐 계산된 값이 있는 집계 함수 컬럼("total" 값)을 가집니다.

이 추가 행은 JSON*, TabSeparated*, Pretty* 형식에서만 다른 행과 분리되어 생성됩니다:

• XML 및 JSON* 형식에서 이 행은 별도 'totals' 필드로 출력됩니다.
• TabSeparated*, CSV*, Vertical 형식에서 행은 메인 결과 뒤에, 빈 행(다른 데이터 뒤)이 앞에 옵니다.
• Pretty* 형식에서 행은 메인 결과 뒤에 별도 테이블로 출력됩니다.
• Template 형식에서 행은 지정된 템플릿에 따라 출력됩니다.
• 다른 형식에서는 사용할 수 없습니다.

totals는 SELECT 쿼리 결과에 출력되며, INSERT INTO ... SELECT에서는 출력되지 않습니다.

HAVING이 있을 때 WITH TOTALS는 다양한 방식으로 실행될 수 있습니다. 동작은 totals_mode 설정에 따라 다릅니다.

Configuring Totals Processing

기본적으로 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를 통과하지 못한 행을 포함하지 않습니다. 즉 'totals'는 max_rows_to_group_by가 생략된 경우보다 행 수가 적거나 같습니다.

after_having_inclusive – 'totals'에 'max_rows_to_group_by'를 통과하지 못한 모든 행을 포함합니다. 즉 'totals'는 max_rows_to_group_by가 생략된 경우보다 행 수가 많거나 같습니다.

after_having_auto – HAVING을 통과한 행 수를 셉니다. 특정 양(기본 50%)보다 많으면 'totals'에 'max_rows_to_group_by'를 통과하지 못한 모든 행을 포함합니다. 그렇지 않으면 포함하지 않습니다.

totals_auto_threshold – 기본값 0.5. after_having_auto의 계수입니다.

max_rows_to_group_by와 group_by_overflow_mode = 'any'를 사용하지 않으면 after_having의 모든 변형은 같으며, 그중 아무거나(예: after_having_auto) 사용할 수 있습니다.

WITH TOTALS는 서브쿼리에서, JOIN 절의 서브쿼리를 포함해 사용할 수 있습니다(이 경우 각각의 total 값이 결합됩니다).

GROUP BY ALL

GROUP BY ALL은 SELECT된 표현식 중 집계 함수가 아닌 것을 모두 나열하는 것과 동등합니다.

예를 들어:

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY ALL

는 다음과 동일합니다:

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY a * 2, b

특수한 경우로, 집계 함수와 다른 필드를 모두 인자로 가진 함수가 있으면 GROUP BY 키는 그 함수에서 추출할 수 있는 최대 비집계 필드를 포함합니다.

예를 들어:

SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL

는 다음과 동일합니다:

SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)

Examples

Example:

SELECT
    count(),
    median(FetchTiming > 60 ? 60 : FetchTiming),
    count() - sum(Refresh)
FROM hits

MySQL(및 표준 SQL 준수)과 달리, 키나 집계 함수에 없는 일부 컬럼의 어떤 값도 얻을 수 없습니다(상수 표현식 제외). 이를 해결하려면 'any' 집계 함수(첫 번째로 만난 값 얻기) 또는 'min/max'를 사용할 수 있습니다.

Example:

SELECT
    domainWithoutWWW(URL) AS domain,
    count(),
    any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain

만나는 모든 다른 키 값에 대해 GROUP BY는 집계 함수 값 집합을 계산합니다.

GROUPING SETS modifier

이것이 가장 일반적인 modifier입니다. 이 modifier는 여러 집계 키 집합(grouping sets)을 수동으로 지정할 수 있게 해줍니다. 각 grouping set에 대해 집계가 별도로 수행된 후, 모든 결과가 결합됩니다. 컬럼이 grouping set에 없으면 기본값으로 채워집니다.

즉, 위에서 설명한 modifier들은 GROUPING SETS로 표현될 수 있습니다. ROLLUP, CUBE, GROUPING SETS modifier가 있는 쿼리는 구문적으로 동일하지만, 다르게 수행될 수 있습니다. GROUPING SETS가 모든 것을 병렬로 실행하려고 하는 반면, ROLLUP와 CUBE는 단일 스레드에서 집계의 최종 병합을 실행합니다.

원본 컬럼이 기본 값을 포함하는 상황에서는 행이 이러한 컬럼을 키로 사용하는 집계의 일부인지 구분하기 어려울 수 있습니다. 이 문제를 해결하려면 GROUPING 함수를 사용해야 합니다.

Example

다음 두 쿼리는 동등합니다.

-- Query 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

-- Query 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
    (year, month, day),
    (year, month),
    (year),
    ()
);

See also

• SQL 표준 호환을 위한 group_by_use_nulls 설정.

Implementation Details

집계는 컬럼 지향 DBMS의 가장 중요한 기능 중 하나이며, 따라서 그 구현은 ClickHouse에서 가장 많이 최적화된 부분 중 하나입니다. 기본적으로 집계는 해시 테이블을 사용해 메모리에서 수행됩니다. "grouping key" 데이터 타입에 따라 자동으로 선택되는 40개 이상의 특수화가 있습니다.

GROUP BY Optimization Depending on Table Sorting Key

테이블이 어떤 키로 정렬되고 GROUP BY 표현식이 정렬 키의 적어도 접두사를 포함하거나 단사 함수를 포함하면 집계가 더 효과적으로 수행될 수 있습니다. 이 경우 테이블에서 새 키를 읽을 때 집계의 중간 결과를 마무리하고 클라이언트로 보낼 수 있습니다. 이 동작은 optimize_aggregation_in_order 설정으로 켜집니다. 이러한 최적화는 집계 중 메모리 사용을 줄이지만, 어떤 경우에는 쿼리 실행을 느리게 할 수 있습니다.

GROUP BY in External Memory

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를 약 두 배(또는 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 뒤에 LIMIT이 있는 ORDER BY가 있으면, 사용된 RAM 양은 전체 테이블이 아니라 LIMIT의 데이터 양에 따라 달라집니다. LIMIT ... AFTER ... UNTIL 범위는 정렬이 범위에 필요한 행 수를 알 수 없으므로 그런 절감이 없습니다. 그러나 ORDER BY에 LIMIT이 없으면 외부 정렬(max_bytes_before_external_sort)을 활성화하는 것을 잊지 마세요.

더 알아보기 (Learn more)

• Aggregate functions