CASE를 활용한 테이블 피봇 + 리트코드 예제

일반적으로 GROUP BY와 집계 함수를 써서 데이터를 출력할 때, 결과가 세로로 나온다.

 

ex) products 테이블에서 다음과 같은 쿼리를 작성하

SELECT categoryid, AVG(price)
FROM Products
GROUP BY categoryid

위와 같은 결과가 나옴.

 

그러나, categoryid_1     categoryid_2   categoryid_3   ...

             37.979...           23.062.....        25.16.....

이렇게 가로로 표현되는 결과를 얻고 싶다면 CASE문을 횔용한 테이블 피벗으로 가능하다.

 

 

 

• 우선 엑셀에서 흔히 말하는 피벗테이블이 뭘까? 

피벗은 회전 축의 의미로, 기존 테이블에서 기준이 되는 한 축을 잡고 그 관점으로 표를 만들어주는 기능을 한다.

관점이 달라질 때마다 함수를 새로 쓸 필요 없이, 바로 변경해서 볼 수 있으므로 편리하다.

 

 

• 위와 같은 Products 테이블을 예시로 CASE문 작성하기

접근 방식

1. case ~ then에서 '출력할 값'을 지정해줄 때, 따로 이름을 부여하지 않고 기존 테이블의 price값을 넣어주기

2. 평균값이 필요하므로 AVG 함수 안에 CASE문을 포함시켜줄 것, 대신 AS로 새로 지정해주는 칼럼명은 제외, END까지만

3. caregoryid = 1, = 2, ... 차례대로 SELECT문에 넣어주기

 

예시 코드

SELECT AVG(CASE WHEN categoryid = 1 THEN price ELSE NULL END) AS category1_avg_price,
	   AVG(CASE WHEN categoryid = 2 THEN price ELSE NULL END) AS category2_avg_price,
       AVG(CASE WHEN categoryid = 3 THEN price ELSE NULL END) AS category3_avg_price
FROM Products

 

 

그럼 결과가 위와 같이 나온다.

 

• 리트코드 예제 풀이(난이도 상)

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

 

1179. Reformat Department Table

 

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+
In SQL,(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"].

Reformat the table such that there is a department id column and a revenue column for each month.

Return the result table in any order.

The result format is in the following example.

 

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).

 

문제 해설

- 각 월마다 department id랑 revenue 칼럼이 짝지어지게 해라(표는 가로로)

- 정렬 순서는 상관없음

- 결과 테이블에 칼럼 수는 13개 id + 12개의 달

 

 

접근 방식

1. 위에서 했던 방식으로 case ~ then의 출력할 값에 revenue 넣어주기, 칼럼명 output에 나온대로 새로 지정

2. id값이 1,2,3... 하나씩 배치가 되어야 함 >> 그냥 select에 id만 넣으니까 1,2,3 다음 다시 1이 나타나기 때문에 groupby를 해줘야 함

3. 1월의 1번 부서의 모든 예산을 나타내는 것이므로 sum이 들어가야 함 >> 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