[SQL] CASE WHEN으로 Pivot 하는 방법

Hive 뿐만 아니라, 다양한 SQL 언어에서 CASE WHEN을 활용하여 Pivot이 가능하다.

 

개인적으로 컬럼의 종류가 많으면 Python Pandas를 활용한 Pivot을 선호하며, 대용량 데이터이거나 또는 피벗 해야 하는 데이터 종류가 많지 않다면 SQL에서 처리하는 것을 더 선호한다.  따라서 본인은 상황에 따라 바꿔 가면서 데이터 Pivoting을 진행하는 편이다.

 

예시로 참고한 데이터는 Leetcode의 "reformat-department-table"이다.

 

https://leetcode.com/problems/reformat-department-table/

Reformat Department Table - LeetCode

 

Pivot Table을 만들어야 테이블은 아래와 같다. id는 PK이며, 각 부서의 월별 매출액이다. 월 정보는 varchar로 Jan, Feb로 들어가 있는 게 특징이다.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+
(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

 

아래와 같이 월별 Pivot 테이블로 표기가 되어야 하며, null 값이 존재함.

 

Example 1:

Input: 
Department table:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
Output: 
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+
Explanation: The revenue from Apr to Dec is null.
Note that the result table has 13 columns (1 for the department id + 12 for the months

Answer:

CASE WHEN 구분을 활용

SELECT id
    , SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue
    , SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue
    , SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue
    , SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue
    , SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS May_Revenue
    , SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue
    , SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue
    , SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue
    , SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue
    , SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue
    , SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue
    , SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue    
FROM Department
GROUP BY id

설명:

 

CASE WHEN month = 'Jan' THEN revenue ELSE NULL END 이라는 것은 데이터를 위에서 아래로 조회하면서 Month라는 컬럼이 'Jan'이라는 조건에 맞다면 Then 이후의 값을 할당하고 그렇지 않으면 ELSE라는 값을 할당하는 역할이다. 

 

output 이 id 별로 되어 있기 때문에, 이를 위해 id를 group by하고 SUM을 제외한 CASE WHEN 구문을 이용하여 결과를 확인해보자.

SELECT id
    , CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_Revenue
    , CASE WHEN month = 'Feb' THEN revenue ELSE NULL END AS Feb_Revenue 
FROM Department
GROUP BY id

output:

Id	Jan_Revenue	Feb_Revenue
1	8000	Null
2	9000	Null
3	Null	10000

 

1번 id에 Feb가 있음에도 불구하고, 1번 id로 그룹된 결과는 Feb_Revenue = Null이다. 

Group by 구문을 빼보고, Raw별로 확인해보자.

SELECT id 
           , month
           , revenue
           , CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_Revenue
           , CASE WHEN month = 'Feb' THEN revenue ELSE NULL END AS Feb_Revenue
FROM DEPARTMENT

output:

id	month	revenue	Jan_Revenue	Feb_Revenue
1	"Jan"	8000	8000	Null
1	"Feb"	7000	Null	7000
1	"Mar"	6000	Null	Null
2	"Jan"	9000	9000	Null
3	"Feb"	10000	Null	10000

이전 SQL 구문에서 group by를 했을 때는 1번 raw가 그대로 들어가 Feb_Revenue의 값에 Null 이 들어간 것이다.

 

따라서, id 기준으로 group by 할때, SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END AS Feb_Revenue)을 통해 id 1에 해당하는 Feb_Revenue을 모두 합쳐줘야 한다.

 

id 1에 해당하는 Feb_Revenue는 Null + 7000 + Null

 

SELECT id
           , month
           , revenue
           , SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_revenue
           , SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue
FROM DEPARTMENT
GROUP BY id

output:

id	month	revenue	Jan_Revenue	Feb_Revenue
1	"Jan"	8000	8000	7000
2	"Jan"	9000	9000	Null
3	"Feb"	10000	Null	10000

SUM과 CASE WHEN을 함께 활용했을 때, 원하는 output을 얻을 수 있는것을 확인할 수 있다.