1 测试表数据
|
1 2 3 4 5 6 7 8 9 |
SELECT relative_label_content FROM frk_s.label_cor_gene relative_label_content ------ AA BB CC |
2 列转行 写法
写法1：
string_agg
|
1 2 3 4 5 6 7 8 9 10 |
SELECT frwybs, string_agg (relative_label_content, ',' ) as relative_label_content FROM frk_s.label_cor_gene GROUP BY frwybs relative_label_content ------------ AA,BB,CC |
写法2：
array_to_string(ARRAY_AGG (text),',')
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT frwybs, array_to_string( ARRAY_AGG ( DISTINCT relative_label_content), ',' ) as labels_content FROM frk_s.label_cor_gene GROUP BY frwybs labels_content ------------ AA,BB,CC |
补充： PostgreSQL 行列转换(兼容oracle pivot unpivot)
oracle11g开始内置了数据透视表pivot table这一功能，可以用来实现行列转换的功能，但是在数据量较大的时候使用性能就会较差。
pivot语法为：
|
1 2 3 4 5 6 7 |
SELECT ... FROM ... PIVOT [XML] (pivot_clause pivot_for_clause pivot_in_clause ) WHERE ... |
oracle pivot使用例子：
–创建测试表并插入数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table usr ( name varchar2(20), score int , class varchar2(20) ); insert into usr values ( 'a' ,20, 'math' ); insert into usr values ( 'a' ,22, 'phy' ); insert into usr values ( 'b' ,23, 'phy' ); insert into usr values ( 'b' ,21, 'math' ); insert into usr values ( 'c' ,22, 'phy' ); insert into usr values ( 'c' ,24, 'math' ); insert into usr values ( 'd' ,25, 'math' ); insert into usr values ( 'd' ,23, 'phy' ); |
–使用pivot进行行列转换
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select * from usr 2 pivot( sum (score) for class in ( 'math' , 'phy' ) 3 4 5 ); NAME 'math' 'phy' -------------------- ---------- ---------- d 25 23 a 20 22 b 21 23 c 24 22 |
我们还可以使用unpivot来实现列转行。
unpivot语法为：
|
1 2 3 4 5 6 7 |
SELECT ... FROM ... UNPIVOT [INCLUDE|EXCLUDE NULLS] (unpivot_clause unpivot_for_clause unpivot_in_clause ) WHERE ... |
oracle unpivot使用例子：
–创建测试表并插入数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE t1 ( VendorID int , Emp1 int , Emp2 int , Emp3 int , Emp4 int , Emp5 int ); INSERT INTO t1 VALUES (1,4,3,5,4,4); INSERT INTO t1 VALUES (2,4,1,5,5,5); INSERT INTO t1 VALUES (3,4,3,5,4,4); INSERT INTO t1 VALUES (4,4,2,5,5,4); INSERT INTO t1 VALUES (5,5,1,5,5,5); |
–使用unpivot进行列转行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SQL> select * from t1 2 UNPIVOT( orders for Employee in (emp1,emp2,emp3,emp4,emp5) ); 3 4 VENDORID EMPL ORDERS ---------- ---- ---------- 1 EMP1 4 1 EMP2 3 1 EMP3 5 1 EMP4 4 1 EMP5 4 2 EMP1 4 2 EMP2 1 2 EMP3 5 2 EMP4 5 2 EMP5 5 3 EMP1 4 VENDORID EMPL ORDERS ---------- ---- ---------- 3 EMP2 3 3 EMP3 5 3 EMP4 4 3 EMP5 4 4 EMP1 4 4 EMP2 2 4 EMP3 5 4 EMP4 5 4 EMP5 4 5 EMP1 5 5 EMP2 1 VENDORID EMPL ORDERS ---------- ---- ---------- 5 EMP3 5 5 EMP4 5 5 EMP5 5 25 rows selected. |
那么在pg中该如何实现oracle的pivot/unpivot的行列转行功能呢？pg中自带的tablefunc插件可以实现，我们可以使用该插件中的crosstab函数接口进行行列转换。
pg行转列例子：
–建表插入测试数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
create table tbl (seller text,se_year int ,se_month int ,se_amount int ); insert into tbl values ( 'test1' ,2020,01,123456); insert into tbl values ( 'test1' ,2020,02,234567); insert into tbl values ( 'test1' ,2020,03,345678); insert into tbl values ( 'test1' ,2020,04,345678); insert into tbl values ( 'test1' ,2020,05,567890); insert into tbl values ( 'test2' ,2020,01,12); insert into tbl values ( 'test2' ,2020,02,23); insert into tbl values ( 'test2' ,2020,03,34); insert into tbl values ( 'test2' ,2020,04,45); insert into tbl values ( 'test2' ,2020,05,56); insert into tbl values ( 'test3' ,2020,03,12); insert into tbl values ( 'test3' ,2020,04,45); insert into tbl values ( 'test3' ,2020,05,56); insert into tbl values ( 'test4' ,2020,02,20); insert into tbl values ( 'test4' ,2020,03,30); insert into tbl values ( 'test4' ,2020,04,40); insert into tbl values ( 'test4' ,2020,05,50); insert into tbl values ( 'test1' ,2019,01,123456); insert into tbl values ( 'test1' ,2019,02,234567); insert into tbl values ( 'test1' ,2019,03,345678); insert into tbl values ( 'test1' ,2019,04,345678); insert into tbl values ( 'test1' ,2019,05,567890); insert into tbl values ( 'test1' ,2019,06,123456); insert into tbl values ( 'test1' ,2019,07,234567); insert into tbl values ( 'test1' ,2019,08,345678); insert into tbl values ( 'test1' ,2019,09,345678); insert into tbl values ( 'test1' ,2019,10,567890); insert into tbl values ( 'test1' ,2019,11,123456); insert into tbl values ( 'test1' ,2019,12,234567); insert into tbl values ( 'test2' ,2019,11,12); insert into tbl values ( 'test2' ,2019,12,23); insert into tbl select * from tbl; |
–行转列
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
bill=# select bill-# js->> 'seller' as seller, bill-# js->> 'se_year' as se_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-# from crosstab( bill(# -- 这个是需要进行行列变换的源SQL ， 数据源。 bill(# -- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份） bill(# -- （必须在下一个参数中提取出对应的所有枚举值） bill(# $$ select jsonb_build_object( 'seller' , seller, 'se_year' , se_year) as js, se_month, sum (se_amount) from tbl group by 1,2 order by 1$$, bill(# -- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值 bill(# -- 或(select * from (values('jan'),...('dec')) t(se_month)) bill(# 'select distinct se_month from tbl order by 1' bill(# ) bill-# as -- crosstab 输出格式 bill-# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个） bill(# Jan numeric , -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列） bill(# feb numeric , -- ...同上 bill(# mar numeric , bill(# apr numeric , bill(# may numeric , bill(# jun numeric , bill(# jul numeric , bill(# aug numeric , bill(# sep numeric , bill(# oct numeric , bill(# nov numeric , bill(# dec numeric bill(# ) bill-# order by 1,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 | | | | | | | (6 rows ) |
–列转行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
bill=# with a as ( -- A对应原始数据（即需要列转行的数据） bill(# select bill(# js->> 'seller' as seller, bill(# js->> 'se_year' as se_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(# from crosstab( bill(# -- 这个是需要进行行列变换的源SQL ， 数据源。 bill(# -- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份） bill(# -- （必须在下一个参数中提取出对应的所有枚举值） bill(# $$ select jsonb_build_object( 'seller' , seller, 'se_year' , se_year) as js, se_month, sum (se_amount) from tbl group by 1,2 order by 1$$, bill(# -- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值 bill(# -- 或(select * from (values('jan'),...('dec')) t(se_month)) bill(# 'select distinct se_month from tbl order by 1' bill(# ) bill(# as -- crosstab 输出格式 bill(# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个） bill(# Jan numeric , -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列） bill(# feb numeric , -- ...同上 bill(# mar numeric , bill(# apr numeric , bill(# may numeric , bill(# jun numeric , bill(# jul numeric , bill(# aug numeric , bill(# sep numeric , bill(# oct numeric , bill(# nov numeric , bill(# dec numeric bill(# ) bill(# order by 1,2 bill(# ) bill-# , bill-# -- b ， 用jsonb把多列合并为一列，并使用jsonb_each展开。 bill-# b as ( select seller, se_year, jsonb_each(row_to_json(a)::jsonb- 'seller' ::text- 'se_year' ::text) as rec from a) bill-# select seller, se_year, (b.rec). key as month , (b.rec).value as sum from b; 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 (72 rows ) |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/HSH205572/article/details/83743990
查看更多关于PostgreSQL 实现列转行问题的详细内容...