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物化视图使用 http://www.linuxidc.com/Linux/2013-09/90045.htm使用 PostgreSQL 数据库日期类型的 4 个提示 http://www.linuxidc.com/Linux/2013-08/89472.htmPostgreSQL删除表中重复数据行 http://www.linuxidc.com/Linux/2013-07/87780.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmPython写的分析MySQL binlog日志工具PostgreSQL的行转列函数使用一例相关资讯 PostgreSQL PostgreSQL行转列

Ubuntu 16.04 下安装 PostgreSQL （08月14日）
PostgreSQL 发布全系安全更新（02月12日）
使用pg_basebackup搭建PostgreSQL （12/30/2015 09:00:29）

Linux下RPM包方式安装PostgreSQL （03月04日）
PostgreSQL9.5新特性之行级安全性（01月19日）
利用pgpool实现PostgreSQL的高可用（12/30/2015 08:54:36）

本文评论查看全部评论（0）