SQL로 Pivot Table 만들기

게시일 : 2019년 07월 20일    
# PostgreSQL # tablefunc # crosstab # MySQL

SQL로 Pivot Table 만드는 법을 알아본다.

데이터 예시

id country region activity num_of_places
1 France Normandy River cruise 2
2 Germany Bavaria Golf 5
3 Germany Berlin Adventure park 2
4 France lle-de-France River cruise 1
5 Sweden Stockholm River cruise 3
6 France Normandy Kart racing 4

pivot 결과

country adventure_park golf river_cruise kart_racing
France 0 0 3 4
Germany 2 5 0 0
Sweden 0 0 3 0

1. GROUP BY와 Aggregate 함수를 활용하여 계산하기

공통

SELECT country,
    SUM(CASE WHEN activity='Adventure park' THEN num_of_places ELSE 0 END) as adventure_park,
    SUM(CASE WHEN activity='Golf' THEN  num_of_places ELSE 0 END) as golf,
    SUM(CASE WHEN activity='River cruise' THEN num_of_places ELSE 0 END) as river_cruise,
    SUM(CASE WHEN activity='Kart racing' THEN num_of_places ELSE 0 END) as kart_racing
FROM leisures
GROUP BY country
ORDER BY country;

DB Fiddle에서 확인 - PostgreSQL

MySQL

MySQL에서만 제공하는 IF 함수를 활용할 수도 있다.

SELECT country,
    SUM(IF(activity='Adventure park', num_of_places, 0)) as adventure_park,
    SUM(IF(activity='Golf', num_of_places, 0)) as golf,
    SUM(IF(activity='River cruise', num_of_places, 0)) as river_cruise,
    SUM(IF(activity='Kart racing', num_of_places, 0)) as kart_racing
FROM leisures
GROUP BY country
ORDER BY country;

DB Fiddle에서 확인 - MySQL

2. 특수하게 계산하기

PostgreSQL

PostgreSQL에는 tablefunc extension에서 지원하는 crosstab을 활용할 수 있다.

주의할 점은 crosstab은 aggregate가 이미 되어 있는 테이블에서 pivot만 가능하다.

먼저, table을 aggregate한 후 crosstab에 적용해야 한다.

-- base가 될 aggregate 함수를 실행한 테이블
SELECT country, activity, SUM(num_of_places) as total 
FROM leisures 
GROUP BY country, activity 
ORDER BY country, activity;
country activity total
France Kart racing 4
France River cruise 3
Germany Adventure park 2
Germany Golf 5
Sweden River cruise 3

crosstab 사용법

crosstab ( base가 될 table 생성 쿼리, pivot할 컬럼 쿼리(DISTINCT 사용) ) AS (pivot 테이블 컬럼 정의)

pivot할 컬럼 쿼리의 값들이 pivot 테이블의 컬럼이 되도록 정의한다.

-- 먼저 해당 데이터베이스에 extension 추가를 실행해야 한다. 
CREATE EXTENSION tablefunc;

SELECT *
FROM crosstab(
  'SELECT country, activity, SELECT country, activity, COALESCE(SUM(num_of_places),0) as total
   FROM leisures 
   GROUP BY country, activity 
   ORDER BY country, activity',
   'SELECT DISTINCT activity FROM leisures ORDER BY activity') 
   AS (country text, adventure_park integer, golf integer, 
       kart_racing integer, river_cruise integer);

DB Fiddle에서 확인 - PostgreSQL

결과에서 NULL을 바로 처리할 수는 없다.

country adventure_park golf kart_racing river_cruise
France NULL NULL 4 3
Germany 2 5 NULL NULL
Sweden NULL NULL NULL 3