PostgreSQL的行转列应用

为什么80%的码农都做不了架构师？>>>   

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是已知的，也就是说提前知道我们会分成几列。这也是一个不足，就是不能动态地生成列。


参考：http://blog.163.com/digoal@126/blog/static/163877040201151253211186/

转载于:https://my.oschina.net/Kenyon/blog/54357