SQL로 Pivot Table 만들기 - YA-Hwang 기술 블로그

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 함수를 활용하여 계산하기

공통

SUM은 NULL값을 제외하고 계산한다. SUM 결과가 NULL인 경우만 COALESCE로 처리한다.

( 불필요한 계산을 방지해서 성능에 영향을 줄 수 있음 )

SELECT country,
    COALESCE( SUM(CASE WHEN activity='Adventure park' THEN num_of_places END), 0) as adventure_park,
    COALESCE( SUM(CASE WHEN activity='Golf' THEN  num_of_places END), 0) as golf,
    COALESCE( SUM(CASE WHEN activity='River cruise' THEN num_of_places END), 0) as river_cruise,
    COALESCE( SUM(CASE WHEN activity='Kart racing' THEN num_of_places END), 0) 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

FILTER

FILTER 절은 Aggregate function(COUNT, SUM, …)과 함께 사용가능하다.

위의 예제처럼 CASE WHEN을 활용한 쿼리와 동일하게 만들 수 있다. 또한, 쿼리가 이해하기 쉬운 구조로 되어 있다.

예시

SUM(expression) FILTER(WHERE condition) == SUM(CASE WHEN condition THEN expression END)

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

DB Fiddle에서 확인 - PostgreSQL

crosstab

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, 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

References :

filter — Selective Aggregates