[MySQL] SQL CASE~WHEN을 활용한 피벗테이블 만들기

멋쟁이 사자처럼 AI스쿨

데이터리안 Special Lecture : SQL 중급반

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

 

Pivot Table 

SQL 로 피벗테이블을 구성하기 위해서는

먼저 GROUP BY , CASE WHEN 함수의 속성 , SQL의 NULL값 처리 연산 과정에 대해 이해하고 있어햐한다.

 

 

LeetCode의 1179번 문제를 예제로 CASE 구문으로 피벗테이블이 어떻게 구성되는지를 살펴보자. 

Reformat Department Table - LeetCode

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

위의 Input Table을 아래의 Output Table로 변환해주는 문제이다.

| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

입력테이블을 살펴보면 수익, 부서 , 월에 대한 정보를 세로로 나열하고 있지만 출력 테이블을 살펴보면 각 부서들의 월 별 수익을 가로로 펼쳐놓은 것을 확인할 수 있다 !!
그래서 이 문제의 핵심은 각 부서벌 로 월수익을 가로로 쭉 펼쳐주는 코드를 구현하면 된다. 

 

---

CASE() 속성

: CASE WHEN - THEN - ELSE - END 구문은 데이터를 순서대로 조회하면서 WHEN 조건을 만족할 경우 THEN 에 입력된 값을 넣고, 만족하지 못하면 ELSE 에 입력된 값을 할당한다.

열을 기준으로 먼저 변수를 채워넣자.

• 그래서 month가 1월인 데이터부터 12월인 데이터까지 조회하면서 각 달에 대한 수익을 저장하고, 행부터 오른쪽으로 뻗어나간다.
  • CASE WHEN 구문을 한줄씩 추가해나가면서 각 달의 수익을 입력해주고 해당 month가 아니면 NULL값을 입력한뒤 새로운 변수를 생성해나간다.

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
       ... 
      CASE WHEN month = 'Dec' THEN revenue ELSE NULL END AS Dec_Revenue

FROM DEPARTMENT

+------+---------+-------+---------+---------+---------+---------+
| id   | revenue | month | Jan_rev | Feb rev |   ...     Dec_rev
+------+---------+-------+---------+---------+---------+---------+
| 1    | 8000    | Jan   | 8000    | NULL    |             NULL
| 2    | 9000    | Jan   | 9000    | NULL    |             NULL
| 3    | 10000   | Feb   | NULL    | 10000   |   ...   |   NULL
| 1    | 7000    | Feb   | NULL    | 70000   |             NULL
| 1    | 6000    | Mar   | NULL    | NULL    |             NULL
+------+---------+-------+---------+---------+---------+---------+

 

GROUP BY()

• 중복되는 아이디를 하나로 묶어주기 위해 ID를 기준으로 그룹화를 시켜준다.
  • ID값을 기준으로 GROUP BY 를 적용시키는데 특정한 옵션이 없다면 ? 제일 상단에 먼저나오는 값을 포함한다.
    • • GROUP BY만 적용시킨 경우 id가 1이면서 2월 달 수익을 나타내는 값이 NULL 로 표시가 된다.

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
       ... 
      CASE WHEN month = 'Dec' THEN revenue ELSE NULL END AS Dec_Revenue

FROM DEPARTMENT
GROUP BY id 

+----+--------+-----------+----------+----------
| id | month  |  revenue  | Jan_Rev  | Feb_Rev
+----+--------+-----------+----------+----------
| 1  | "Jan"  |   8000    |  8000    |   NULL <---- 7000이 들어와야함 
| 2  | "Jan"  |   9000    |  9000    |   NULL <----
| 3  | "Feb"  |   10000   |  NULL    |   10000
+----+--------+-----------+---------------+-----

 

이 값을 살리기 위해 SUM() 연산을 적용해야한다 .

SUM()

• id 별로 월별 수익을 합쳐준다.
  • SUM()의 경우 NULL값은 무시한다.
    • null값은 계산에 포함되지 않기 때문에 id 1번의 2월달 수익에 대한 값 7000이 표시되는 것을 확인할 수 있다 !
• group by 대상 값이 중복되지 않는다면 집계함수를 사용하지 않아도 되지만, 중복되는 값이 2개 이상일 경우 null을 이용하여 걸러내야한다 !

따라서 최종 코드는 다음과 같다

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

 

 

 

 

 

 

 

 

Reference

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

[리트코드 sql] 1179. Reformat Department Table