[DB] MySQL/MariaDB Pivot(피벗) 테이블 사용/만들기

DB를 조회할 때 종종 행과 열이 바뀌어서 조회가 필요한 경우가 있습니다.

RDB(관계형 DB)는 행과 열로 이루어져 있는데, 이 위치를 뒤바꾸는 것을 피벗(Pivot)이라고 합니다.

 

Oracle 11g 에서는 해당 기능을 오라클에서 제공하지만, MySQL/MariaDB 는 따로 제공을 하지 않습니다.

해당 글에서는 MySQL/MariaDB 에서 피벗 형식으로 조회가 되도록 간단하게 예제를 진행해보도록 하겠습니다.

---

0. 테이블

- 현재 테이블의 구조 및 데이터는 다음과 같습니다.

• student_table

• department_table

1. Pivot 사용 전

- 두 테이블을 조인하여 연도별, 학과별 입학 인원을 조회하려고 합니다.

- 학과에 입학인원이 없더라도, 0명으로 조회되어야 때문에 Outer Join 을 사용하고, 조건을 주어 0명으로 조회되도록 합니다.

select 
    	b.department_name, 
    	admission_year, 
    	if(admission_year is not null, sum(1), 0) as cnt
from 
	student_table a
	right outer join department_table b
		on a.department_id = b.department_id
group by b.department_id, a.admission_year
order by admission_year
;

- 결과는 아래와 같습니다.

- 하지만 조회를 하면 Group By 조건에 의해 연도에 없는 학과들은 조회가 안되거나, 같은 학과지만 연도가 달라 중복되어 보이게 됩니다.

-  저의 목표는 각 학과별/연도별로 입학 인원 수를 조회하고자 합니다.

- 피벗을 사용한다면 아래 그림과 같이 볼 수 있습니다.

2. Pivot 사용 후

- 먼저 학생 테이블에서 학과별, 연도별로 수를 구합니다.

select 
	department_id, admission_year, count(1) as cnt
from student_table
group by department_id, admission_year
;

- 피벗 테이블을 만들 때는 피벗의 컬럼이 되어 줄 키(Key)가 필요합니다.

- 위 데이터에서는 admission_year(입학연도) 가 해당 키가 됩니다.

- 해당 키를 통해서 피벗 형태로 만들어주는 쿼리를 작성하면 아래와 같습니다.

select
       b.department_name as '학과',
       max(if(a.admission_year = '2022', cnt, 0)) as '2022',
       max(if(a.admission_year = '2021', cnt, 0)) as '2021',
       max(if(a.admission_year = '2020', cnt, 0)) as '2020'
from
	(
    		select department_id, admission_year, sum(1) as cnt
        	from student_table
        	group by department_id, admission_year
    	) a
	right outer join department_table b
	on a.department_id = b.department_id
group by b.department_id
;

- if 문을 통해서 키와 동일한 컬럼을 세팅합니다.

- 추가로 학과 테이블과 조인을 함으로서 1명도 입학하지 않은 학과도 조회되도록 합니다.

- 최종 결과는 아래 그림과 같습니다.

---

참고

- https://shxrecord.tistory.com/181