PIVOT 구문

PIVOT 구문 (PIVOT Statement)

PIVOT 구문은 한 열 내의 고유한 값들을 각자의 열로 분리할 수 있게 해줘요. 그 새 열들의 값은 각 고유 값과 일치하는 행 부분집합에 대한 집계 함수로 계산돼요.

DuckDB는 SQL 표준 PIVOT 문법과, 피보팅하면서 생성할 열을 자동으로 감지하는 단순화된 PIVOT 문법을 모두 구현해요. PIVOT 키워드 대신 PIVOT_WIDER도 사용할 수 있어요.

출처: 문서

본문

PIVOT 구문이 어떻게 구현되는지에 대한 자세한 내용은 [Pivot Internals 사이트]({% link docs/current/internals/pivot.md %}#pivot)를 참고해요.

[UNPIVOT 구문]({% link docs/current/sql/statements/unpivot.md %})은 PIVOT 구문의 역이에요.

단순화된 `PIVOT` 문법

전체 문법 다이어그램은 아래에 있지만, 단순화된 PIVOT 문법은 스프레드시트 피벗 테이블 명명 규칙으로 요약하면 이렇게 돼요:

PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩
ORDER BY ⟨columns_with_order_directions⟩
LIMIT ⟨number_of_rows⟩;

ON, USING, GROUP BY 절은 각각 선택적이지만, 모두 생략할 수는 없어요.

예시 데이터

모든 예시는 아래 쿼리들이 만든 데이터셋을 사용해요:

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

SELECT *
FROM cities;

country	name	year	population
NL	Amsterdam	2000	1005
NL	Amsterdam	2010	1065
NL	Amsterdam	2020	1158
US	Seattle	2000	564
US	Seattle	2010	608
US	Seattle	2020	738
US	New York City	2000	8015
US	New York City	2010	8175
US	New York City	2020	8772

`PIVOT ON` 및 `USING`

아래 PIVOT 구문을 사용해 각 연도에 대해 별도의 열을 만들고 각 연도의 총 인구를 계산해요. ON 절은 어떤 열을 별도의 열로 분할할지 지정해요. 이것은 스프레드시트 피벗 테이블의 columns 파라미터와 동등해요.

USING 절은 별도 열로 분할된 값들을 어떻게 집계할지 결정해요. 이것은 스프레드시트 피벗 테이블의 values 파라미터와 동등해요. USING 절이 포함되지 않으면 기본값은 count(*)이에요.

PIVOT cities
ON year
USING sum(population);

country	name	2000	2010	2020
NL	Amsterdam	1005	1065	1158
US	Seattle	564	608	738
US	New York City	8015	8175	8772

위 예시에서 sum 집계는 항상 단일 값에 대해 동작해요. 집계 없이 데이터가 표시되는 방향만 바꾸고 싶다면 first 집계 함수를 사용해요. 이 예시에서는 숫자 값을 피보팅하지만, first 함수는 텍스트 열을 피보팅할 때 아주 잘 동작해요. (이것은 스프레드시트 피벗 테이블에서 어렵지만 DuckDB에서는 쉬운 일이에요!)

이 쿼리는 위와 동일한 결과를 만들어요:

PIVOT cities
ON year
USING first(population);

참고: SQL 문법은 USING 절에서 집계 함수와 함께 [FILTER 절]({% link docs/current/sql/query_syntax/filter.md %})을 허용해요. DuckDB에서 PIVOT 구문은 현재 이를 지원하지 않으며 조용히 무시해요.

`PIVOT ON`, `USING`, `GROUP BY`

기본적으로 PIVOT 구문은 ON이나 USING 절에 지정되지 않은 모든 열을 유지해요. 특정 열만 포함하고 더 집계하려면 GROUP BY 절에 열을 지정해요. 이것은 스프레드시트 피벗 테이블의 rows 파라미터와 동등해요.

아래 예시에서 name 열은 더 이상 출력에 포함되지 않고, 데이터가 country 수준으로 집계돼요.

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

country	2000	2010	2020
NL	1005	1065	1158
US	8579	8783	9510

`ON` 절의 `IN` 필터

ON 절의 열 내 특정 값에만 별도의 열을 만들려면 선택적 IN 표현식을 사용해요. 예를 들어 특별한 이유 없이 2020년을 잊고 싶다고 해볼게요...

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;

country	2000	2010
NL	1005	1065
US	8579	8783

절당 여러 표현식

ON과 GROUP BY 절에는 여러 열을, USING 절에는 여러 집계 표현식을 포함할 수 있어요.

여러 `ON` 열 및 `ON` 표현식

여러 열을 각자의 열로 피보팅할 수 있어요. DuckDB는 각 ON 절 열에서 고유 값을 찾고, 그 값들의 모든 조합(데카르트 곱)에 대해 새 열 하나를 만들어요.

아래 예시에서 고유 국가와 고유 도시의 모든 조합이 각자의 열을 받아요. 일부 조합은 기본 데이터에 존재하지 않을 수 있으므로, 그 열들은 NULL 값으로 채워져요.

PIVOT cities
ON country, name
USING sum(population);

year	NL_Amsterdam	NL_New York City	NL_Seattle	US_Amsterdam	US_New York City	US_Seattle
2000	1005	NULL	NULL	NULL	8015	564
2010	1065	NULL	NULL	NULL	8175	608
2020	1158	NULL	NULL	NULL	8772	738

기본 데이터에 존재하는 값의 조합만 피보팅하려면 ON 절에서 표현식을 사용해요. 여러 표현식 및/또는 열을 제공할 수 있어요.

여기서 country와 name이 연결되고, 결과 연결 각각이 각자의 열을 받아요. 임의의 비집계 표현식을 사용할 수 있어요. 이 경우 밑줄로 연결하는 것은 여러 ON 열이 제공될 때(앞 예시처럼) PIVOT 절이 사용하는 명명 규칙을 모방하기 위한 것이에요.

PIVOT cities
ON country || '_' || name
USING sum(population);

year	NL_Amsterdam	US_New York City	US_Seattle
2000	1005	8015	564
2010	1065	8175	608
2020	1158	8772	738

여러 `USING` 표현식

USING 절의 각 표현식에 별칭을 포함할 수도 있어요. 별칭은 밑줄(_) 뒤에 생성된 열 이름에 붙어요. 이로 인해 USING 절에 여러 표현식이 포함될 때 열 명명 규칙이 훨씬 깔끔해져요.

이 예시에서 population 열의 sum과 max가 각 연도별로 계산되고 별도의 열로 분할돼요.

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;

country	2000_total	2000_max	2010_total	2010_max	2020_total	2020_max
US	8579	8015	8783	8175	9510	8772
NL	1005	1005	1065	1065	1158	1158

여러 `GROUP BY` 열

여러 GROUP BY 열을 제공할 수도 있어요. 열 위치(1, 2 등)가 아니라 열 이름을 사용해야 하고, GROUP BY 절에는 표현식이 지원되지 않는다는 점을 주목해요.

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

country	name	2000	2010	2020
NL	Amsterdam	1005	1065	1158
US	Seattle	564	608	738
US	New York City	8015	8175	8772

`SELECT` 구문 안에서 `PIVOT` 사용

PIVOT 구문은 CTE([Common Table Expression, 또는 WITH 절]({% link docs/current/sql/query_syntax/with.md %}))나 서브쿼리로 SELECT 구문 안에 포함될 수 있어요. 이로 인해 PIVOT을 다른 SQL 로직과 함께 사용할 수 있고, 한 쿼리에서 여러 PIVOT을 사용할 수도 있어요.

CTE 안에서 SELECT는 필요 없어요. PIVOT 키워드가 그 자리를 차지한다고 생각하면 돼요.

WITH pivot_alias AS (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
)
SELECT * FROM pivot_alias;

PIVOT은 서브쿼리에 사용할 수 있으며 괄호로 감싸야 해요. 이 동작은 SQL 표준 Pivot과 다른데, 후속 예시에서 설명할게요.

SELECT *
FROM (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
) pivot_alias;

여러 `PIVOT` 구문

각 PIVOT은 마치 SELECT 노드처럼 취급될 수 있으므로, 서로 조인하거나 다른 방식으로 조작할 수 있어요.

예를 들어 두 PIVOT 구문이 같은 GROUP BY 표현식을 공유하면, GROUP BY 절의 열을 사용해 더 넓은 피벗으로 조인할 수 있어요.

SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);

