그룹핑 세트

그룹핑 세트 (GROUPING SETS)

SQL에서 한 번에 여러 차원으로 그룹을 지어 집계하고 싶을 때가 있어요. GROUPING SETS, ROLLUP, CUBE는 GROUP BY 절 안에서 여러 차원에 걸친 그룹핑을 한 쿼리로 수행하게 해주는 강력한 도구예요. 데이터를 여러 각도로 봐야 할 때 정말 유용하죠. 여기서 개념을 차근차근 설명해드릴게요.

출처: DuckDB 공식 문서 — GROUPING SETS

기본 개념

GROUPING SETS, ROLLUP, CUBE는 GROUP BY 절에서 사용해서 하나의 쿼리로 여러 차원에 걸친 그룹핑을 수행할 수 있어요.

참고: 이 문법은 GROUP BY ALL과는 호환되지 않아요.

예제 (Examples)

제공된 네 가지 서로 다른 차원을 따라 평균 소득을 계산해볼게요:

-- () 문법은 빈 집합을 의미해요 (즉, 그룹 없이 집계를 계산)
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ());

같은 차원들을 따라 평균 소득을 계산해요:

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY CUBE (city, street_name);

(city, street_name), (city), () 차원을 따라 평균 소득을 계산해요:

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY ROLLUP (city, street_name);

설명 (Description)

GROUPING SETS는 서로 다른 GROUP BY 절들을 하나의 쿼리에서 같은 집계로 수행해요.

먼저 예제 테이블을 만들게요:

CREATE TABLE students (course VARCHAR, type VARCHAR);
INSERT INTO students (course, type)
VALUES
    ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters'),
    ('CS', NULL), ('CS', NULL), ('Math', NULL);

네 가지 그룹 세트로 집계를 수행하면:

SELECT course, type, count(*)
FROM students
GROUP BY GROUPING SETS ((course, type), course, type, ());

위 쿼리에서 우리는 course, type, course, type, () (빈 그룹)이라는 네 가지 서로 다른 세트로 그룹을 나눴어요. 결과에서 해당 결과의 그룹핑 세트에 포함되지 않은 그룹은 NULL로 표시돼요. 즉, 위 쿼리는 다음 UNION ALL 절의 조합과 동일해요:

-- Group by course, type:
SELECT course, type, count(*)
FROM students
GROUP BY course, type
UNION ALL
-- Group by type:
SELECT NULL AS course, type, count(*)
FROM students
GROUP BY type
UNION ALL
-- Group by course:
SELECT course, NULL AS type, count(*)
FROM students
GROUP BY course
UNION ALL
-- Group by nothing:
SELECT NULL AS course, NULL AS type, count(*)
FROM students;

CUBE와 ROLLUP은 흔히 사용되는 그룹핑 세트를 쉽게 만들어주는 문법적 설탕(syntactic sugar)이에요.

ROLLUP 절은 그룹핑 세트의 모든 "하위 그룹(sub-groups)"을 만들어내요. 예를 들어 ROLLUP (country, city, zip)은 그룹핑 세트 (country, city, zip), (country, city), (country), ()를 만들어내죠. 이는 GROUP BY 절의 다양한 상세 수준(detail level)을 만들어내는 데 유용해요. ROLLUP 절에 있는 항목 수가 n이라면 n+1개의 그룹핑 세트를 만들어요.

CUBE는 입력값의 모든 조합에 대한 그룹핑 세트를 만들어내요. 예를 들어 CUBE (country, city, zip)는 (country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), ()를 만들어내죠. 이는 2^n개의 그룹핑 세트를 만들어요.

GROUPING_ID()로 그룹핑 세트 식별하기

GROUPING SETS, ROLLUP, CUBE로 생성된 슈퍼-집계(super-aggregate) 행은 종종 해당 그룹핑 열에 반환된 NULL 값으로 식별할 수 있어요. 하지만 그룹핑에 사용된 열 자체가 실제 NULL 값을 포함할 수 있다면, 결과셋의 값이 데이터 자체에서 나온 "진짜" NULL인지, 아니면 그룹핑 구조에 의해 생성된 NULL인지 구분하기 어려워질 수 있어요. GROUPING_ID() 또는 GROUPING() 함수는 결과에서 슈퍼-집계 행을 생성한 그룹을 식별하도록 설계됐어요.

GROUPING_ID()는 그룹핑을 구성하는 열 표현식을 인자로 받는 집계 함수예요. BIGINT 값을 반환하지요. 슈퍼-집계 행이 아닌 행은 0을 반환해요. 하지만 슈퍼-집계 행의 경우, 슈퍼-집계가 생성된 그룹을 구성하는 표현식의 조합을 식별하는 정수 값을 반환해요. 예제가 도움이 될 거예요. 다음 쿼리를 보죠:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT y, q, m, GROUPING_ID(y, q, m) AS "grouping_id()"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

결과는 다음과 같아요:

이 예제에서 가장 낮은 그룹핑 수준은 그룹핑 세트 (y, q, m)으로 정의된 월(month) 수준이에요. 그 수준에 해당하는 결과 행은 단순히 집계 행이며, GROUPING_ID(y, q, m) 함수는 그 행들에 대해 0을 반환해요. 그룹핑 세트 (y, q)는 월 수준에 대한 슈퍼-집계 행을 만들어내고, m 열에 NULL 값을 남기며, GROUPING_ID(y, q, m)은 1을 반환해요. 그룹핑 세트 (y)는 분기(quarter) 수준에 대한 슈퍼-집계 행을 만들어내고, m과 q 열에 NULL 값을 남기며, GROUPING_ID(y, q, m)은 3을 반환하지요. 마지막으로 () 그룹핑 세트는 전체 결과셋에 대한 하나의 슈퍼-집계 행을 만들어내고, y, q, m에 NULL 값을 남기며 GROUPING_ID(y, q, m)은 7을 반환해요.

반환값과 그룹핑 세트 사이의 관계를 이해하려면, GROUPING_ID(y, q, m)이 비트필드(bitfield)에 쓰는 것으로 생각해볼 수 있어요. 여기서 첫 번째 비트는 GROUPING_ID()에 전달된 마지막 표현식에 해당하고, 두 번째 비트는 그 직전에 전달된 표현식에 해당하는 식이에요. GROUPING_ID()를 BIT로 캐스팅하면 더 명확해져요:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT
    y, q, m,
    GROUPING_ID(y, q, m) AS "grouping_id(y, q, m)",
    right(GROUPING_ID(y, q, m)::BIT::VARCHAR, 3) AS "y_q_m_bits"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

다음과 같은 결과를 반환해요:

GROUPING_ID()에 전달되는 표현식의 개수나 순서는 GROUPING SETS 절에 실제로 나타나는 그룹 정의(또는 ROLLUP과 CUBE가 암시하는 그룹)와 무관하다는 점을 참고하세요. GROUPING_ID()에 전달된 표현식이 GROUPING SETS 절 어딘가에 나타나는 표현식이기만 하면, 해당 표현식이 슈퍼-집계로 롤업될 때마다 GROUPING_ID()는 그 표현식의 위치에 해당하는 비트를 설정해요.

더 알아보기 (Learn more)

• GROUP BY 절
• HAVING 절
• 집계 함수 (Aggregate Functions)