PostgreSQL的行转列函数使用一例

开发同事说使用postgres的扩展行转列应用时有一个问题，示例如下该扩展包初步使用参考：http://www.linuxidc.com/Linux/2013-10/91437.htm一、环境
OS:CentOS 6.3
DB:PostgreSQL 9.3.0二、场景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）;
insert into t values（"2010-04-03","DAT503",125）;
insert into t values（"2010-04-04","DAT501",100）;
insert into t values（"2010-04-04","DAT503",200）;
--想得到如下结果
day | dat501 | dat502 | dat503
------------+--------+--------+--------
2010-04-01 | 100 | 120 | 130
2010-04-02 | 110 | 105 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
（4 rows）--但是直接使用crosstab会导致第3,4行不准确，也就是说中间项为Null就会不准
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 |
2010-04-03 | 125 | |
2010-04-04 | 100 | 200 |
（4 rows）三、解决
crosstab还有一个包含两个输入参数的用法，用这个可以解决上述问题
test=# SELECT * FROM crosstab（"select day, equipment, output from t order by 1,2",$$values（"DAT501"::text）,（"DAT502"::text）,（"DAT503"::text）$$） 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 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
（4 rows）--其他的写法,本质都一样
test=# SELECT * FROM crosstab（"select day, equipment, output from t order by 1","select distinct equipment from t order by 1"） 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 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
（4 rows）四、说明
使用两个参数的crosstab其实更安全，推荐使用，其基础用法是crosstab（text source_sql, text category_sql）

参考：
http://www.postgresql.org/docs/9.2/static/tablefunc.html相关阅读：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.htmPostgreSQL的行转列应用Oracle DB 序列相关资讯 PostgreSQL函数 PostgreSQL行转列

PostgreSQL avg（）函数（02月26日）
PostgreSQL的行转列应用（10/15/2013 14:06:57）

用第三方语言编写PostgreSQL 存储（06/01/2014 10:56:59）
使用C编写的动态链接库为（11/24/2012 11:08:46）

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