[mySQL] 리트코드 Reformat Department Table

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

이전에 내가 작성한 글을 보면 알겠지만 이 문제에서 도대체 왜 SUM함수를 쓰는지 알 수가 없었고
선미님께서 작성해주신 답변 댓글과 모든 것을 종합해봐도 헷갈려서 다시금 확실히 정리하고자 쓰는 글...

참고
https://www.inflearn.com/questions/24200/sum-%ED%95%A8%EC%88%98-%EB%AC%B8%EC%9D%98

선미님께서
CASE 의 속성과 GROUP BY 의 속성을 따로 이해해야 한다고 말씀 주셨다.

CASE의 속성

• CASE는 데이터를 순서대로 조회하면서, IF 조건에 맞는다면 THEN 뒤의 값을 넣고, 그렇지 않으면 ELSE 값을 넣는 역할을 수행

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

부서 id가 어떤 값을 가지든지 상관 없이 month = 'Jan' 이라면 revenue를 넣어주고, 그렇지 않으면 NULL 값을 넣는다.

Id	Jan_Revenue	month	revenue
1	8000	Jan	8000
2	9000	Jan	9000
3	null	Feb	10000
1	null	Feb	7000
1	null	Mar	6000

3번째 행부터는 month = 'Jan'이 아니기 때문에
Jan_Revenue 컬럼에 revenue가 아닌 NULL 값이 입력되어 있다.

특히, id가 1이지만 month가 Jan이 아닌 4, 5번째 행은 revenue 대신 NULL 값이 입력되어 있다.

CASE WHEN ~ 절에 month = 'Feb' 조건을 추가해보면 아래와 같이 나온다

Id	Jan_Revenue	Feb_Revenue	month	revenue
1	8000	null	Jan	8000
2	9000	null	Jan	9000
3	null	10000	Feb	10000
1	null	7000	Feb	7000
1	null	null	Mar	6000

첫 번째 CASE문에 따라,
month = 'Jan'인 경우만 Jan_Revenue에 revenue 입력, 그 외에는 NULL 출력

두 번째 CASE문에 따라,
month = 'Feb'인 경우만 Feb_Revenue에 revenue 입력, 그 외에는 NULL 출력

더 나아가서
id가 1인 경우를 보면
month = 'Jan' 일 때 Jan_Revenue에 revenue 출력, 그 외에는 NULL
-> month = 'Jan' 인 CASE에서 id가 1인 행은 (8000, NULL)를 갖는다.

month = 'Feb' 일 때 Feb_Revenue에 revenue 출력, 그 외에는 NULL
-> month = 'Feb' 인 CASE에서 id가 1인 행은 (NULL, 7000)을 갖는다.

GROUP BY의 속성

다음으로, 위 코드에서 SUM 함수 없이, Id 별로 GROUP BY를 하게 된다면?

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,
       month,
       revenue
FROM Department
GROUP BY Id

Id	Jan_Revenue	Feb_Revenue	month	revenue
1	8000	null	Jan	8000
2	9000	null	Jan	9000
3	null	10000	Feb	10000

-> Id가 1이고 month='Jan'일 때, revenue가(8000) Jan_Revenue 컬럼에 들어가있지만, Feb_Revenue는 NULL로 출력된다.
-> 왜?????????
더하라고 한 적도 없고, GROUP BY만 해주었으니 그대로 출력된다.
(2월에 해당하는 NULL, 7000 값은 어디론가 증발해버리고 없다)

GROUP BY Id를 했기 때문에, Id 별로 하나의 row씩만 나오도록 해야 하는데
현재 Id = 1인 데이터가 총 3개인 상황이다.

3개를 1개로 줄이기 위해서는,
1) Id가 같은 것들끼리 하나의 그룹으로 묶은 후
2) 3개의 Revenue를 연산해 그 그룹을 대표하는 값을 하나 만들어주어야 한다.

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

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,
       month,
       revenue
FROM Department
GROUP BY Id

Id	Jan_Revenue	Feb_Revenue	month	revenue
1	8000	7000	Jan	8000
2	9000	null	Jan	9000
3	null	10000	Feb	10000

GROUP BY에 따라 Id별로 분리 -> month = 'Jan'인 행의 값을 전부 더한다
GROUP BY에 따라 Id별로 분리 -> month = 'Feb'인 행의 값을 전부 더한다

정리하자면, 위 코드의 작동 순서는 아래와 같을 것이다.

1. GROUP BY에 따라 각 Id별로 그루핑 (Id에 따라 1, 2, 3으로 그루핑 됨)
2. Id가 1이고 month = Jan인 행을 전부 더함 -> (8000 + NULL + NULL) = 8000
3. Id가 1이고 month = Feb인 행을 전부 더함 -> (NULL + 7000 + NULL) = 7000

-> SUM() 함수는 NULL을 무시하므로 Jan_Revenue의 최종값은 8000, Feb_Revenue의 최종값은 7000이 된다.
.
.
.
GROUP BY 없이 단순히 SUM ( ) 만 했었다면,
Jan_Revenue는 17000 (8000+9000)
Feb_Revenue는 17000 (10000+7000)이 나왔을 것이다.

+AVG( ) 함수도 NULL 값을 무시하고 연산해줌

다시 한번 되짚어보면서 정리를 하긴 했는데 내가 잘 이해했는지 확신이 안 선다.. 이건 두고두고 계속 보면서 봐야할 것 같다 ㅠㅠ