PostgreSQL的行轉列應用

Oracle裡面有比較成熟的內建行轉列函數，Postgres也有這麼一個應用，名稱就是 tablefunc.
Pg的這個應用在安裝的時候預設是不安裝的，如果已經有安裝，則在$PGHOME/share/extension/路徑下會有tablefunc*的三個檔案。分別是：

[postgres @localhost extension]$ pwd
/home/postgres/share/extension
[postgres@localhost  extension]$ ll tablefunc*
-rw-r--r-- 1 postgres postgres 2153 04-19 15:27 tablefunc--1.0.sql
-rw-r--r-- 1 postgres postgres  174 04-19 15:27 tablefunc.control
-rw-r--r-- 1 postgres postgres 1144 04-19 15:27 tablefunc--unpackaged--1.0.sql

沒有的話則需要去PG的安裝包裡去編譯一下。 下面主要介紹下crosstab這個函數的應用

1.編譯安裝tablefunc
# cd ~/setupfile/postgresql-9.1.2/contrib/tablefunc/

[postgres @greentea tablefunc]$ make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  -c -o tablefunc.o tablefunc.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -L../../src/port -L/usr/lib  -Wl,-rpath,'/home/postgres/lib',--enable-new-dtags -lm  -shared -o tablefunc.so tablefunc.o
rm tablefunc.o
[postgres @greentea tablefunc]$ make install
/bin/mkdir -p '/home/postgres/share/extension'
/bin/mkdir -p '/home/postgres/lib'
/bin/sh ../../config/install-sh -c -m 644 ./tablefunc.control '/home/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./tablefunc--1.0.sql ./tablefunc--unpackaged--1.0.sql  '/home/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 755  tablefunc.so '/home/postgres/lib/'

2.建立tablefunc
安裝完成後$PGHOME/share/extension/就會出現開頭提到的那三個檔案，此時需要在資料庫裡建立一下extension
較早的如8.*版本的建立的文法是psql -f tablefunc.sql 即可，9.*以後版本不行，使用的文法如下：

[postgres @greentea extension]$ psql -U postgres -d test
psql (9.1.2)
Type "help" for help.

test=# create extension tablefunc;
CREATE EXTENSION
test=# 

此時用戶端工具上也能出現一個新的拓展：

另外在PUBLIC下面會建立11個函數
 

3.測試
測試資料：

create table t(day date,equipment varchar(20),output integer);
insert into t values('2010-04-01','DAT501',100);
insert into t values('2010-04-01','DAT502',120);
insert into t values('2010-04-01','DAT503',130);
insert into t values('2010-04-02','DAT501',110);
insert into t values('2010-04-02','DAT502',105);

test=# select * from t;
    day    | equipment | output 
------------+-----------+--------
 2010-04-01 | DAT501    |    100
 2010-04-01 | DAT502    |    120
 2010-04-02 | DAT501    |    110
 2010-04-02 | DAT502    |    105
 2010-04-01 | DAT503    |    130
(5 rows)

test=# SELECT * FROM crosstab('select day, equipment, output from t order by 1,2')  AS t(day date, DAT501 integer, DAT502 integer,DAT503 integer);
    day    | dat501 | dat502 | dat503 
------------+--------+--------+--------
 2010-04-01 |    100 |    120 |    130
 2010-04-02 |    110 |    105 |       
(2 rows)

4.總結
這個應用其實在統計月份、季度等帶時間性質的場合下比較不錯，有一個前提是後面組合的dat501,dat502,dat503是已知的，也就是說提前知道我們會分成幾列。這也是一個不足，就是不能動態地產生列。

PostgreSQL 9.3物化視圖使用

使用 PostgreSQL 資料庫日期類型的 4 個提示

PostgreSQL刪除表中重複資料行

PostgreSQL緩衝詳述

Windows平台編譯 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)環境的配置與安裝