PIVOT

PIVOT (피벗)

FROM 절의 선택적 하위 절로, 입력 관계의 행을 출력 컬럼으로 회전시키는 문법이에요. 범주별로 컬럼을 펼쳐 보고 싶을 때 사용해요.

출처: 문서

본문

PIVOT 절은 FROM 절의 선택적 하위 절이에요. 하나 이상의 피벗 컬럼(pivot column) 로 행을 분할하고 각 피벗 값(pivot value) 마다 하나 이상의 집계(aggregation) 를 계산해 입력 관계의 행을 출력 컬럼으로 회전시켜요. 피벗의 입력은 테이블, 뷰, 서브쿼리예요. 피벗의 출력은 관계(relation)이므로, 그 자체로 FROM 절에 나타나거나 별칭을 붙이거나 다른 PIVOT의 입력이 될 수 있어요.

PIVOT는 입력의 각 행이 범주형 차원(월, 지역, 상태 같은)을 따라 하나의 관측치를 나타내고, 보고서가 범주별로 하나의 컬럼을 표시해야 할 때 유용해요. 일반적인 사용 사례는 다음과 같아요:

• 시간 버킷별로 측정값 요약
• 상태나 범주별로 컬럼 하나씩 만들기
• 반복적인 CASE나 FILTER 표현식을 쓰지 않고 집계 지표를 나란히 비교

예시

다음 예시에서 sales는 지역/월별로 한 행씩 기록하고, PIVOT는 각 지역 안에서 월별로 컬럼 하나를 만들어요:

SELECT *
FROM sales PIVOT (
    sum(amount) AS total
    FOR month IN (1 AS jan, 2 AS feb, 3 AS mar)
    GROUP BY region
    )

출력은 region, jan_total, feb_total, mar_total 컬럼을 가져요.

이어지는 섹션에서 PIVOT 절의 모든 하위 절을 설명해요.

집계 (Aggregations)

sum(amount) AS total

각 집계는 하나 이상의 집계 함수 호출을 포함하는 표현식이에요. 표현식은 피벗 값마다 한 번씩 평가되며, 각 집계는 해당 피벗 값과 일치하는 행에 한정돼요. 피벗 값 스코핑이 적용되는 대상은 집계이므로, 집계가 없는 표현식은 거부돼요. 윈도우 함수와 그룹핑 연산은 집계가 아니며, 집계 안에 허용되지 않아요.

집계 별칭은 출력 컬럼 이름의 일부가 돼요(출력 컬럼 참고). 별칭은 단일 집계 경우에는 선택적이지만, PIVOT가 둘 이상의 집계를 선언하면 각 집계가 출력 컬럼에 구분되는 접미사를 기여하도록 별칭이 필수예요.

그 외에도 PIVOT는 Trino가 집계 select 항목으로 받아들이는 어떤 표현식이든 허용해요. 예를 들어 다음 모두 동작해요:

sum(amount)                                    -- 단일 집계
avg(amount) * 100 AS pct                       -- 집계에 대한 표현식
sum(amount) - sum(refund) AS net               -- 한 슬롯에 여러 집계
sum(amount) FILTER (WHERE amount > 0) AS gains  -- 집계에 명시적 FILTER

슬롯 표현식이 여러 집계 호출을 포함하면, 피벗 필터는 각 집계에 개별적으로 적용돼요. 그래서 sum(amount) - sum(refund)는 두 sum 모두를 현재 피벗 값에 해당하는 행으로 필터링해요.

집계는 자체 FILTER (WHERE ...) 절을 가질 수도 있어요. 피벗 값 스코핑과 결합되는데, 집계는 현재 피벗 값과 명시적 필터 조건을 모두 만족하는 행만 보게 돼요.

서브쿼리는 집계 호출 내부 어디든 — 인자, FILTER, ORDER BY — 나타날 수 있지만, 집계 표현식의 다른 곳에는 나타날 수 없어요.

피벗 컬럼과 IN 목록

FOR month IN (1 AS jan, 2 AS feb, 3 AS mar)

FOR 절은 피벗 컬럼(복합 키면 괄호로 묶은 피벗 컬럼 목록)을 이름짓고, IN 절은 출력 컬럼이 되는 값을 공급해요. 각 값은 상수 표현식이에요. 하나의 출력 컬럼을 이름 지으므로 쿼리의 모든 입력 행에 대해 같은 값을 가져야 해요. 따라서 입력 관계의 컬럼을 참조하거나, 서브쿼리를 포함하거나, random() 같은 비결정적 함수를 호출할 수 없어요. 쿼리에는 고정됐지만 쿼리 간엔 고정되지 않은 값(예: current_date()나 쿼리 파라미터)은 허용돼요.

