[SQL] GROUP BY와 pivot

1179. Reformat Department Table

이 문제는 오답을 내는데까지도 시간이 꽤 걸렸고, 정답을 이해하는 데에도 시간이 꽤 걸린만큼 내게 깊은 인상을 남긴 문제다. 나의 오답과 수정과정까지 상세히 기록한다.

---

문제
첫 번째 테이블로부터 각 부서별로(id) 월별 매출을 구하되, 아래 두 번째 테이블과 같은 형태(피봇)로 구하라.

id	revenue	month
1	8000	Jan
2	9000	Jan
3	10000	Feb
1	7000	Feb
1	6000	Mar

id	Jan_Revenue	Feb_Revenue	Mar_Revenue
1	8000	7000	6000
2	9000	null	null
3	null	10000	null

문제해석

• id컬럼이 primary key가 아니다. 각 부서의 이름 대신 나타내는 id이고, 1번 id는 Jan, Feb, Mar 세 개의 month에 대해 데이터를 갖고 있다.
• 형식에 대한 제약이 없었다면 SELECT * FROM department GROUP BY id, month했겠으나 피봇된 형태로 결과를 출력해야 한다.
• SQL에서는 CASE 함수를 사용해서 테이블을 피봇할 수 있다.

1차 시도

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와 함께, (month)_Revenue를 각 컬럼의 이름으로 하고 해당하는 월 매출액’만’ 표시하고 없는 경우 null 값을 표시하는 컬럼을 12개 만들었다.

• 마지막에 GROUP BY를 넣은 것은 부서의 id는 3개인데 한 id에 여러 월 매출액이 있는 경우가 있고, 이 경우 해당하는 월의 컬럼에만 데이터가 들어가면 되기 때문에 부서 id 기준으로 그룹화한 것이다.
  
  

결과는...

전체적인 형태는 갖췄는데, 보니까 Feb과 Mar에서 틀렸다. 1번 부서는 Feb 매출액과 Mar 매출액을 갖고 있는데 표시되지 않은 것이다.

왜?

집계함수 GROUP BY 에 대한 이해가 부족했다.

id가 1인 row는 다음과 같다

구분	id	revenue	month
첫번째 row	1	8000	Jan
두번째 row	1	7000	Feb
세번째 row	1	6000	Mar

여기서 CASE 구문에 따라

Jan_Revenue는 {8000 ; null ; null}

Feb_Revenue는 {null ; 7000 ; null}

Mar_Revenue는 {null ; null ; 6000}

을 값으로 갖게 되는데

내가 여기에 GROUP BY id 를 걸었고 이 GROUP BY 라는 집계함수는 정말 ‘그룹화’의 역할을 하는 것이지, 그 중 어느 값을 그룹의 대표로 출력할 것인가에 대해서는 관여하지 않는다.
쉽게 말해서, 그룹화된 값들 중 가장 앞에 있는 값이 출력되었다는 말이다. 8000, null, null…

최종 쿼리문

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

• 그룹화된 값 중 어떤 값을 대표로 출력할 것인가를 명확히 하기 위해서
  나는 CASE로 뽑아낸 값 중 ‘최댓값(MAX)’을 출력하라고 쿼리문을 수정했다.
  (해당 테이블의 데이터는 월별로 하나의 매출액 값만 갖고 있기 때문에 SUM이나 AVG 같은 다른 함수를 써도 무방하다)
  
  
  짜란~