[SQL] Case-When을 이용한 피벗테이블

오늘은 Group by와 Aggregate, case-when을 결합하여 피벗테이블을 생성하는 법에 대해서 리뷰한다.

 

예시 테이블 소개

 

우리가 오늘 살펴볼 예제는 리트코드의 1179. Reformat Department Table이다.

테이블 구조는 아래와 같다.

테이블 명: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+
In SQL,(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

 

CASE WHEN을 이용한 피봇팅

 

피봇팅(Pivoting)이란, 행 레벨로 만들어진 데이터를 열 레벨로 전환하는 것을 의미한다. 

 

가령, 우리가 ID별 월별 revenue값을 알고 싶은 상황에서 Depertment 테이블에서 아래와 같이 조회했다고 가정하자.

select id, month, sum(revenue)
from Department
group by id, month
order by 1, 2;

 

그렇게 되면, 아래와 같이 조회가 될 것이다.

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

 

 

해당 방식은 나쁘지 않은 방식이고, 쿼리도 굉장히 간단하여 꽤 괜찮은 방법이라고 생각할 수 있다. 그러나, 이는 시각적으로 그리 좋지 않은 방법이다.

 

이런 상황에서 우리는 피봇팅이라는 기법을 활용하여 행 레벨로 만들어진 데이터들을 열 레벨로 전환할 수 있다.

• 피봇팅에서는 앞서 언급한 것처럼, group by, aggregate, case-when절이 사용이 된다.

select id
    , sum(case when month = 'Jan' then revenue end) as Jan_Revenue
    , sum(case when month = 'Feb' then revenue end) as Feb_Revenue
    , sum(case when month = 'Mar' then revenue end) as Mar_Revenue
    , sum(case when month = 'Apr' then revenue end) as Apr_Revenue
    , sum(case when month = 'May' then revenue end) as May_Revenue
    , sum(case when month = 'Jun' then revenue end) as Jun_Revenue
    , sum(case when month = 'Jul' then revenue end) as Jul_Revenue
    , sum(case when month = 'Aug' then revenue end) as Aug_Revenue
    , sum(case when month = 'Sep' then revenue end) as Sep_Revenue
    , sum(case when month = 'Oct' then revenue end) as Oct_Revenue
    , sum(case when month = 'Nov' then revenue end) as Nov_Revenue
    , sum(case when month = 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;

 

위의 쿼리로 조회를 하게 되면 우리가 볼 수 있는 테이블은 아래와 같다.

| id | jan_revenue | feb_revenue | mar_revenue | apr_revenue | may_revenue | jun_revenue | jul_revenue | aug_revenue | sep_revenue | oct_revenue | nov_revenue | dec_revenue |
| -- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- |
| 3  | null        | 10000       | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 2  | 9000        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 1  | 8000        | 7000        | 6000        | null        | null        | null        | null        | null        | null        | null        | null        | null        |

 

해당 절은 case when절도 많이 들어가고 쓸데없이 SQL 구문이 길어진 듯한 느낌을 받을 수 있지만 , 사람의 시각적인 측면에서 바로 알아차리기 쉬운 테이블로 조회가 가능하다는 장점을 가지고 있다.

 

CASE WHEN 주의할 점

 

case when 절을 사용할 때 주의해야 할 몇 가지가 있는데, 그 중 피봇팅에서 가장 주의해야 할 점은 count의 혼용(?)이다.

 

count를 이용한 case when절의 구문은 아래와 같다.

-- 올바른 count(case when ~) 구문
-- count case when의 else의 디폴트는 Null이다.

count(case when 조건 then 1 else null end)

-- 아래 구문은 위 구문과 동일하다.
count(case when 조건 then 1 end)

 

여기서 우리가 짚고 넘어가야 할 점은 else다음은 Null이 디폴트값이라는 것이다. Null인 경우는 SQL 자체에서 count를 하지 않는다.

 

그러나 만일, 우리가 아래와 같이 SQL 구문을 짜게 된다면,

-- 잘못된 쿼리
-- else 0으로 카운트를 지정하면, 모든 row가 카운팅이 되버리게 된다.
count(case when 조건 then 1 else 0 end)

DB는 1이든 0이든 카운트로 판단하여 결론적으로는 Row 카운트의 갯수가 맞지 않는 일이 벌어질 수 있다.

 

따라서 case when을 사용할 때, count(case when 조건 then 1)만 작성하던지, 아니면 아래와 같이 sum(case when ~)을 사용하여 피벗테이블 카운트를 작성할 수 있다. (sum 절을 사용하면 count의 올바른 쿼리와 동일한 값이 나오는 것을 확인할 수 있다.)

-- 올바른 방식 2
-- sum(case when ~)으로 하게 되면 0은 카운팅하지 않는 것과 동일한 효과다.
sum(case when 조건 then 1 else 0 end)

---

Reference

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

https://fenzhan.tistory.com/26

[SQL] CASE WHEN으로 Pivot 하는 방법