[PostgreSQL] CrossTab Query (tablefunc)

cross tab query는 rows를 columns로 바꾸는 query임.
(PIVOT)


1. 모듈설치

PostgreSQL에서는 함수를 통해서 구현할 수 있으며,
해당 함수를 사용하기 위해선 tablefunc 모듈을 설치해야 함.

[설치법]
* 가정
  - 소스 컴파일 방식으로 설치
  - 소스 압축 풀린 경로 : /usr/local/src/postgresql-8.4.3

1. 모듈 경로로 이동
cd /usr/local/src/postgresql-8.4.3/contrib/tablefunc

2. 컴파일&설치
make
make install

3. 함수추가
// 컴파일 하고 나면 모듈 디렉토리에 tablefunc.sql 파일이 생김.
// 그 안에 함수추가에 필요한 query들이 들어 있음.
// 원하는 놈으로 취사선택 해서 설치할 것.
// 본인은 아래정도의 함수를 추가함.

SET search_path TO pg_catalog;

CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8)
RETURNS setof float8
AS '$libdir/tablefunc','normal_rand'
LANGUAGE C VOLATILE STRICT;
ALTER FUNCTION normal_rand(integer, double precision, double precision) OWNER TO postgres;

CREATE OR REPLACE FUNCTION crosstab(text)
RETURNS setof record
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION crosstab(text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION crosstab(text,int)
RETURNS setof record
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION crosstab(text,int) OWNER TO postgres;

CREATE OR REPLACE FUNCTION crosstab(text,text)
RETURNS setof record
AS '$libdir/tablefunc','crosstab_hash'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION crosstab(text,text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int,text)
RETURNS setof record
AS '$libdir/tablefunc','connectby_text'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION connectby(text,text,text,text,int,text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int)
RETURNS setof record
AS '$libdir/tablefunc','connectby_text'
LANGUAGE C STABLE STRICT;
ALTER FUNCTION connectby(text,text,text,text,int) OWNER TO postgres;

2. crosstab() 함수로 pivot 구현


'공장' 테이블과 '분기별 작업진행률' 테이블이 두개가 있다고 치고,
한 번의 query로 공장별-분기별 작업진행률을 가져오려고 한다고 가정.

공장
create table plant (idx int, name text);
insert into plant values (1, '제1공장');
insert into plant values (2, '제2공장');

분기별 작업진행률
create table proc (idx int, lv text, pp int);
insert into proc values (1, '1분기', 33);
insert into proc values (2, '2분기', 52);
insert into proc values (3, '3분기', 77);
insert into proc values (4, '4분기', 89);
insert into proc values (5, '1분기', 11);
insert into proc values (6, '2분기', 27);
insert into proc values (7, '3분기', 45);
insert into proc values (8, '4분기', 99);



1. ANSI-SQL 이용

아래의 query로 가져와서 분기별로 쪼개서 사용해야 함.

SELECT
DISTINCT ON (a.name, b.idx, b.lv)
a.name as "공장명", b.lv as "분기", b.pp||'%' as "진행률" 
FROM plant as a, proc as b 
WHERE a.idx=b.idx 
ORDER BY b.idx, b.lv;

결과)
   공장명   |  분기  |  진행률 
----------+-------+--------
  제1공장   | 1분기  |  33%
  제1공장   | 2분기  |  52%
  제1공장   | 3분기  |  77%
  제1공장   | 4분기  |  89%
  제2공장   | 1분기  |  11%
  제2공장   | 2분기  |  27%
  제2공장   | 3분기  |  45%
  제2공장   | 4분기  |  99%



2. crosstabI() 함수 이용

아래의 query로 한방에 가져올 수 있음.

SELECT 
    a.name as "공장명",
    b.p1||'%' as "1분기 진행률",
    b.p2||'%' as "2분기 진행률",
    b.p3||'%' as "3분기 진행률",
    b.p4||'%' as "4분기 진행률" 
FROM
    plant as a,
    (select * from
        crosstab('select * from proc order by 1,2')
        as ct(p_idx int, p1 int, p2 int, p3 int, p4 int)) as b 
WHERE a.idx=b.p_idx;

결과)
    공장명    |  1분기 진행률  |  2분기 진행률  |  3분기 진행률  |  4분기 진행률 
-----------+--------------+---------------+--------------+--------------
  제1공장    |  33%              |  52%               |  77%             |  89%
  제2공장    |  11%              |  27%               |  45%             |  99%



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