hank=> \c hank postgreshank=# create extension tablefunc;hank=>\dxtablefunc | 1.0 | public| functions that manipulate whole tables, including crosstab举例： 创建一个员工的工资表并初始化数据
create table tbl_rowtocol(name text,salary int,t_year int,t_month int);
insert into tbl_rowtocol values('bell',5000,2008,12);insert into tbl_rowtocol values('bell',4000,2008,11);insert into tbl_rowtocol values('bell',4000,2008,10);insert into tbl_rowtocol values('bell',3000,2008,09);insert into tbl_rowtocol values('bell',3000,2008,08);insert into tbl_rowtocol values('bell',5000,2008,07);insert into tbl_rowtocol values('bell',5000,2008,06);insert into tbl_rowtocol values('bell',5000,2008,05);insert into tbl_rowtocol values('bell',5000,2008,04);insert into tbl_rowtocol values('bell',5000,2008,03);insert into tbl_rowtocol values('bell',1000,2008,02);insert into tbl_rowtocol values('bell',2000,2008,01);insert into tbl_rowtocol values('bell',2000,2010,01);insert into tbl_rowtocol values('bell',1000,2010,05);insert into tbl_rowtocol values('reson',2000,2008,01);insert into tbl_rowtocol values('reson',2000,2008,04);insert into tbl_rowtocol values('reson',2000,2008,07);insert into tbl_rowtocol values('reson',2000,2008,09);
下面就利用自带的函数进行行列转换 先看语法
| Function | Returns | Description |
|---|---|---|
| setof float8 | Produces a set of normally distributed random values --返回numvals个正态分布随机值 | |
| setof record | Produces a "pivot table" containing row names plus N value columns, where N is determined by the row type specified in the calling query --返回一个行名称加上N列数值二维表，N由查询的结果而定 | |
| setof table_crosstab_N | Produces a "pivot table" containing row names plus N value columns. |
|
| setof record | Produces a "pivot table" with the value columns specified by a second query --列由category_sql而定 | |
| setof record | Obsolete version of |
|
| setof record | Produces a representation of a hierarchical tree structure --返回一个树状结构 |
select substr(name,1,char_length(name)-4) as name,substr(name,char_length(name)-3) as year,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" from crosstab('select name||t_year,t_month,salary from tbl_rowtocol order by 1','select * from generate_series(1,12) order by 1')as (name text,"Jan" int,"Feb" int,"Mar" int,"Apr" int,"May" int,"Jun" int,"Jul" int,"Aug" int,"Sep" int,"Oct" int,"Nov" int,"Dec" int) order by 1,2;
这里用到crosstab(text source_sql, text category_sql)
source_sql = select name||t_year,t_month,salary from tbl_rowtocol order by 1 --数据源SQLcategory_sql = select * from generate_series(1,12) order by 1 --需要分为列的SQL，这里有12个值， 所以分别代表1~12月这里解释下 select substr(name,1,char_length(name)-4) --截取名称select substr(name,char_length(name)-3) --截取年 这里要按name||t_year列分组，只不过通过substr分成2个比较容易好看的字段 name||t_year是分组列 t_month 是需要成为每一列的 salary是对应值
(name text,"Jan" int,"Feb" int,"Mar" int,"Apr" int,"May" int,"Jun" int,"Jul" int,"Aug" int,"Sep" int,"Oct" int,"Nov" int,"Dec" int) --这里定义列类型，注意这里要定义name text 是source_sql中"name||t_year"列语句得到一个这样的二维表
normal_rand实例： 取10个值，平均数为8 ，方差为5
hank=> SELECT * FROM normal_rand(10, 8, 5); normal_rand ------------------- 14.1461903681259 6.71858432804888 1.7629468082413 9.95435832036283 5.22620689349945 8.90771881275266 -1.28601983743638 10.8009828959392 3.29085668056401 11.0749340357927crosstab(text sql)实例引用官方文档的实例
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');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
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 | 当然这里category_3 text可以去掉，因为这里需要分为列的attribute值只有2个值att2和att3.
hank=> select * from ct;
id | rowid | attribute | value
----+-------+-----------+-------
1 | test1 | att1 | val1
2 | test1 | att2 | val2
3 | test1 | att3 | val3
4 | test1 | att4 | val4
5 | test2 | att1 | val5
6 | test2 | att2 | val6
7 | test2 | att3 | val7
8 | test2 | att4 | val8这里返回attribute=att2 和attribute=att3的一个二维表,参照原表标红的记录比较直观。connectby:语法结构：
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
| Parameter | Description |
|---|---|
| relname | Name of the source relation --源表名称 |
| keyid_fld | Name of the key field --键值名称 |
| parent_keyid_fld | Name of the parent-key field --父节点键值名称 |
| orderby_fld | Name of the field to order siblings by (optional) --排序兄弟的字段名 |
| start_with | Key value of the row to start at --键值开始值 |
| max_depth | Maximum depth to descend to, or zero for unlimited depth --最大深度，0表示无限深度 |
| branch_delim | String to separate keys with in branch output (optional) --分支输出时的分隔符 |
官方文档的例子看一下
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level
-------+--------------+-------
row2 | | 0
row4 | row2 | 1
row6 | row4 | 2
row8 | row6 | 3
row5 | row2 | 1
row9 | row5 | 2
(6 rows)
-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos
-------+--------------+-------+---------------------+-----
row2 | | 0 | row2 | 1
row5 | row2 | 1 | row2~row5 | 2
row9 | row5 | 2 | row2~row5~row9 | 3
row4 | row2 | 1 | row2~row4 | 4
row6 | row4 | 2 | row2~row4~row6 | 5
row8 | row6 | 3 | row2~row4~row6~row8 | 6
(6 rows)
-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos
-------+--------------+-------+-----
row2 | | 0 | 1
row5 | row2 | 1 | 2
row9 | row5 | 2 | 3
row4 | row2 | 1 | 4
row6 | row4 | 2 | 5
row8 | row6 | 3 | 6
(6 rows)hank=> select * from connectby_tree ; keyid | parent_keyid | pos -------+--------------+----- row1 | | 0 row2 | row1 | 0 row3 | row1 | 0 row4 | row2 | 1 row5 | row2 | 0 row6 | row4 | 0 row7 | row3 | 0 row8 | row6 | 0 row9 | row5 | 0
hank=> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row1', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text) order by 1; keyid | parent_keyid | level | branch -------+--------------+-------+-------------------------- row1 | | 0 | row1 row2 | row1 | 1 | row1~row2 row3 | row1 | 1 | row1~row3 row4 | row2 | 2 | row1~row2~row4 row5 | row2 | 2 | row1~row2~row5 row6 | row4 | 3 | row1~row2~row4~row6 row7 | row3 | 2 | row1~row3~row7 row8 | row6 | 4 | row1~row2~row4~row6~row8 row9 | row5 | 3 | row1~row2~row5~row9以上2个对比起来看比较直观一些,以下是树形图
参考：https://www.postgresql.org/docs/current/static/tablefunc.html