[leetcode / MySQL] 1179. Reformat Department Table

문제

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

 

테이블 형태

 

풀이

SELECT id
    , SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue
    , SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue
    , SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue
    , SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue
    , SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue
    , SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue
    , SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue
    , SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue
    , SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue
    , SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue
    , SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue
    , SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id

 

해설

SELECT id
    , SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue
    , SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue
    , SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue
    , SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue
    , SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue
    , SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue
    , SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue
    , SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue
    , SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue
    , SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue
    , SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue
    , SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id

• 피봇테이블 문제였다. id, revenue, month가 한 행인 데이터가 삽입되어 있어 세로로 길게 늘어진(=롱폼 데이터) month를 가로로 펼쳐야 했고, 각 month별 revenue를 합산해 담아야 했다.
• id를 기준으로 그룹핑하였고 CASE문을 사용해 각 month별 revenue를 도출, 그 값을 SUM 으로 감싸 month별 revenue가 합산된 결과가 와이드 포맷으로 변환되도록 하였다
• 영어로 서술된 문제가 익숙지 않아서 처음에 좀 헤맸다. 몇 번을 fail하다가 그룹핑 조건을 추가하고, SUM 을 추가하면서 통과될 수 있었다