[MYSQL] table 피봇팅

CASE문을 활용한 table 피봇팅

SELECT AVG(CASE WHEN categoryid = 1 THEN price ELSE NULL END) AS category1_price,
       AVG(CASE WHEN categoryid = 2 THEN price ELSE NULL END) AS category2_price,
       AVG(CASE WHEN categoryid = 3 THEN price ELSE NULL END) AS category3_price
FROM Products

category1_price	category2_price	category3_price
37.979166666666664	23.0625	25.16

이런식으로 예제문을 응용하여 코드를 진행 시키면 세로축으로 나오는 DB출력문들이 옆으로 데이터를 펼쳐서 볼수 있게된다.
이것이 바로 table pivot이다.

예제 문제

leetcode

예제 문제 풀이

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

예제 문제 풀이

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).
이런식으로 되어 있는 input형태를 가로축으로 쭉 늘려서 값을 구해야 했다. 이때 Month의 Jan을 기준으로
SUM(CASE WHEN month = "Jan" THEN revenue ELSE NULL END) as Jan_Revenu을 하게 되면
+---------+
| revenue |
+---------+
| 8000 |
| 9000 |
| NULL |
| NULL |
| NULL |
+---------+
이런식으로 출력이 된다. 이때 해당 Output은 id값을 기준으로 출력을 했으므로 groupby를 이용하여 id를 기준으로 값을 출력한다. 이때 groupby는 집계함수를 이용해야 하므로 SUM을 이용한다.