인프런에서 데이터리안의 [백문이 불여일타] SQL 강좌를 듣고 있다.
8월의 데이터리안 SQL 캠프 실전반을 신청해두었기 때문에 그 전에 기본 개념을 배우기 위함이다.
Python은 사용해 본 적이 있으나 SQL은 처음이다.
하지만 입문반 캠프를 수강하기보다 인프런 강좌로 기초 문법을 떼기로 했다.
어제 초급반을 완강하고 중급반을 시작해 현재 CASE 강좌까지 끝냈다.
여태껏 무난했는데, 리트코드(LeetCode)의 CASE 문제가 까다로워 간단히 정리하고자 한다.
LeetCode 문제
- Reformat Department Table (https://leetcode.com/problems/reformat-department-table/)
id(int), revenue(int), month(varchar)로 구성된 Department 테이블이 주어지고
월 별 매출을 부서 id별로 한 행으로 나타내는 문제이다.
CASE를 사용하는 피봇 테이블 문제이고, id별로 나타내야 하니 GROUP BY를 쓰면 될 것 같다.
우선 1월~3월 정보에 대해서만 CASE문을 작성해보았다.
SELECT id
, CASE WHEN month='Jan' THEN revenue END AS 'Jan_Revenue'
, CASE WHEN month='Feb' THEN revenue END AS 'Feb_Revenue'
, CASE WHEN month='Mar' THEN revenue END AS 'Mar_Revenue'
FROM Department다음과 같은 Output이 나왔다.
{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue"],
"values": [[1, 8000, null, null],
[2, 9000, null, null],
[3, null, 10000, null],
[1, null, 7000, null],
[1, null, null, 6000]]} 이제 id별로 GROUP BY를 하면 월 별로 합쳐지겠지?
{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue"],
"values": [[1, 8000, null, null],
[2, 9000, null, null],
[3, null, 10000, null]]}NOPE! 우리의 SQL은 그렇게 스마트하지 호락호락하지 않았다.
월 별로 묶이긴 했는데 아까까지 멀쩡히 뜨던 Feb_Revenue, Mar_Revenue 값이 null값으로 뜨는 걸까?
아예 다른 테이블도 만들어보고 삽질을 해봤지만 원하는 결과가 나오지 않았다.
혹시 아직 안 배운 개념이 쓰이는걸까 싶어 검색하다 보니 원하는대로 null 값 없는 집계를 하려면
CASE문을 SUM으로 감싸야 한다고 한다.
SUM?
한 줄 입력해보니 정답은 나왔지만, SUM 없어도 알아서 GROUP BY가 null값은 무시해줘야하지 않나?
강의를 보아도 CASE문 입력 부터 바로 SUM을 쓰시고 상세한 설명이 없었는데
첨부된 Q&A 링크에 같은 내용에 대한 질문이 있었다.
결론은 집계함수(aggregate function)를 사용해줘야 한다는 것이었다.
피봇의 결과를 부서 별로 한 줄 요약하려는 상황에서 부서 별 정보를 한 줄에 나타내기 위한 집계함수 말이다.
예시를 사용한 SUM(CASE) 해설
예제 input중 중복되는 부서 id 1을 예시로 들어보자.
위 첫번째 output에서 id=1에 해당하는 결과만 가져오면 아래와 같다.
{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue"],
"values": [[1, 8000, null, null],
[1, null, 7000, null],
[1, null, null, 6000]]} 우리는 null값 없이 [1, 8000, 7000, 6000]의 형태로 나타나길 바란다.
하지만 GROUP BY id를 하면 [1, 8000, null, null] 만 나온다.
사라진 7000과 6000을 가져오기 위해 SUM을 해주어야 한다는 것이다.
Feb_Revenue만 보자.
두 번째 세로줄이 CASE로 정리된 부서 1의 2월 매출(Feb_Revenue)인 null, 7000, null이다.
이걸 SUM을 사용해 null + 7000 + null = 7000으로 나타낼 수 있다.
(SQL의 SUM은 null값을 무시한다)
두 줄에 대해서만 SUM을 추가해보면 코드는 아래와 같고
SELECT id
, SUM(CASE WHEN month='Jan' THEN revenue END) AS 'Jan_Revenue'
, SUM(CASE WHEN month='Feb' THEN revenue END) AS 'Feb_Revenue'
, CASE WHEN month='Mar' THEN revenue END AS 'Mar_Revenue'
FROM Department
GROUP BY id출력값은 다음과 같이 바뀐다.
{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue"],
"values": [[1, 8000, 7000, null],
[2, 9000, null, null],
[3, null, 10000, null]]}부서 1의 결과에 Feb_Revenue 값이 정상적으로 합산된 모습을 볼 수 있다.
SUM해주지 않은 Mar_Revenue 값은 여전히 null로 남아있다.
요는, CASE가 부서 1에 대해 정리한 정보는 3개의 행이므로
이를 GROUP BY를 사용해 하나의 행으로 표시하기 위해서는 각 세로줄(원하는 컬럼 값들)에
적절한 집계함수를 써서 요약해줘야 한다는 것이다.
이런 집계 함수 없이 GROUP BY를 냅다 썼을 때 나타난 [8000, null, null]로 나타난 이유는 모르겠으나
그냥 부서 1에 대한 가장 첫 번째 줄을 가져온 것이 아닐까 추측해본다.
확실히 중급반으로 넘어오니 좀 더 정확한 이해가 필요한 문제가 등장한다.
이번 문제가 어려웠던 이유는 CASE문 작성보다 GROUP BY에 대한 명확한 이해 부족이 원인이라 생각한다.
인프런 Q&A글에 강사 선미님이 남겨주신 피봇 관련 링크와 같은 사이트의 GROUP BY 링크를 남긴다.
피봇 테이블 - https://mode.com/sql-tutorial/sql-pivot-table/
GROUP BY - https://mode.com/sql-tutorial/sql-group-by/
(썸네일 : https://wonkooklee.github.io/thumbnail_maker/ by @oneook )
