PostgreSQL 实现列转行问题

2023-07-06 23:39:03 179

1测试表数据

SELECT
relative_label_content
FROM
frk_s.label_cor_gene
relative_label_content
------
AA
BB
CC

2列转行写法

写法1：

string_agg

SELECT
frwybs,
string_agg(relative_label_content,',')asrelative_label_content
FROM
frk_s.label_cor_gene
GROUPBY
frwybs
relative_label_content
------------
AA,BB,CC

写法2：

array_to_string(ARRAY_AGG(text),',')

SELECT
frwybs,
array_to_string(
ARRAY_AGG(DISTINCTrelative_label_content),
','
)aslabels_content
FROM
frk_s.label_cor_gene
GROUPBY
frwybs
labels_content
------------
AA,BB,CC

补充：PostgreSQL行列转换(兼容oraclepivotunpivot)

oracle11g开始内置了数据透视表pivottable这一功能，可以用来实现行列转换的功能，但是在数据量较大的时候使用性能就会较差。

pivot语法为：

SELECT...
FROM...
PIVOT[XML]
(pivot_clause
pivot_for_clause
pivot_in_clause)
WHERE...

oraclepivot使用例子：

–创建测试表并插入数据

createtableusr
(namevarchar2(20),
scoreint,
classvarchar2(20)
);
insertintousrvalues('a',20,'math');
insertintousrvalues('a',22,'phy');
insertintousrvalues('b',23,'phy');
insertintousrvalues('b',21,'math');
insertintousrvalues('c',22,'phy');
insertintousrvalues('c',24,'math');
insertintousrvalues('d',25,'math');
insertintousrvalues('d',23,'phy');

–使用pivot进行行列转换

SQL>select*fromusr
2pivot(
sum(score)
forclassin('math','phy')
345);
NAME'math''phy'
----------------------------------------
d2523
a2022
b2123
c2422

我们还可以使用unpivot来实现列转行。

unpivot语法为：

SELECT...
FROM...
UNPIVOT[INCLUDE|EXCLUDENULLS]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause)
WHERE...

oracleunpivot使用例子：

–创建测试表并插入数据

CREATETABLEt1
(
VendorIDint,
Emp1int,
Emp2int,
Emp3int,
Emp4int,
Emp5int
);
INSERTINTOt1VALUES(1,4,3,5,4,4);
INSERTINTOt1VALUES(2,4,1,5,5,5);
INSERTINTOt1VALUES(3,4,3,5,4,4);
INSERTINTOt1VALUES(4,4,2,5,5,4);
INSERTINTOt1VALUES(5,5,1,5,5,5);

–使用unpivot进行列转行

SQL>select*fromt1
2UNPIVOT(
ordersforEmployeein(emp1,emp2,emp3,emp4,emp5)
);34
VENDORIDEMPLORDERS
------------------------
1EMP14
1EMP23
1EMP35
1EMP44
1EMP54
2EMP14
2EMP21
2EMP35
2EMP45
2EMP55
3EMP14
VENDORIDEMPLORDERS
------------------------
3EMP23
3EMP35
3EMP44
3EMP54
4EMP14
4EMP22
4EMP35
4EMP45
4EMP54
5EMP15
5EMP21
VENDORIDEMPLORDERS
------------------------
5EMP35
5EMP45
5EMP55
25rowsselected.

那么在pg中该如何实现oracle的pivot/unpivot的行列转行功能呢？pg中自带的tablefunc插件可以实现，我们可以使用该插件中的crosstab函数接口进行行列转换。

pg行转列例子：

–建表插入测试数据

createtabletbl(sellertext,se_yearint,se_monthint,se_amountint);
insertintotblvalues('test1',2020,01,123456);
insertintotblvalues('test1',2020,02,234567);
insertintotblvalues('test1',2020,03,345678);
insertintotblvalues('test1',2020,04,345678);
insertintotblvalues('test1',2020,05,567890);
insertintotblvalues('test2',2020,01,12);
insertintotblvalues('test2',2020,02,23);
insertintotblvalues('test2',2020,03,34);
insertintotblvalues('test2',2020,04,45);
insertintotblvalues('test2',2020,05,56);
insertintotblvalues('test3',2020,03,12);
insertintotblvalues('test3',2020,04,45);
insertintotblvalues('test3',2020,05,56);
insertintotblvalues('test4',2020,02,20);
insertintotblvalues('test4',2020,03,30);
insertintotblvalues('test4',2020,04,40);
insertintotblvalues('test4',2020,05,50);
insertintotblvalues('test1',2019,01,123456);
insertintotblvalues('test1',2019,02,234567);
insertintotblvalues('test1',2019,03,345678);
insertintotblvalues('test1',2019,04,345678);
insertintotblvalues('test1',2019,05,567890);
insertintotblvalues('test1',2019,06,123456);
insertintotblvalues('test1',2019,07,234567);
insertintotblvalues('test1',2019,08,345678);
insertintotblvalues('test1',2019,09,345678);
insertintotblvalues('test1',2019,10,567890);
insertintotblvalues('test1',2019,11,123456);
insertintotblvalues('test1',2019,12,234567);
insertintotblvalues('test2',2019,11,12);
insertintotblvalues('test2',2019,12,23);
insertintotblselect*fromtbl;

–行转列

