[MySQL]피벗(Pivot)

1. 피벗(Pivot)이란

행(row) 데이터를 열(column) 형태로 변환하는 과정을 의미한다. 즉, 기존에는 한 열에 나열된 값을 개별 열로 바꿔 데이터를 더욱 직관적으로 정리하는 방법이다.

2. 예제 데이터

아래 데이터는 각 사용자(uName)가 특정 계절(season)에 사용한 금액(amount)을 기록한 것이다. 하지만 이렇게 되어 있으면 각 사용자의 계절별 사용 금액을 한눈에 보기 어렵다.

uName	season	amount
SpongeBob	봄	100
SpongeBob	여름	150
Patrick	겨울	200
SpongeBob	겨울	300
Patrick	여름	120
SpongeBob	가을	80
Patrick	봄	230

위 데이터를 피벗을 적용하여 각 계절을 열(column)로 변환하면 다음과 같은 결과가 나온다.

uName	봄	여름	가을	겨울	합계
SpongeBob	100	150	80	300	630
Patrick	230	120	0	200	550

3. MySQL에서 피벗 구현하기

MySQL에서는 SUM(IF(...)) 또는 SUM(CASE WHEN ... THEN ... END)을 활용하여 피벗을 구현할 수 있다.

3-1. IF(MySQL에서 사용 가능)

SELECT uName,
SUM(IF(season='봄', amount, 0)) AS '봄',
SUM(IF(season='여름', amount, 0)) AS '여름',
SUM(IF(season='가을', amount, 0)) AS '가을',
SUM(IF(season='겨울', amount, 0)) AS '겨울',
SUM(amount) AS '합계'
FROM pivottest
GROUP BY uName;

3-2. CASE WHEN ... THEN ... END(대부분의 DBMS에서 사용 가능)

SELECT uName,
SUM(CASE WHEN season='봄' THEN amount ELSE 0 END) AS "봄",
SUM(CASE WHEN season='여름' THEN amount ELSE 0 END) AS "여름",
SUM(CASE WHEN season='가을' THEN amount ELSE 0 END) AS "가을",
SUM(CASE WHEN season='겨울' THEN amount ELSE 0 END) AS "겨울",
SUM(amount) AS "합계"
FROM pivottest
GROUP BY uName;

4. group by가 헷갈릴 때 팁

4-1. GROUP BY를 설정하는 방법

피벗 테이블을 만들 때 GROUP BY를 어떻게 지정해야 할지 헷갈릴 때가 있다. 이럴 때는 먼저 첫 번째 열(기준이 되는 열)을 기준으로 GROUP BY를 설정하고 이후 데이터를 분류하면 된다.

1. 첫 번째 열을 기준으로 GROUP BY 설정
2. 그룹화한 후 CASE WHEN이나 IF를 이용해 열을 분류
3. 필요한 합계를 구하면 깔끔한 피벗 테이블 완성

4-2. 예제 테이블과 코드

season	SpongeBob	Patrick	합계
봄	100	230	330
여름	150	120	270
가을	80	0	80
겨울	300	200	500

SELECT season,
    SUM(IF(uName = 'SpongeBob', amount, 0)) AS 'SpongeBob',
    SUM(IF(uName = 'Patrick', amount, 0)) AS 'Patrick',
    SUM(amount) AS '합계'
FROM pivottest
GROUP BY season;

1. 기준이 되는 열을 찾기(여기서는 season(계절)이 첫 번째 열이므로, GROUP BY season을 적용)
2. 각 이름(SpongeBob, Patrick)의 값을 개별 열로 변환
3. 합계를 계산