Postgresql Pivot Work/PostgreSQL2014. 7. 31. 10:26
집계 테이블을 표현 할 때 흔히 격자형 형태로 표현하기를 원하는 경우들이 있다.
이런 경우 엑셀에서는 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
