UNPIVOT 문
UNPIVOT 문 (UNPIVOT Statement)
UNPIVOT 문은 여러 열을 더 적은 열로 쌓아 올릴 수 있게 해 줘요. 기본적인 경우 여러 열이 NAME 열(원본 열 이름을 담음)과 VALUE 열(원본 열의 값을 담음) 두 열로 쌓여요.
DuckDB는 SQL 표준 UNPIVOT 문법과 간소화된 UNPIVOT 문법을 모두 구현해요. 둘 다 COLUMNS 표현식을 사용해 unpivot할 열을 자동으로 감지할 수 있어요. PIVOT_LONGER를 UNPIVOT 키워드 대신 사용할 수도 있어요.
UNPIVOT 문이 어떻게 구현되는지에 대한 자세한 내용은 Pivot Internals 사이트를 참고해요.
PIVOT문은UNPIVOT문의 역연산이에요.
출처: 문서
본문
간소화된 UNPIVOT 문법 (Simplified UNPIVOT Syntax)
전체 문법 다이어그램은 아래에 있지만, 간소화된 UNPIVOT 문법은 스프레드시트 피벗 테이블 명명 규칙을 써서 다음과 같이 요약할 수 있어요:
UNPIVOT ⟨dataset⟩
ON ⟨column(s)⟩
INTO
NAME ⟨name_column_name⟩
VALUE ⟨value_column_name(s)⟩
ORDER BY ⟨column(s)_with_order_direction(s)⟩
LIMIT ⟨number_of_rows⟩;
예시 데이터 (Example Data)
모든 예시는 아래 쿼리로 만든 데이터셋을 사용해요:
CREATE OR REPLACE TABLE monthly_sales
(empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
INSERT INTO monthly_sales VALUES
(1, 'electronics', 1, 2, 3, 4, 5, 6),
(2, 'clothes', 10, 20, 30, 40, 50, 60),
(3, 'cars', 100, 200, 300, 400, 500, 600);
FROM monthly_sales;
| empid | dept | Jan | Feb | Mar | Apr | May | Jun |
|---|---|---|---|---|---|---|---|
| 1 | electronics | 1 | 2 | 3 | 4 | 5 | 6 |
| 2 | clothes | 10 | 20 | 30 | 40 | 50 | 60 |
| 3 | cars | 100 | 200 | 300 | 400 | 500 | 600 |
수동 UNPIVOT
가장 전형적인 UNPIVOT 변환은 이미 피벗된 데이터를 다시 가져와 이름용 열과 값용 열로 각각 쌓는 것이에요. 이 경우 모든 월이 month 열과 sales 열로 쌓여요.
UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
NAME month
VALUE sales;
| empid | dept | month | sales |
|---|---|---|---|
| 1 | electronics | Jan | 1 |
| 1 | electronics | Feb | 2 |
| 1 | electronics | Mar | 3 |
| 1 | electronics | Apr | 4 |
| 1 | electronics | May | 5 |
| 1 | electronics | Jun | 6 |
| 2 | clothes | Jan | 10 |
| 2 | clothes | Feb | 20 |
| 2 | clothes | Mar | 30 |
| 2 | clothes | Apr | 40 |
| 2 | clothes | May | 50 |
| 2 | clothes | Jun | 60 |
| 3 | cars | Jan | 100 |
| 3 | cars | Feb | 200 |
| 3 | cars | Mar | 300 |
| 3 | cars | Apr | 400 |
| 3 | cars | May | 500 |
| 3 | cars | Jun | 600 |
COLUMNS 표현식으로 동적 UNPIVOT
많은 경우 unpivot할 열의 수를 미리 정하기 어려워요. 이 데이터셋의 경우 새 월이 추가될 때마다 위 쿼리를 바꿔야 해요. COLUMNS 표현식을 사용하면 empid나 dept가 아닌 모든 열을 선택할 수 있어요. 이러면 월이 몇 개나 추가되든 동작하는 동적 unpivoting이 가능해져요. 아래 쿼리는 위 쿼리와 동일한 결과를 돌려줘요.
UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
NAME month
VALUE sales;
| empid | dept | month | sales |
|---|---|---|---|
| 1 | electronics | Jan | 1 |
| 1 | electronics | Feb | 2 |
| 1 | electronics | Mar | 3 |
| 1 | electronics | Apr | 4 |
| 1 | electronics | May | 5 |
| 1 | electronics | Jun | 6 |
| 2 | clothes | Jan | 10 |
| 2 | clothes | Feb | 20 |
| 2 | clothes | Mar | 30 |
| 2 | clothes | Apr | 40 |
| 2 | clothes | May | 50 |
| 2 | clothes | Jun | 60 |
| 3 | cars | Jan | 100 |
| 3 | cars | Feb | 200 |
| 3 | cars | Mar | 300 |
| 3 | cars | Apr | 400 |
| 3 | cars | May | 500 |
| 3 | cars | Jun | 600 |
여러 값 열로 UNPIVOT
UNPIVOT 문은 더 많은 유연성이 있어요: 대상 열이 2개보다 많을 수 있어요. 이는 데이터셋이 피벗된 정도를 줄이는 게 목표지만 피벗된 열을 완전히 쌓지는 않으려 할 때 유용해요. 이를 보여 주기 위해 아래 쿼리는 분기(quarter) 내 각 월의 번호(월 1, 2, 3)마다 별도 열이 있고, 각 분기에 대해 별도 행이 있는 데이터셋을 만들어요. 분기가 월보다 적기 때문에 이러면 데이터셋이 길어지지만, 위 예시만큼 길지는 않아요.
이를 위해 ON 절에 여러 열 세트를 포함해요. q1과 q2 별칭은 선택적이에요. ON 절의 각 열 세트의 열 수는 VALUE 절의 열 수와 일치해야 해요.
UNPIVOT monthly_sales
ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
INTO
NAME quarter
VALUE month_1_sales, month_2_sales, month_3_sales;
| empid | dept | quarter | month_1_sales | month_2_sales | month_3_sales |
|---|---|---|---|---|---|
| 1 | electronics | q1 | 1 | 2 | 3 |
| 1 | electronics | q2 | 4 | 5 | 6 |
| 2 | clothes | q1 | 10 | 20 | 30 |
| 2 | clothes | q2 | 40 | 50 | 60 |
| 3 | cars | q1 | 100 | 200 | 300 |
| 3 | cars | q2 | 400 | 500 | 600 |
SELECT 문 안에서 UNPIVOT 사용하기
UNPIVOT 문은 SELECT 문 안에 CTE(공통 테이블 표현식, 또는 WITH 절) 또는 서브쿼리로 포함될 수 있어요. 이러면 UNPIVOT을 다른 SQL 로직과 함께 사용할 수 있고, 하나의 쿼리에서 여러 UNPIVOT을 사용할 수도 있어요.
CTE 안에는 SELECT가 필요 없어요. UNPIVOT 키워드가 그 자리를 차지한다고 생각하면 돼요.
WITH unpivot_alias AS (
UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
NAME month
VALUE sales
)
SELECT * FROM unpivot_alias;
UNPIVOT은 서브쿼리에서 사용할 수 있으며 괄호로 감싸야 해요. 이 동작은 이후 예시에서 보여 주는 것처럼 SQL 표준 Unpivot과 다르다는 점에 유의해요.
SELECT *
FROM (
UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
NAME month
VALUE sales
) unpivot_alias;
UNPIVOT 문 안의 표현식
DuckDB는 단일 열만 다루는 한 UNPIVOT 문 안에서 표현식을 허용해요. 이를 사용해 계산뿐 아니라 명시적 캐스팅을 수행할 수 있어요. 예를 들어:
UNPIVOT
(SELECT 42 AS col1, 'woot' AS col2)
ON
(col1 * 2)::VARCHAR,
col2;
| name | value |
|---|---|
| col1 | 84 |
| col2 | woot |
간소화된 UNPIVOT 전체 문법 다이어그램
아래는 UNPIVOT 문의 전체 문법 다이어그램이에요.
SQL 표준 UNPIVOT 문법 (SQL Standard UNPIVOT Syntax)
전체 문법 다이어그램은 아래에 있지만, SQL 표준 UNPIVOT 문법은 다음과 같이 요약할 수 있어요:
FROM [dataset]
UNPIVOT [INCLUDE NULLS] (
[value-column-name(s)]
FOR [name-column-name] IN [column(s)]
);
name-column-name 표현식에는 열이 하나만 포함될 수 있다는 점에 유의해요.
SQL 표준 UNPIVOT 수동
SQL 표준 문법을 사용해 기본 UNPIVOT 연산을 완료하려면 몇 가지만 더하면 돼요.
FROM monthly_sales UNPIVOT (
sales
FOR month IN (jan, feb, mar, apr, may, jun)
);
| empid | dept | month | sales |
|---|---|---|---|
| 1 | electronics | Jan | 1 |
| 1 | electronics | Feb | 2 |
| 1 | electronics | Mar | 3 |
| 1 | electronics | Apr | 4 |
| 1 | electronics | May | 5 |
| 1 | electronics | Jun | 6 |
| 2 | clothes | Jan | 10 |
| 2 | clothes | Feb | 20 |
| 2 | clothes | Mar | 30 |
| 2 | clothes | Apr | 40 |
| 2 | clothes | May | 50 |
| 2 | clothes | Jun | 60 |
| 3 | cars | Jan | 100 |
| 3 | cars | Feb | 200 |
| 3 | cars | Mar | 300 |
| 3 | cars | Apr | 400 |
| 3 | cars | May | 500 |
| 3 | cars | Jun | 600 |
COLUMNS 표현식으로 SQL 표준 UNPIVOT 동적 실행
COLUMNS 표현식을 사용해 IN 열 목록을 동적으로 결정할 수 있어요. 이는 데이터셋에 month 열이 추가되어도 계속 동작해요. 위 쿼리와 같은 결과를 만들어요.
FROM monthly_sales UNPIVOT (
sales
FOR month IN (columns(* EXCLUDE (empid, dept)))
);
SQL 표준 UNPIVOT 여러 값 열로
UNPIVOT 문은 더 많은 유연성이 있어요: 대상 열이 2개보다 많을 수 있어요. 이는 데이터셋이 피벗된 정도를 줄이는 게 목표지만 피벗된 열을 완전히 쌓지는 않으려 할 때 유용해요. 이를 보여 주기 위해 아래 쿼리는 분기 내 각 월의 번호(월 1, 2, 3)마다 별도 열이 있고, 각 분기에 대해 별도 행이 있는 데이터셋을 만들어요. 분기가 월보다 적기 때문에 이러면 데이터셋이 길어지지만, 위 예시만큼 길지는 않아요.
이를 위해 UNPIVOT 문의 value-column-name 부분에 여러 열을 포함해요. IN 절에도 여러 열 세트를 포함해요. q1과 q2 별칭은 선택적이에요. IN 절의 각 열 세트의 열 수는 value-column-name 부분의 열 수와 일치해야 해요.
FROM monthly_sales
UNPIVOT (
(month_1_sales, month_2_sales, month_3_sales)
FOR quarter IN (
(jan, feb, mar) AS q1,
(apr, may, jun) AS q2
)
);
| empid | dept | quarter | month_1_sales | month_2_sales | month_3_sales |
|---|---|---|---|---|---|
| 1 | electronics | q1 | 1 | 2 | 3 |
| 1 | electronics | q2 | 4 | 5 | 6 |
| 2 | clothes | q1 | 10 | 20 | 30 |
| 2 | clothes | q2 | 40 | 50 | 60 |
| 3 | cars | q1 | 100 | 200 | 300 |
| 3 | cars | q2 | 400 | 500 | 600 |
SQL 표준 UNPIVOT 전체 문법 다이어그램
아래는 SQL 표준 버전 UNPIVOT 문의 전체 문법 다이어그램이에요.