country	2000	2010	2020	Amsterdam	New York City	Seattle
NL	1005	1065	1158	3228	NULL	NULL
US	8579	8783	9510	NULL	24962	1910

단순화된 `PIVOT` 전체 문법 다이어그램

아래는 PIVOT 구문의 전체 문법 다이어그램이에요.

SQL 표준 `PIVOT` 문법

전체 문법 다이어그램은 아래에 있지만, SQL 표준 PIVOT 문법은 다음과 같이 요약할 수 있어요:

SELECT *
FROM ⟨dataset⟩
PIVOT (
    ⟨values⟩
    FOR
        ⟨column_1⟩ IN (⟨in_list⟩)
        ⟨column_2⟩ IN (⟨in_list⟩)
        ...
    GROUP BY ⟨rows⟩
);

단순화된 문법과 달리, 피보팅할 각 열에 대해 IN 절을 지정해야 해요. 동적 피보팅에 관심이 있다면 단순화된 문법을 권장해요.

FOR 절의 표현식은 쉼표로 구분되지 않지만, value와 GROUP BY 표현식은 쉼표로 구분되어야 한다는 점을 주목해요!

예시 (Examples)

이 예시는 단일 value 표현식, 단일 column 표현식, 단일 row 표현식을 사용해요:

SELECT *
FROM cities
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
);

