PostgreSQL实现交叉表（行列转换）的5种方法示例
交叉表
交叉表（CrossTabulations）是一种常用的分类汇总表格。使用交叉表查询，显示源于表中某个字段的汇总值，并将它们分组，其中一组列在数据表的左侧，另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算，如：求和、平均值、记数、最大值、最小值等。使用交叉表查询数据非常直观明了，被广泛应用。交叉表查询也是数据库的一个特点。
例如：
select表1.组名,
(select表1.成员姓名from表2bwhere表1.成员1id=表2.成员id)as成员1id,
(select表1.成员姓名from表2bwhere表1.成员2id=表2.成员id)as成员2id,
(select表1.成员姓名from表2bwhere表1.成员3id=表2.成员id)as成员3id
from表1，表2
--这种就是交叉表查询
交叉报表是报表当中常见的类型，属于基本的报表，是行、列方向都有分组的报表。这里牵涉到另外一个概念即分组报表。这是所有报表当中最普通，最常见的报表类型，也是所有报表工具都支持的一种报表格式。从一般概念上来讲，分组报表就是只有纵向的分组。传统的分组报表制作方式是把报表划分为条带状，用户根据一个数据绑定向导指定分组，汇总字段，生成标准的分组报表。
这里我来演示下在POSTGRESQL里面如何实现交叉表的展示，下面话不多说了，来一起看看详细的介绍吧
原始表数据如下：
t_girl=#select*fromscore; name|subject|score -------+---------+------- Lucy|English|100 Lucy|Physics|90 Lucy|Math|85 Lily|English|95 Lily|Physics|81 Lily|Math|84 David|English|100 David|Physics|86 David|Math|89 Simon|English|90 Simon|Physics|76 Simon|Math|79 (12rows) Time:2.066ms
想要实现以下的结果：
name|English|Physics|Math ------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89
大致有以下几种方法：
1、用标准SQL展现出来
t_girl=#selectname, t_girl-#sum(casewhensubject='English'thenscoreelse0end)as"English", t_girl-#sum(casewhensubject='Physics'thenscoreelse0end)as"Physics", t_girl-#sum(casewhensubject='Math'thenscoreelse0end)as"Math" t_girl-#fromscore t_girl-#groupbynameorderbynamedesc; name|English|Physics|Math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:1.123ms
2、用PostgreSQL提供的第三方扩展tablefunc带来的函数实现
以下函数crosstab里面的SQL必须有三个字段，name,分类以及分类值来作为起始参数，必须以name,分类值作为输出参数。
t_girl=#SELECT*
FROMcrosstab('selectname,subject,scorefromscoreorderbynamedesc',$$values('English'::text),('Physics'::text),('Math'::text)$$)
ASscore(nametext,Englishint,Physicsint,Mathint);
name|english|physics|math
-------+---------+---------+------
Simon|90|76|79
Lucy|100|90|85
Lily|95|81|84
David|100|86|89
(4rows)
Time:2.059ms
3、用PostgreSQL自身的聚合函数实现
t_girl=#selectname,split_part(split_part(tmp,',',1),':',2)as"English", t_girl-#split_part(split_part(tmp,',',2),':',2)as"Physics", t_girl-#split_part(split_part(tmp,',',3),':',2)as"Math" t_girl-#from t_girl-#( t_girl(#selectname,string_agg(subject||':'||score,',')astmpfromscoregroupbynameorderbynamedesc t_girl(#)asT; name|English|Physics|Math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:2.396ms
4、存储函数实现
createorreplacefunctionfunc_ytt_crosstab_py()
returnssetofytt_crosstab
as
$ytt$
forrowinplpy.cursor("selectname,string_agg(subject||':'||score,',')astmpfromscoregroupbynameorderbynamedesc"):
a=row['tmp'].split(',')
yield(row['name'],a[0].split(':')[1],a[1].split(':')[1],a[2].split(':')[1])
$ytt$languageplpythonu;
t_girl=#selectname,english,physics,mathfromfunc_ytt_crosstab_py();
name|english|physics|math
-------+---------+---------+------
Simon|90|76|79
Lucy|100|90|85
Lily|95|81|84
David|100|86|89
(4rows)
Time:2.687ms
5、用PLPGSQL来实现
t_girl=#createtypeytt_crosstabas(nametext,Englishtext,Physicstext,Mathtext); CREATETYPE Time:22.518ms createorreplacefunctionfunc_ytt_crosstab() returnssetofytt_crosstab as $ytt$ declarev_nametext:=''; v_englishtext:=''; v_physicstext:=''; v_mathtext:=''; v_tmp_resulttext:=''; declarecs1cursorforselectname,string_agg(subject||':'||score,',')fromscoregroupbynameorderbynamedesc; begin opencs1; loop fetchcs1intov_name,v_tmp_result; exitwhennotfound; v_english=split_part(split_part(v_tmp_result,',',1),':',2); v_physics=split_part(split_part(v_tmp_result,',',2),':',2); v_math=split_part(split_part(v_tmp_result,',',3),':',2); returnqueryselectv_name,v_english,v_physics,v_math; endloop; end; $ytt$languageplpgsql; t_girl=#selectname,English,Physics,Mathfromfunc_ytt_crosstab(); name|english|physics|math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:2.127ms
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。