bill=#select
bill-#js->>'seller'asseller,
bill-#js->>'se_year'asse_year,
bill-#jan,
bill-#feb,
bill-#mar,
bill-#apr,
bill-#may,
bill-#jun,
bill-#jul,
bill-#aug,
bill-#sep,
bill-#oct,
bill-#nov,
bill-#dec
bill-#fromcrosstab(
bill(#--这个是需要进行行列变换的源SQL，数据源。
bill(#--排序字段为groupby字段，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）
bill(#--（必须在下一个参数中提取出对应的所有枚举值）
bill(#$$selectjsonb_build_object('seller',seller,'se_year',se_year)asjs,se_month,sum(se_amount)fromtblgroupby1,2orderby1$$,
bill(#--行列转换的行，有哪些值被提取出来作为列。这个在这里代表的是月份，也就是se_month的值
bill(#--或(select*from(values('jan'),...('dec'))t(se_month))
bill(#'selectdistinctse_monthfromtblorderby1'
bill(#)
bill-#as--crosstab输出格式
bill-#(jsjsonb,--第一个参数SQL内对应的orderby对应的字段（1个或多个）
bill(#Jannumeric,--第一个参数SQL内对应导数第二个字段的枚举值，（行转列）
bill(#febnumeric,--...同上
bill(#marnumeric,
bill(#aprnumeric,
bill(#maynumeric,
bill(#junnumeric,
bill(#julnumeric,
bill(#augnumeric,
bill(#sepnumeric,
bill(#octnumeric,
bill(#novnumeric,
bill(#decnumeric
bill(#)
bill-#orderby1,2;
seller|se_year|jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
--------+---------+--------+--------+--------+--------+---------+--------+--------+--------+--------+---------+--------+--------
test1|2019|246912|469134|691356|691356|1135780|246912|469134|691356|691356|1135780|246912|469134
test1|2020|246912|469134|691356|691356|1135780|||||||
test2|2019|||||||||||24|46
test2|2020|24|46|68|90|112|||||||
test3|2020|||24|90|112|||||||
test4|2020||40|60|80|100|||||||
(6rows)

–列转行

bill=#withaas(--A对应原始数据（即需要列转行的数据）
bill(#select
bill(#js->>'seller'asseller,
bill(#js->>'se_year'asse_year,
bill(#jan,
bill(#feb,
bill(#mar,
bill(#apr,
bill(#may,
bill(#jun,
bill(#jul,
bill(#aug,
bill(#sep,
bill(#oct,
bill(#nov,
bill(#dec
bill(#fromcrosstab(
bill(#--这个是需要进行行列变换的源SQL，数据源。
bill(#--排序字段为groupby字段，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）
bill(#--（必须在下一个参数中提取出对应的所有枚举值）
bill(#$$selectjsonb_build_object('seller',seller,'se_year',se_year)asjs,se_month,sum(se_amount)fromtblgroupby1,2orderby1$$,
bill(#--行列转换的行，有哪些值被提取出来作为列。这个在这里代表的是月份，也就是se_month的值
bill(#--或(select*from(values('jan'),...('dec'))t(se_month))
bill(#'selectdistinctse_monthfromtblorderby1'
bill(#)
bill(#as--crosstab输出格式
bill(#(jsjsonb,--第一个参数SQL内对应的orderby对应的字段（1个或多个）
bill(#Jannumeric,--第一个参数SQL内对应导数第二个字段的枚举值，（行转列）
bill(#febnumeric,--...同上
bill(#marnumeric,
bill(#aprnumeric,
bill(#maynumeric,
bill(#junnumeric,
bill(#julnumeric,
bill(#augnumeric,
bill(#sepnumeric,
bill(#octnumeric,
bill(#novnumeric,
bill(#decnumeric
bill(#)
bill(#orderby1,2
bill(#)
bill-#,
bill-#--b，用jsonb把多列合并为一列，并使用jsonb_each展开。
bill-#bas(selectseller,se_year,jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text)asrecfroma)
bill-#selectseller,se_year,(b.rec).keyasmonth,(b.rec).valueassumfromb;
seller|se_year|month|sum
--------+---------+-------+---------
test1|2019|apr|691356
test1|2019|aug|691356
test1|2019|dec|469134
test1|2019|feb|469134
test1|2019|jan|246912
test1|2019|jul|469134
test1|2019|jun|246912
test1|2019|mar|691356
test1|2019|may|1135780
test1|2019|nov|246912
test1|2019|oct|1135780
test1|2019|sep|691356
test1|2020|apr|691356
test1|2020|aug|null
test1|2020|dec|null
test1|2020|feb|469134
test1|2020|jan|246912
test1|2020|jul|null
test1|2020|jun|null
test1|2020|mar|691356
test1|2020|may|1135780
test1|2020|nov|null
test1|2020|oct|null
test1|2020|sep|null
test2|2019|apr|null
test2|2019|aug|null
test2|2019|dec|46
test2|2019|feb|null
test2|2019|jan|null
test2|2019|jul|null
test2|2019|jun|null
test2|2019|mar|null
test2|2019|may|null
test2|2019|nov|24
test2|2019|oct|null
test2|2019|sep|null
test2|2020|apr|90
test2|2020|aug|null
test2|2020|dec|null
test2|2020|feb|46
test2|2020|jan|24
test2|2020|jul|null
test2|2020|jun|null
test2|2020|mar|68
test2|2020|may|112
test2|2020|nov|null
test2|2020|oct|null
test2|2020|sep|null
test3|2020|apr|90
test3|2020|aug|null
test3|2020|dec|null
test3|2020|feb|null
test3|2020|jan|null
test3|2020|jul|null
test3|2020|jun|null
test3|2020|mar|24
test3|2020|may|112
test3|2020|nov|null
test3|2020|oct|null
test3|2020|sep|null
test4|2020|apr|80
test4|2020|aug|null
test4|2020|dec|null
test4|2020|feb|40
test4|2020|jan|null
test4|2020|jul|null
test4|2020|jun|null
test4|2020|mar|60
test4|2020|may|100
test4|2020|nov|null
test4|2020|oct|null
test4|2020|sep|null
(72rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。