행이 값과 일치하는 것은 pivot_column = value가 성립할 때이며, 일반적인 = 의미를 따르면서 컬럼과 값이 공통 comparable 슈퍼타입으로 강제(상위 타입일 수 있음)돼요. 그래서 INTEGER 피벗 컬럼을 BIGINT 값과 매칭할 수 있고, 양쪽 모두 BIGINT로 비교돼요.

여러 피벗 컬럼의 경우 튜플 값을 순서에 맞게 제공해요:

FOR (region, month) IN (('NA', 1) AS na_jan, ('EU', 1) AS eu_jan)

각 튜플은 피벗 컬럼 목록과 같은 길이(arity)여야 해요.

값 별칭은 그 값의 출력 컬럼 이름을 제어해요. 실제로는 강력히 권장돼요 — 별칭이 없으면 컬럼 이름이 값 표현식의 SQL 텍스트에서 파생되므로(1과 '1'이 각각 이름이 1, '1'인 별개의 컬럼이 됨).

NULL은 허용된 값이지만, Trino의 표준 = 의미로 처리돼요. pivot_column = NULL 술어는 UNKNOWN이므로, 해당 출력 컬럼은 항상 빈 입력 집계 결과(sum이면 NULL, count면 0 등)를 담아요. 피벗 컬럼이 NULL인 행에 대한 컬럼을 만들려면 NULL IN 값에 의존하지 말고 소스 관계에서 그 버킷을 명시적으로 제공해요.

GROUP BY

GROUP BY region

PIVOT 안의 선택적 GROUP BY 절은 어떤 차원이 추가 출력 컬럼으로 보존되는지 제어해요. 최상위 GROUP BY와 같은 형태를 받아들여요: 단순 표현식, GROUP BY (), GROUPING SETS, CUBE, ROLLUP. 각 그룹핑 표현식은 출력 컬럼으로 투영돼요. GROUP BY AUTO는 허용되지 않는데, PIVOT에는 그룹핑 컬럼을 파생할 select 목록이 없기 때문이에요.

GROUP BY를 생략하면 GROUP BY ()와 동일하게 동작해요. 결과는 피벗 출력 컬럼만 있는 단일 행이에요.

출력 컬럼

출력 컬럼 목록은 순서대로 다음과 같아요:

• GROUP BY가 도입한 컬럼(선언 순서대로), 있으면.
• 피벗 값 그룹별로 블록 하나(선언 순서대로).

각 블록 안에서 컬럼은 집계가 선언된 순서대로 집계 슬롯당 한 번씩 나타나요. 컬럼 이름은 값의 이름이고, 집계에 별칭이 있으면 집계 별칭이 뒤에 붙어요:

그래서 sum(amount) FOR month IN (1 AS jan)은 jan을 만들고, sum(amount) AS total FOR month IN (1 AS jan)은 jan_total을 만들어요. 여러 집계가 있는 PIVOT는 각 집계에 별칭이 필요하므로, 컬럼 이름은 항상 두 번째 형태를 취해요.

식별자에서 온 별칭은 다른 것과 마찬가지로 정규화돼요. 따옴표 없는 별칭은 소문자화되고, 따옴표 있는 별칭은 대소문자를 유지해요. 그래서 AS Jan은 jan 컬럼을, AS "Jan"은 Jan 컬럼을 이름지어요.

두 출력 컬럼이 이름을 공유할 수도 있는데, SELECT 목록과 같아요. 모든 컬럼을 선택하는 쿼리는 각각을 반환하고, 공유 이름을 이름으로 참조하는 쿼리는 모호하다며 실패해요.

피벗 관계 별칭

PIVOT 절 자체에 선택적 컬럼 별칭과 함께 별칭을 붙일 수 있어요:

SELECT p.r, p.jan, p.feb
FROM sales PIVOT (
    sum(amount) FOR month IN (1 AS jan, 2 AS feb)
    GROUP BY region
    ) AS p (r, jan, feb)

컬럼 별칭 목록은 있을 때 출력 컬럼 수와 일치해야 해요. 서브쿼리 별칭에 지원되는 것과 같은 형태예요.

더 알아보기 (Learn more)

피벗을 둘러싼 쿼리 구조는 SELECT 문서에서, 행 패턴 인식은 MATCH_RECOGNIZE 문서에서 다루고 있어요.