Postgresql Pivot

집계 테이블을 표현 할 때 흔히 격자형 형태로 표현하기를 원하는 경우들이 있다.

이런 경우 엑셀에서는 pivot 이라는 기능을 이용한다.

데이터베이스에서도 비슷한 상황이 존재할 수 있는데, 이를 위해서 DBMS마다 약간씩 다른 방법을 제공한다. Postgresql 의 경우는 아래은 방법을 제공한다.

SELECT a::date AS cdate,

       b.desc AS TYPE,

       (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

  (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;

위의 쿼리 결과는 아래와 같다.

cdate	type	val
2014-07-21	OSX	3128
2014-07-21	Windows	8118
2014-07-21	Linux	6699
2014-07-22	OSX	6364
2014-07-22	Windows	4439
2014-07-22	Linux	3727
2014-07-23	OSX	6010
2014-07-23	Windows	5006
2014-07-23	Linux	2249
2014-07-24	OSX	3664
2014-07-24	Windows	2377
2014-07-24	Linux	6907
2014-07-25	OSX	6145
2014-07-25	Windows	728
2014-07-25	Linux	5920
2014-07-26	OSX	6704
2014-07-26	Windows	1331
2014-07-26	Linux	1472
2014-07-27	OSX	5668
2014-07-27	Windows	1408
2014-07-27	Linux	6162
2014-07-28	OSX	6805
2014-07-28	Windows	4762
2014-07-28	Linux	5977
2014-07-29	OSX	8917
2014-07-29	Windows	9468
2014-07-29	Linux	7261
2014-07-30	OSX	1913
2014-07-30	Windows	1495
2014-07-30	Linux	5639
2014-07-31	OSX	5195
2014-07-31	Windows	4623
2014-07-31	Linux	3756

이런 결과의 격자형 형태를 위해 아래처럼 case를 이용한 쿼리를 작성하는 방법이 일반적이다.

select cdate

, sum(case when type = 'OSX' then val else 0 end) OSX

, sum(case when type = 'Windows' then val else 0 end) Windows

, sum(case when type = 'Linux' then val else 0 end) Linux

from (

SELECT a::date AS cdate,

  b.desc AS TYPE,

  (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

 (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b

) a

group by cdate

order by 1;

결과는 아래와 같다.

cdate	osx	windows	linux
2014-07-21	7095	287	1101
2014-07-22	9741	3417	3237
2014-07-23	6321	7028	9647
2014-07-24	569	8523	7655
2014-07-25	7371	4659	2482
2014-07-26	8352	9015	559
2014-07-27	604	5552	8965
2014-07-28	79	4691	1138
2014-07-29	3355	9816	2113
2014-07-30	4707	1285	2997
2014-07-31	2029	8379	3283

Postgresql 에서 제공하는 또다른 방법은 crosstab을 이용하는 벙법이다. 쿼리는 아래와 같다.

-- crosstab 을 이용하기 위한 extension 설치

CREATE EXTENSION tablefunc;

-- Query

SELECT *

FROM crosstab(

'SELECT

a cdate,

b.desc AS type,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - ''10 days''::interval)::date, now()::date, ''1 day''::interval) a,

(SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2'

,'SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'

) 

AS ct(cdate date, OSX int, Windows int, Linux int);

결과는 위의 case를 이용한 Query와 동일하다.

출처 : http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/

참고 : http://www.postgresql.org/docs/9.1/static/tablefunc.html