[SQL] CASE_테이블 피봇

 

GROUP BY를 이용해 SupplierID별 price의 평균을 구해보면, 다음과 같이 출력된다.

SELECT supplierid, AVG(price)
FROM products
GROUP BY supplierid

 

그런데 위의 형식이 아닌 다음과 같은 형식으로 출력하고 싶다면, 어떻게 해야 할까?

 

 

테이블 피봇

 

위와 같이 열의 값을 행으로 바꾸는 것을 테이블 피봇이라고 한다.

테이블 피봇을 하기 위해 아래처럼 CASE를 이용해 작성할 수 있다.

SELECT AVG(CASE WHEN supplierid = 1 THEN price ELSE NULL END) AS SupplierID1_Avg
     , AVG(CASE WHEN supplierid = 2 THEN price ELSE NULL END) AS SupplierID2_Avg
     , AVG(CASE WHEN supplierid = 3 THEN price ELSE NULL END) AS SupplierID3_Avg
     , AVG(CASE WHEN supplierid = 4 THEN price ELSE NULL END) AS SupplierID4_Avg
FROM products

 

• supplierID가 1인 경우에는 price, 1이 아닌 경우에는 NULL을 반환한다.  →  AVG에 의해 NULL이 아닌 값을 모두 합한 후, NULL이 아닌 값의 개수를 나누어준다.  →  즉, supplierID가 1인 값만의 평균을 구할 수 있다.
• AS를 이용해 컬럼명이 SupplierID1_Avg로 나오도록 지정한다.
• 이 과정을 supplierID가 2, 3, 4인 경우에도 동일하게 해준다.

 

 

 

 

<LeatCode 연습문제>

 

Reformat Department Table

▶ Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

example)

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

 

Idea)

1. CASE를 이용한 테이블 피벗 활용하기
2. month가 Jan인 경우에만 revenue, 나머지는 null을 반환하도록 하기
3. Jan에서의 총 revenue를 구해야하므로 SUM 이용하기
4. id를 기준으로 그룹화하기

 

Solution)

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

* SUM을 사용하지 않으면, 오류가 발생함. SUM을 이용해야 하는 이유 다시 생각해보기!

 

 

 

 

---

 

 

↘↘↘

[백문이불여일타] 데이터 분석을 위한 중급 SQL

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EC%A4%91%EA%B8%89-sql