tablefunc 行转列

来源：互联网发布：python async def 编辑：程序博客网时间：2024/05/19 08:36

http://www.postgresql.org/docs/9.3/static/tablefunc.html

安装extension tablefunc ：

digoal=# create extension tablefunc ;CREATE EXTENSION

几个主要函数介绍：

normal_rand：

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

生成numvals个值，其平均为mean，标准偏差为stddev。

例如，两组数的集合 {0,5,9,14} 和 {5,6,8,9} 其平均值都是 7 ，但第二个集合具有较小的标准差。

digoal=# SELECT * FROM normal_rand(10, 5, 3);       normal_rand     ---------------------    2.40647424167461    3.97358357565708    6.83705458528592    3.55287920003402  -0.813572020831272    8.04323869492369    10.4944509678492     6.0051904741271    3.70728813314852 -0.0320731730703967(10 rows)digoal=# SELECT * FROM normal_rand(10, 5, 3000);    normal_rand    -------------------  865.360923345198 -1977.78887247386  2826.97836891189   1053.5080721384  366.541989988861  3071.38745724025  1832.10141151151 -1217.16007210777 -7018.42213577992 -1816.45691496064(10 rows)

crosstab：

crosstab(text sql)：

参数sql的格式必须固定如下：

select rowid, attribute, value from tb;

解释：

rowid：分类row行的约束，即组成group的条件。

attribute：分类column列的约束，即crosstab后形成的setof结果集中有刨去rowid这一列外，会分成多少组group（category）。

value：在分类万row和column后，对应row, column交叉地带应填充的值。

举例说明：

postgres=# CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');CREATE TABLEpostgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');INSERT 0 1postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');INSERT 0 1postgres=# SELECT *postgres-# FROM crosstab(postgres(#   'select rowid, attribute, valuepostgres'#    from ctpostgres'#    where attribute = ''att2'' or attribute = ''att3''postgres'#    order by 1,2')postgres-# AS ct(row_name text, category_1 text, category_2 text, category_3 text); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1    | val2       | val3       |  test2    | val6       | val7       | (2 rows)

此外：结果集setof需要做"AS" 的说明罗列出气列数和列的类型。

crosstabN(text sql)：

跟crosstab一样，只是不需要对结果集setof进行做“AS”声明，而是使用“N” 对齐进行说明结果集的列。

The tablefunc module includes crosstab2, crosstab3, and crosstab4

tablefunc模块只支持N为2,3,4。

如果想之多更多则需要参考文档create type进行创建：http://www.postgresql.org/docs/9.3/static/tablefunc.html

crosstab(text source_sql, text category_sql)：

解释：

source_sql：格式需规定为：select rowid, rowdt, attribute, value from tb;大体与crosstab一致。其中至少需要rowid, attribute, value着三个值。rowdt是额外可有可无的（但是需要注意"AS" 部分的定义要与其相符）。

category_sql：为分类列的约束group(category)。

postgres=# CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');CREATE TABLEpostgres=# INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');INSERT 0 1postgres=# INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');INSERT 0 1postgres=# INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');INSERT 0 1postgres=# INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');INSERT 0 1postgres=# INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');INSERT 0 1postgres=# INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');INSERT 0 1postgres=# INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');INSERT 0 1postgres=# SELECT * FROM crosstabpostgres-# (postgres(#   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',postgres(#   'SELECT DISTINCT attribute FROM cth ORDER BY 1'postgres(# )postgres-# ASpostgres-# (postgres(#        rowid text,postgres(#        rowdt timestamp,postgres(#        temperature int4,postgres(#        test_result text,postgres(#        test_startdate timestamp,postgres(#        volts float8postgres(# ); rowid |        rowdt        | temperature | test_result |   test_startdate    | volts  -------+---------------------+-------------+-------------+---------------------+-------- test1 | 2003-03-01 00:00:00 |          42 | PASS        |                     | 2.6987 test2 | 2003-03-02 00:00:00 |          53 | FAIL        | 2003-03-01 00:00:00 | 3.1234(2 rows)

注意：其中的value值是取最后一条：

postgres=# INSERT INTO cth VALUES('test1','02 March 2003','temperature','42'); INSERT 0 1postgres=# INSERT INTO cth VALUES('test1','03 March 2003','temperature','42'); INSERT 0 1postgres=# INSERT INTO cth VALUES('test1','04 March 2003','temperature','422');INSERT 0 1

postgres=# SELECT * FROM crosstabpostgres-# (postgres(#   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',postgres(#   'SELECT DISTINCT attribute FROM cth ORDER BY 1'postgres(# )postgres-# ASpostgres-# (postgres(#        rowid text,postgres(#        rowdt timestamp,postgres(#        temperature int4,postgres(#        test_result text,postgres(#        test_startdate timestamp,postgres(#        volts float8postgres(# ); rowid |        rowdt        | temperature | test_result |   test_startdate    | volts  -------+---------------------+-------------+-------------+---------------------+-------- test1 | 2003-03-01 00:00:00 |         422 | PASS        |                     | 2.6987 test2 | 2003-03-02 00:00:00 |          53 | FAIL        | 2003-03-01 00:00:00 | 3.1234(2 rows)

1 0