Pivot 내부 구현

Pivot 내부 구현 (Pivot Internals)

PIVOT

[피보팅]({% link docs/current/sql/statements/pivot.md %})은 SQL 쿼리 재작성과 더 높은 성능을 위한 전용 PhysicalPivot 연산자의 조합으로 구현돼요. 각 PIVOT은 리스트로의 일련의 집계로 구현되고, 전용 PhysicalPivot 연산자가 그 리스트들을 컬럼 이름과 값으로 변환해요. 피보팅 시 생성할 열이 동적으로 감지되는 경우(IN 절을 사용하지 않을 때) 추가 전처리 단계가 필요해요.

출처: 문서

본문

DuckDB는 대부분의 SQL 엔진처럼 쿼리 시작 시 모든 컬럼 이름과 타입을 알아야 해요. PIVOT 구문의 결과로 생성해야 할 열을 자동으로 감지하려면, 이를 여러 쿼리로 변환해야 해요. [ENUM 타입]({% link docs/current/sql/data_types/enum.md %})이 열이 되어야 할 고유 값들을 찾는 데 사용돼요. 그런 다음 각 ENUM이 PIVOT 구문의 IN 절 중 하나에 주입돼요.

IN 절이 ENUM으로 채워진 후, 쿼리는 리스트로의 일련의 집계로 다시 재작성돼요.

예를 들어:

PIVOT cities
ON year
USING sum(population);

는 처음에 다음과 같이 변환돼요:

CREATE TEMPORARY TYPE __pivot_enum_0_0 AS ENUM (
    SELECT DISTINCT
        year::VARCHAR
    FROM cities
    ORDER BY
        year
    );
PIVOT cities
ON year IN __pivot_enum_0_0
USING sum(population);

그리고 마지막으로 다음과 같이 변환돼요:

SELECT country, name, list(year), list(population_sum)
FROM (
    SELECT country, name, year, sum(population) AS population_sum
    FROM cities
    GROUP BY ALL
)
GROUP BY ALL;

이는 다음과 같은 결과를 만들어요:

PhysicalPivot 연산자는 그 리스트들을 컬럼 이름과 값으로 변환해 다음 결과를 반환해요:

UNPIVOT

내부 구현 (Internals)

언피보팅은 전적으로 SQL 쿼리로의 재작성으로 구현돼요. 각 UNPIVOT은 컬럼 이름 리스트와 컬럼 값 리스트에 대해 동작하는 일련의 unnest 함수로 구현돼요. 동적 언피보팅 시 COLUMNS 표현식이 먼저 평가되어 컬럼 리스트를 계산해요.

예를 들어:

UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
    NAME month
    VALUE sales;

는 다음과 같이 변환돼요:

SELECT
    empid,
    dept,
    unnest(['jan', 'feb', 'mar', 'apr', 'may', 'jun']) AS month,
    unnest(["jan", "feb", "mar", "apr", "may", "jun"]) AS sales
FROM monthly_sales;

month를 채우기 위한 텍스트 문자열 리스트를 만드는 데는 작은따옴표를, sales에 사용할 컬럼 값을 가져오는 데는 큰따옴표를 사용한다는 점을 주목해요. 이는 초기 예시와 동일한 결과를 만들어요:

더 알아보기 (Learn more)

PIVOT 구문의 사용 방법은 [PIVOT Statement]({% link docs/current/sql/statements/pivot.md %}) 문서를 참고해요.