安装extension tablefunc :
digoal=# create extension tablefunc ; CREATE EXTENSION
几个主要函数介绍:
normal_rand:
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):
举例说明:
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 TABLE
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT 0 1
postgres=# INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
INSERT 0 1
postgres=# SELECT *
postgres-# FROM crosstab(
postgres(# 'select rowid, attribute, value
postgres'# from ct
postgres'# 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)
crosstabN(text sql):
crosstab2
, crosstab3
, and crosstab4
crosstab(text source_sql, text category_sql):
postgres=# CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
CREATE TABLE
postgres=# INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT 0 1
postgres=# INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT 0 1
postgres=# INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT 0 1
postgres=# INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT 0 1
postgres=# INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT 0 1
postgres=# 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 1
postgres=# INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
INSERT 0 1
postgres=# SELECT * FROM crosstab
postgres-# (
postgres(# 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
postgres(# 'SELECT DISTINCT attribute FROM cth ORDER BY 1'
postgres(# )
postgres-# AS
postgres-# (
postgres(# rowid text,
postgres(# rowdt timestamp,
postgres(# temperature int4,
postgres(# test_result text,
postgres(# test_startdate timestamp,
postgres(# volts float8
postgres(# );
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 1
postgres=# INSERT INTO cth VALUES('test1','03 March 2003','temperature','42');
INSERT 0 1
postgres=# INSERT INTO cth VALUES('test1','04 March 2003','temperature','422');
INSERT 0 1
postgres=# select * from cth
order by 1,3;
rowid | rowdt | attribute | val
-------+---------------------+----------------+---------------
test1 | 2003-03-01 00:00:00 | temperature | 42
test1 | 2003-03-02 00:00:00 | temperature | 42
test1 | 2003-03-03 00:00:00 | temperature | 42
test1 | 2003-03-04 00:00:00 | temperature | 422
test1 | 2003-03-01 00:00:00 | test_result | PASS
test1 | 2003-03-01 00:00:00 | volts | 2.6987
test2 | 2003-03-02 00:00:00 | temperature | 53
test2 | 2003-03-02 00:00:00 | test_result | FAIL
test2 | 2003-03-02 00:00:00 | test_startdate | 01 March 2003
test2 | 2003-03-02 00:00:00 | volts | 3.1234
(10 rows)
postgres=# SELECT * FROM crosstab
postgres-# (
postgres(# 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
postgres(# 'SELECT DISTINCT attribute FROM cth ORDER BY 1'
postgres(# )
postgres-# AS
postgres-# (
postgres(# rowid text,
postgres(# rowdt timestamp,
postgres(# temperature int4,
postgres(# test_result text,
postgres(# test_startdate timestamp,
postgres(# volts float8
postgres(# );
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)