[MySQL] table pivot을 하며 깨달은 CASE()과 GROUP BY()의 속성

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

Reformat Department Table - LeetCode

이 문제를 풀 때마다 SUM()을 쓰는 이유에 대해 헷갈렸다!

선미님께서 CASE()와 GROUP BY()의 속성을 생각한다면 조금 더 쉽게 이해할 수 있을 거라고 하셔서, 다시 정리하며 풀어 보았다!

 

 

선미님 강의는 아래 링크 참고!

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/lecture/30027?tab=question&q=24200

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

 

일단, 코드부터 보면 아래와 같다.

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별로 알 수 있는 테이블을 피보팅해야 했는데,

CASE문에 왜 `SUM`을 써야하는지 너무나 헷갈렸다.

 

CASE()의 속성

`CASE WHEN ~ THEN ~ ELSE ~ END` : 데이터를 순서대로 조회하면서, WHEN 조건에 맞는다면 THEN 뒤의 값을 넣고, 그렇지 않으면 ELSE 값을 넣는 역할을 한다.

 

아래 쿼리를 실행해서 CASE문에 따라 Jan_Revenue가 어떻게 나오는지 확인해보자.

SELECT id 
           , month
           , revenue
           , CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_Revenue
FROM DEPARTMENT

id, month, revenue를 차례대로 출력하고,

CASE문에 따라 month = 'Jan' 이라면 Jan_Revenue에 revenue의 값을, 그렇지 않으면 NULL 값을 출력해준다.

 

---+--------+------------+--------------

id month revenue Jan_Revenue

---+--------+------------+--------------
1   "Jan"  8000     8000
1   "Feb"  7000     NULL
1   "Mar"  6000     NULL

2   "Jan"   9000     9000

3   "Feb"  10000    NULL

---+--------+------------+--------------

 

id가 1인 것만 봤을 때

-> id가 1인 첫번째 행은 month가 "Jan"이기 때문에 revenue의 8000을 그대로 Jan_Revenue에 넣어준다.

-> id가 1인 두번째,세 번째 행은 month가 "Jan"이 아니기 때문에 7000, 6000인 revenue 값 대신 CASE 문을 통해 NULL로 나온다.

 

 

그럼 Feb_Revenue도 추가해서 어떻게 나오는지 확인해보자.

SELECT id 
           , month
           , revenue
           , CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_Revenue
           , CASE WHEN month = 'Feb' THEN revenue ELSE NULL END AS Feb_Revenue
FROM DEPARTMENT

첫번째 CASE문에 따라 month = 'Jan' 이라면 Jan_Revenue에 revenue의 값을, 그렇지 않으면 NULL 값을 출력,

두번째 CASE문에 따라 month = 'Feb' 이라면 Feb_Revenue에 revenue의 값을, 그렇지 않으면 NULL 값을 출력해준다.

---+--------+------------+---------------+----------------

id  month  revenue  Jan_Revenue Feb_Revenue

---+--------+------------+---------------+----------------
1    "Jan"      8000    8000           NULL

1    "Feb"      7000    NULL          7000

1    "Mar"     6000     NULL          NULL
2    "Jan"      9000     9000           NULL
3    "Feb"     10000    NULL          10000

---+--------+------------+---------------+----------------

 

id가 1인 것만 봤을 때

-> id가 1인 첫번째 행은 month가 "Feb"가 아니기 때문에 NULL값을 Feb_Revenue에 넣어준다

-> id가 1인 두번째 행은 month가 "Feb"기 때문에 7000인 revenue 값을 그대로 Feb_Revenue에 넣어준다.

 

GROUP BY()의 속성

여기에 CASE문에 SUM()을 걸어주지 않고, GROUP BY를 id 기준으로 수행해주면,

SELECT id
           , month
           , revenue
           , CASE WHEN month = 'Jan' THEN revenue ELSE NULL END AS Jan_revenue
           , CASE WHEN month = 'Feb' THEN revenue ELSE NULL END AS Feb_Revenue
FROM DEPARTMENT
GROUP BY id

 

 

---+--------+------------+---------------+---------------

id  month   revenue  Jan_Revenue Feb_Revenue

---+--------+------------+---------------+---------------
1   "Jan"    8000       8000            NULL
2   "Jan"    9000       9000            NULL
3   "Feb"   10000      NULL           10000

---+--------+------------+---------------+---------------

-> id가 1이고, month="Jan"일 때, revenue인 8000을 Jan_Revenue에 넣고, 그 외에는(Feb_Revenue에는) NULL이 들어가게 된다.

-> 더하는 게 아니므로 그대로 출력한다.

따라서, 월별 수익을 id별로 보려면 SUM을 써서 합쳐줘야 한다.

 

 

SUM()을 해주면,

SELECT id
           , month
           , revenue
           , 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
FROM DEPARTMENT
GROUP BY id

 

---+--------+------------+---------------+---------------

id  month   revenue  Jan_Revenue Feb_Revenue

---+--------+------------+---------------+---------------
1   "Jan"     8000      8000            7000
2   "Jan"     9000      9000            NULL
3   "Feb"    10000     NULL            10000

---+--------+------------+---------------+---------------

-> id가 1이고, month="Jan"일 때, revenue인 8000을 Jan_Revenue에 넣고, 그 외에는(Feb_Revenue에는) NULL이 들어가게 된다.

-> 그리고 id가 1인 것의 Jan_Revenue 값 8000, NULL, NULL을 다 더해주고, id가 1인 것의 Feb_Revenue값 NULL, 7000, NULL을 다 더해준다.

-> SUM() 함수는 NULL을 무시하므로 Jan_Revenue의 최종값은 8000, Feb_Revenue의 최종값은 7000이 된다.

 

 

+ SUM()이 아닌 AVG()같은 집계함수를 써도 NULL은 무시하고 연산해주므로 괜찮다.

 

 

 

 

다시는 휘발되지 말자 나의 작고 소중한 SQL 지식아..