SQL CASE문 : Data Pivoting 예제

SQL: CASE , WHEN

SQL의 IF문

코드에 익숙한 사람들이라면 주로 쓰는 IF / ELSE 구문

SQL에서도 마찬가지로 CASE 문을 사용하여 표현할 수 있다.

SELECT CASE 
				WHEN 열1 = '조건값1' THEN '결과값1'
				WHEN 열2 = '조건값2' THEN '결과값2'
				...
				ELSE '결과값N' 
		END

문법은 간단하다 CASE 와 END 절 사이에 원하는 CASE별 값들을 지정해주면 된다

오늘은 이러한 CASE 문이 쓰이는 예제 중 Pivot Table을 만드는 예제를 살펴보자

예제

문제 출처: https://leetcode.com/problems/reformat-department-table/

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+
INPUT
+------+---------+-------+
| 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        |
+-------+------+---------+-------+------+---------+-------+----------+

다음과 같은 Department 테이블이 있다고 할 때 결과 값으로

각 ID별 월에 따른 revenue 값을 (1월부터 12월) 열로 새롭게 표현하기 위해서는 어떻게 해야 할까?

---

다음과 같이 기존 테이블에서 조건에 따라 새로운 열을 생성하여 새로운 테이블을 생성 해야하는 경우, CASE, WHEN 절을 사용한 데이터 피봇팅이 필요하다.

1) 각 ID별로 분류하기 때문에 ID 컬럼에 GROUP BY절을 적용한다

2) 하나의 CASE절은 하나의 컬럼을 생성하기 때문에 월 별 Revenue ( 예) Jan_Revenue )을 생성하기 위해서는 각 월에 대한 개별적인 CASE절이 필요하다.

• 먼저 Jan_Revenue 열을 위한 CASE문을 작성해보면

# 만약 month값이 Jan이라면, revenue값을 아닌라면 NULL값을 표기한다
# 해당 열을 'Jan_Revenue'로 표기한다.
SELECT  id,
				CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_Revenue
FROM Department

OUTPUT
| id | Jan_Revenue |
| -- | ----------- |
| 1  | 8000        |
| 2  | 9000        |
| 3  |             |
| 1  |             |
| 1  |             |

• 작성한 CASE문으로 생성된 열의 ID별 총 합을 GROUP BY와 SUM 집계함수를 사용하여 표현하고
• 나머지 Feb ~ Dec 에 해당하는 CASE 문도 작성한다.

작성한 쿼리 문의 결과는 다음과 같다.

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