postgresql之交叉表:crosstab
在学习postgresql行列转换的时候,看到了使用crosstab。所以写这篇博客巩固记录一下。
postgresql本身提供了额外的模块:tablefunc供我们使用。但是使用前,需要我们安装一下插件。
-- 导入模块
create extension tablefunc;
导入tablefunc模块后,就会在函数列表中出现以下函数,如下图。
结合官方文档,我们一起来学习一下。
https://www.postgresql.org/docs/9.2/tablefunc.html
- crosstab(text)
crosstab(text sql)
crosstab(text sql, int N)
这里的crosstab函数接受的是一个文本参数,参数是一个sql查询,生成以列展示的原始数据,然后通过函数得到表格式化的数据。如下图:
其中,sql参数是产生数据源的sql语句,此语句必须返回一个row_name列,一个category类和一个value列。N是一个过时的参数,如果提供则忽略(以前它必须和输出值列的数量想匹配,现在由调用查询函数确定)。
该函数为具有相同row_name 值的每组连续输入行生成一个输出行。它使用这些行中的值字段 从左到右填充输出值列。如果组中的行数少于输出值列数,则用空值填充额外的输出列;如果有更多行,则跳过额外的输入行。
在实践中,SQL 查询应始终指定ORDER BY 1,2以确保输入行正确排序,也就是说,将具有相同row_name的值放在一起并在行内正确排序。注意,crosstab它本身并不关注查询结果的第二列;它只是用来排序的,以控制第三列值在页面上出现的顺序。
给一个案例,先插入测试数据。
create table t1(
name varchar(255),
sub varchar(255),
score int4
);
insert into t1 values ('张三','语文',67);
insert into t1 values ('张三','数学',78);
insert into t1 values ('张三','英语',63);
insert into t1 values ('李四','语文',86);
此时,根据crosstab(text)函数可以得到如下写法:
select * from crosstab(
'select name,sub,score from t1 order by 1,2'
)as t(name varchar,数学 int,英语 int,语文 int)
注意:
-
这