country	2000	2010	2020
NL	1005	1065	1158
US	8579	8783	9510

이 예시는 다소 억지스럽지만 FOR 절에서 여러 value 표현식과 여러 열을 사용하는 예시로 쓰인다.

SELECT *
FROM cities
PIVOT (
    sum(population) AS total,
    count(population) AS count
    FOR
        year IN (2000, 2010)
        country IN ('NL', 'US')
);

name	2000_NL_total	2000_NL_count	2000_US_total	2000_US_count	2010_NL_total	2010_NL_count	2010_US_total	2010_US_count
Amsterdam	1005	1	NULL	0	1065	1	NULL	0
Seattle	NULL	0	564	1	NULL	0	608	1
New York City	NULL	0	8015	1	NULL	0	8175	1

SQL 표준 `PIVOT` 전체 문법 다이어그램

아래는 SQL 표준 버전의 PIVOT 구문 전체 문법 다이어그램이에요.

제한 사항 (Limitations)

PIVOT는 현재 집계 함수만 받아들이며, 표현식은 허용되지 않아요. 예를 들어 다음 쿼리는 인구를 수천 명이 아니라 수로 얻으려고 해요 (즉, 564 대신 564000):

PIVOT cities
ON year
USING sum(population) * 1000;

하지만 다음 오류로 실패해요:

Catalog Error:
* is not an aggregate function

이 제한을 우회하려면 집계만으로 PIVOT을 수행한 다음 [COLUMNS 표현식]({% link docs/current/sql/expressions/star.md %}#columns-expression)을 사용해요:

SELECT country, name, 1000 * COLUMNS(* EXCLUDE (country, name))
FROM (
    PIVOT cities
    ON year
    USING sum(population)
);

더 알아보기 (Learn more)

PIVOT의 내부 구현은 [Pivot Internals]({% link docs/current/internals/pivot.md %}) 문서를 참고해요.

PIVOT 구문

PIVOT 구문 (PIVOT Statement)

본문

단순화된 PIVOT 문법

예시 데이터

PIVOT ON 및 USING

PIVOT ON, USING, GROUP BY

ON 절의 IN 필터

절당 여러 표현식

여러 ON 열 및 ON 표현식

여러 USING 표현식

여러 GROUP BY 열

SELECT 구문 안에서 PIVOT 사용

여러 PIVOT 구문

단순화된 PIVOT 전체 문법 다이어그램

SQL 표준 PIVOT 문법