피봇팅(Pivotiong)

피벗팅이란 행 레벨로 만들어진 데이터를 열 레벨로 전환하는 것을 말한다.

예를 들어

YEAR	DEPTNO	JOB	EMPID	SALARY
2022	10	SALESMAN	2	25
2022	20	MANAGER	1	25
2022	30	CLERK	3	30
2022	20	MANAGER	1	10
…			…

SUM

위와 같은 데이터가 있을 때 년도별 직원의 급여를 보고자 할 때

SELECT YEAR, EMPID, SUM(SALARY) SALARY
FROM EMP
GROUP BY YEAR, EMPID

이 코드를 실행하면 아래와 같은 테이블을 볼 수 있다.

YEAR	EMPID	SALARY
2022	1	100
2022	2	200
2022	3	300
2022	4	400
2022	5	500
2022	6	600

이 때, 위 데이터를 피봇 형식으로 만들기 위해서는 아래와 같은 코드를 실행시키면 된다.

SELECT YEAR, AVG(SALARY) AVG_SAL,
	SUM(CASE WHEN EMPID = 1 THEN SALARY END) EMP_1,
	SUM(CASE WHEN EMPID = 2 THEN SALARY END) EMP_2,
	...
	SUM(CASE WHEN EMPID = N THEN SALARY END) EMP_N
# EMPID가 N일 때 SALARY를 더한 값을 EMP_N으로 지정
FROM EMP
GROUP BY YEAR

그럼 아래와 같은 결과를 볼 수 있다.

YEAR	AVG_SAL	EMP_1	EMP_2	…	EMP_N
2022	300	100	200	…	900
2023	500	200	250	…	900

이렇게 피봇 형식으로 보면 보기에는 좋지만 데이터 적으로는(?) 좋지 않다.

왜냐면 N명의 직원 데이터를 보기 위해서는 N개의 코드를 쳐야 하고, 후에 직원이 추가된다면 그만큼 또 코드가 늘어나기 때문이다.

COUNT

각 부서별 직원 수를 보고 싶을 때는 아래 코드를 사용하면 된다.

SELECT DEPTNO, COUNT(*) AS COUNT
FROM EMP
GROUP BY DEPTNO

DEPTNO	COUNT
10	10
20	5
30	6

각 부서를 group by로 묶고, 직업별 사람 수를 pivot형식으로 나타내보자

SELECT DEPTNO, COUNT(CASE WHEN JOB='SALESMAN' THEN 1 END) AS SALESMAN,
	COUNT(CASE WHEN JOB='MANAGER' THEN 1 END) AS MANAGER,
	COUNT(CASE WHEN JOB='CLERK' THEN 1 END) AS CLERK
FROM EMP
GROUP BY DEPTNO

위 코드를 실행하면 아래와 같은 결과를 볼 수 있다.

DEPTNO	SALESMAN	MANAGER	CLERK
10	5	3	2
20	3	NULL	2
30	3	NULL	3

이 때, 조심해야 할 점이 있는데 바로

COUNT(CASE WHEN JOB=’SALESMAN’ THEN 1 ELSE 0 END) AS SALESMAN

이렇게 코드를 쓰면 안 된다는 점이다.

THEN 1 이라 적는다고 그걸 하나씩 세라는 말이 아니다

1이 하나 라는 의미가 아님!

THEN 3, THEN 10 이렇게 적어도 결과는 위 테이블처럼 나온다.

(+3, +10이 아니라는 말)

그렇기 때문에 아래와 같은 코드를 실행하면 잘못된 결과가 나온다.

SELECT DEPTNO, COUNT(*) AS ALL,
	COUNT(CASE WHEN JOB='SALESMAN' THEN 1 ELSE 0 END) AS SALESMAN,
	COUNT(CASE WHEN JOB='MANAGER' THEN 1 ELSE 0 END) AS MANAGER,
	COUNT(CASE WHEN JOB='CLERK' THEN 1 ELSE 0 END) AS CLERK
FROM EMP
GROUP BY DEPTNO

DEPTNO	ALL	SALESMAN	MANAGER	CLERK
10	10	10	10	10
20	5	5	5	5
30	6	6	6	6

각 부서 인원이 직업별로 나눠서 COUNT 되는 게 아니라 그냥 직업에 관계 없이 전체 수가 집계되는 것을 볼 수 있다.

ELSE를 사용하고 싶다면 ELSE 0 가 아닌 ELSE NULL 을 사용해야 한다.

CASE WHEN JOB=’~’ THEN 1 ELSE 0 END 를 사용하고 싶다면 COUNT가 아닌 SUM 을 이용해야 한다.

SELECT DEPTNO, COUNT(*) AS ALL,
	SUM(CASE WHEN JOB='SALESMAN' THEN 1 ELSE 0 END) AS SALESMAN,
	SUM(CASE WHEN JOB='MANAGER' THEN 1 ELSE 0 END) AS MANAGER,
	SUM(CASE WHEN JOB='CLERK' THEN 1 ELSE 0 END) AS CLERK
FROM EMP
GROUP BY DEPTNO

이렇게 적으면 맨 처음 봤던 올바른 결과를 볼 수 있음