[LeetCode] 1179.Reformat Department Table, MySQL 피봇테이블

문제 해석:

id, revenue, month 각각의 데이터가 입력된 테이블을 id별, 월별 revenue의 합으로 표현한 피봇테이블을 생성하라는 것

 

문제를 풀기 위해 가장 먼저 output 테이블을 보고 id별로 구분되어 있는 것으로 GROUP BY를 사용해야 한다고 떠올릴 수 있어야 하고,

month에 따라서 컬럼명이 month_Revenue로 되어 있는 것을 보고 CASE WHEN을 떠올려야 함

 

* 주의

CASE WHEN을 사용하여 ELSE 부분에 NULL을 주면 month가 Jan가 아니면 모두 NULL로 표시하게 되는데 SUM 집계 함수를 통해 NULL을 무시하고 모두 더해주어야 원하는 값이 나오게 됨

 

 

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


/*
["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue", "Apr_Revenue", "May_Revenue", "Jun_Revenue", "Jul_Revenue", "Aug_Revenue", "Sep_Revenue", "Oct_Revenue", "Nov_Revenue", "Dec_Revenue"]
[1, 8000, 7000, 6000, null, null, null, null, null, null, null, null, null]
[2, 9000, null, null, null, null, null, null, null, null, null, null, null]
[3, null, 10000, null, null, null, null, null, null, null, null, null, null]
*/

오답

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
     , CASE WHEN month = 'Mar' THEN revenue ELSE NULL END AS Mar_Revenue
     , CASE WHEN month = 'Apr' THEN revenue ELSE NULL END AS Apr_Revenue
     , CASE WHEN month = 'May' THEN revenue ELSE NULL END AS May_Revenue
     , CASE WHEN month = 'Jun' THEN revenue ELSE NULL END AS Jun_Revenue
     , CASE WHEN month = 'Jul' THEN revenue ELSE NULL END AS Jul_Revenue
     , CASE WHEN month = 'Aug' THEN revenue ELSE NULL END AS Aug_Revenue
     , CASE WHEN month = 'Sep' THEN revenue ELSE NULL END AS Sep_Revenue
     , CASE WHEN month = 'Oct' THEN revenue ELSE NULL END AS Oct_Revenue
     , CASE WHEN month = 'Nov' THEN revenue ELSE NULL END AS Nov_Revenue
     , CASE WHEN month = 'Dec' THEN revenue ELSE NULL END AS Dec_Revenue
FROM department
GROUP BY id

/*
["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue", "Apr_Revenue", "May_Revenue", "Jun_Revenue", "Jul_Revenue", "Aug_Revenue", "Sep_Revenue", "Oct_Revenue", "Nov_Revenue", "Dec_Revenue"]
[1, 8000, null, null, null, null, null, null, null, null, null, null, null]
[2, 9000, null, null, null, null, null, null, null, null, null, null, null]
[3, null, 10000, null, null, null, null, null, null, null, null, null, null]
*/