postgre--行列转换相关函数

在进行报表开发时，需要展示各种指标，会经常需要将数据表进行行列互转。

以一个课程成绩表为例,原表的数据如下图，需要展示的一个id号所对应的各科的成绩。

case…when

使用case…when，需要和聚合函数一起使用,不仅仅可以用sum,也可用max。

select 
	id
	,sum(case when subject='语文' then score end) as chinese
	,sum(case when subject='数学' then score end) as math
	,sum(case when subject='英语' then score end) as english
from score
 group by id;

结果如下：

string_agg

string_agg函数语法：

string_agg(expression, delimiter)
属于聚集函数的一种，需要和group by一起使用

可以将分组后的结果拼接到一起。

select 
	id
	,string_agg(subject,',')
	,string_agg(score::varchar,',')  #注意数据类型
 from score
 group by id

结果如下：

split_part

split_part函数用法:

split_part(string text, delimiter text, field int)
按照指定的分隔符分割字符串,返回指定位置的字符

可以将拼接后的字符串以指定分隔符分开。

select 
  id
  ,split_part(temp1,',',1)
  ,split_part(temp2,',',1)
from (select 
	id
	,string_agg(subject,',') as temp1
	,string_agg(score::varchar,',') as temp2
 from score
 group by id)a

crosstab

tablefunc模块
crosstab是tablefunc模块中的函数,需要安装该模块。

create extension tablefunc;

crosstab(text sql)

crosstab函数被用来产生"pivot"显示，在其中数据被横布在页面上而不是直接向下列举。例如,原先的数据格式为:

使用函数后转换后的格式为:

拿上面的课程分数表举例:

select * from
 crosstab('select 
 id,
 subject,
 score 
 from score
 where id in (1,2,3)
  order by  1,2 ')
as t(id int, "数学" int, "英语" int, "语文" int) #注意数据类型	

注意:用这个方法时要注意每一用户的拥有的指标数量和名称需要是一致的

crosstabN(text sql)

crosstabN函数不需要再定义as后面的数据表的格式,它默认根据N的值进行如下的定义:

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

注意:这里的数据类型都为text,若返回的数据类型不为text,则不能使用

crosstab(text source_sql, text category_sql)

source_sql是一个产生源数据集的 SQL 语句。这个语句必须返回一个 row_name列、一个category列以及一个value列。row_name列必须是第一个。 category和value列必须是按照这个顺序的最后两个列。中间可以存在其他列。

select 
  id,
  subject,
  score 
 from score
 order by 1   #一定要进行排序分类

category_sql是一个产生分类集合的 SQL 语句。这个语句必须只返回一列并且没有重复值.

select distinct subject from score;

subject
--------
"语文"
"英语"
"数学"

以课程成绩表为例:

select * from
 crosstab('select 
 id,
 subject,
 score 
 from score
 order by 1 
','select distinct subject from score')
as score(id int, "数学" int, "英语" int, "语文" int)

总结

一般会使用case…when结合聚合函数进行行列互转,遇到需要大量行列互转时可以选择crosstab函数,使用该函数时需要安装tablefunc模块。