GROUP BY와 집계함수의 조건문을 이용한 피봇팅(Pivoting)

 

0️⃣ 들어가며

MySQL을 공부하면서 GROUP BY와 집계함수의 CASE WHEN을 이용한 피봇팅(Pivoting)이 가능하다는 걸 배웠다. 피봇팅의 '피봇(Pivot)'은 엑셀의 '피봇 테이블(Pivot Table)'의 그 피봇이 맞다.

엑셀이나 스프레드 시트를 사용해 본 사람 중 피봇 테이블을 사용해보지 않은 사람은 드물 것 같다. 나 역시 회사를 다닐 때 데이터를 요약하거나 보고서를 작성하기 위해 피봇 테이블 기능을 즐겨 썼다. 그런데 단 한 번도 '피봇'이 무슨 뜻인지 궁금해한 적이 없다는 걸 깨달았다.

 

그래서 찾아봤다. 피봇은 무슨 뜻일까?

 

 

 

1️⃣ 피봇(Pivot)이란?

'pivot'의 사전적 정의는 다음과같다.

ⓒ네이버 영어사전

 

요컨대 '축' 혹은 '축을 중심으로 회전하다'는 뜻이다. 그렇다면 피봇 테이블의 뜻은 '축을 중심으로 데이터를 회전(이동/변경)시킬 수 있는 테이블'이라고 보면 되겠다. 더 요약하자면 '재정렬한 표'라고도 할 수 있을 것 같다. 그렇다면 아래 기술할 '피봇팅'은 마찬가지로 '축을 중심으로 데이터를 이동/변경시키는 작업(일련의 과정)' 정도로 정리할 수 있을 것이다.

 

피봇, 피봇팅의 개념을 확실히 알게 되었으니, 우리에게 익숙한 엑셀 말고 SQL 쿼리로 어떻게 피봇팅하여 피봇 테이블을 만들 수 있는지 알아보자.

 

 

 

2️⃣ 사전 지식 - GROUP BY 절과 집계함수(Aggregate Function)

✅ GROUP BY

• GROUP BY 절은 해당 절에 기술한 컬럼의 값으로 그룹화(Grouping)를 해 준다.
• 집계 함수와 함께 사용되어 그룹화된 집계 정보를 보여준다.
• having 절을 이용하여 그룹화한 결과에 조건을 걸 수 있다.

-- 참고 : SQL은 From > Where > Group by > Having > Select > Order by 순으로 실행

-- 구조
SELECT 컬럼
FROM 테이블
WHERE 조건
GROUP BY 적용할 컬럼
HAVING 그룹화 결과의 필터링 조건
ORDER BY 컬럼

-- 예시 (하단 이미지)
SELECT genre, avg(price) as avg_price
FROM book_list
GROUP BY genre

ⓒlearnsql.com/blog/group-by-in-sql-explained/

 

✅ 집계함수

• 여러 개의 행, 혹은 테이블 전체 행을 입력받아 하나의 출력값을 반환하는 함수이다.
• SUM()을 제외하고, NULL을 집계하지 않는다.

ⓒlearnsql.com/blog/sql-aggregate-functions/

 

 

 

3️⃣ 피봇팅(Pivoting)

앞서 피봇 테이블을 만드는 작업을 피봇팅이라고 했다. SQL로 피봇팅을 하려면 GROUP BY와 집계함수, 그리고 집계함수에 적용할 조건문 CASE WHEN을 이용해야 한다.

 

가령 아래와 같이 transactions 테이블이 있다고 해 보자.

 

ⓒlearnsql.com/blog/useful-sql-patterns-pivoting/

 

이 테이블을 피봇 테이블로 만들려면 아래와 같이 쿼리를 구성해야 한다.

 

SELECT customer,
    SUM(CASE WHEN type = 'type_1' THEN amount ELSE 0 END) AS sum_type_1,
    SUM(CASE WHEN type = 'type_2' THEN amount ELSE 0 END) AS sum_type_2,
    SUM(CASE WHEN type = 'type_3' THEN amount ELSE 0 END) AS sum_type_3
FROM transactions
GROUP BY customer
ORDER BY customer;

 

컬럼은 customer, sum_type_1, sum_type_2, sum_type_3 총 4개가 된다.

그리고 집계함수를 사용한 값이 결과값이 된다.

 

ⓒlearnsql.com/blog/useful-sql-patterns-pivoting/

 

즉 고객별 타입별 주문합계를 피봇 테이블로 만든 것이다.

 

 

 

4️⃣ 나가며

어떤 데이터가 필요한지에 따라 집계함수를 달리 사용하여 피봇 테이블을 만들 수 있다. 데이터의 양이 적다면 엑셀에 붙여 넣고 '피봇 테이블 만들기' 기능을 클릭하면 끝날 일이지만, 앞으로 어떤 상황에서 어느 정도 양의 데이터를 다룰지 모르니 꼭 직접 쿼리를 작성해보며 공부하자.