PostgreSQL 實(shí)現(xiàn)列轉(zhuǎn)行問(wèn)題
更新時(shí)間：2021年01月11日 09:49:01 作者：dev_hui
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)列轉(zhuǎn)行問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
1 測(cè)試表數(shù)據(jù)
SELECT relative_label_content FROM frk_s.label_cor_gene relative_label_content ------ AA BB CC
2 列轉(zhuǎn)行寫(xiě)法
寫(xiě)法1：
string_agg
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
寫(xiě)法2：
array_to_string(ARRAY_AGG (text),',')
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
補(bǔ)充：PostgreSQL行列轉(zhuǎn)換(兼容oracle pivot unpivot)
oracle11g開(kāi)始內(nèi)置了數(shù)據(jù)透視表pivot table這一功能，可以用來(lái)實(shí)現(xiàn)行列轉(zhuǎn)換的功能，但是在數(shù)據(jù)量較大的時(shí)候使用性能就會(huì)較差。
pivot語(yǔ)法為：
SELECT ... FROM ... PIVOT [XML] (pivot_clause pivot_for_clause pivot_in_clause ) WHERE ...
oracle pivot使用例子：
–創(chuàng)建測(cè)試表并插入數(shù)據(jù)
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進(jìn)行行列轉(zhuǎn)換
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來(lái)實(shí)現(xiàn)列轉(zhuǎn)行。
unpivot語(yǔ)法為：
SELECT ... FROM ... UNPIVOT [INCLUDE|EXCLUDE NULLS] (unpivot_clause unpivot_for_clause unpivot_in_clause ) WHERE ...
oracle unpivot使用例子：
–創(chuàng)建測(cè)試表并插入數(shù)據(jù)
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進(jìn)行列轉(zhuǎn)行
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中該如何實(shí)現(xiàn)oracle的pivot/unpivot的行列轉(zhuǎn)行功能呢？pg中自帶的tablefunc插件可以實(shí)現(xiàn)，我們可以使用該插件中的crosstab函數(shù)接口進(jìn)行行列轉(zhuǎn)換。
pg行轉(zhuǎn)列例子：
–建表插入測(cè)試數(shù)據(jù)
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;
–行轉(zhuǎn)列
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(# -- 這個(gè)是需要進(jìn)行行列變換的源SQL ， 數(shù)據(jù)源。
bill(# -- 排序字段為group by字段 ，最后一個(gè)字段為轉(zhuǎn)換后的內(nèi)容字段，導(dǎo)數(shù)第二個(gè)字段為行列變換的字段（內(nèi)容為枚舉，比如月份）
bill(# -- （必須在下一個(gè)參數(shù)中提取出對(duì)應(yīng)的所有枚舉值）
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(# -- 行列轉(zhuǎn)換的行，有哪些值被提取出來(lái)作為列。 這個(gè)在這里代表的是月份，也就是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, -- 第一個(gè)參數(shù)SQL內(nèi)對(duì)應(yīng)的order by對(duì)應(yīng)的字段（1個(gè)或多個(gè)）
bill(# Jan numeric, -- 第一個(gè)參數(shù)SQL內(nèi)對(duì)應(yīng)導(dǎo)數(shù)第二個(gè)字段的枚舉值，（行轉(zhuǎn)列）
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)
–列轉(zhuǎn)行
bill=# with a as ( -- A對(duì)應(yīng)原始數(shù)據(jù)（即需要列轉(zhuǎn)行的數(shù)據(jù)）
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(# -- 這個(gè)是需要進(jìn)行行列變換的源SQL ， 數(shù)據(jù)源。
bill(# -- 排序字段為group by字段 ，最后一個(gè)字段為轉(zhuǎn)換后的內(nèi)容字段，導(dǎo)數(shù)第二個(gè)字段為行列變換的字段（內(nèi)容為枚舉，比如月份）
bill(# -- （必須在下一個(gè)參數(shù)中提取出對(duì)應(yīng)的所有枚舉值）
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(# -- 行列轉(zhuǎn)換的行，有哪些值被提取出來(lái)作為列。 這個(gè)在這里代表的是月份，也就是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, -- 第一個(gè)參數(shù)SQL內(nèi)對(duì)應(yīng)的order by對(duì)應(yīng)的字段（1個(gè)或多個(gè)）
bill(# Jan numeric, -- 第一個(gè)參數(shù)SQL內(nèi)對(duì)應(yīng)導(dǎo)數(shù)第二個(gè)字段的枚舉值，（行轉(zhuǎn)列）
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展開(kāi)。
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)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
PostgreSQL中查看當(dāng)前時(shí)間和日期的幾種常用方法
在 PostgreSQL 中,有多個(gè)函數(shù)可以用來(lái)查看當(dāng)前時(shí)間和日期,這些函數(shù)在處理時(shí)間戳、日期和時(shí)間的計(jì)算時(shí)非常有用,以下是幾種常用的查看當(dāng)前時(shí)間和日期的函數(shù)及示例,需要的朋友可以參考下2024-10-10
Postgresql設(shè)置遠(yuǎn)程訪問(wèn)的方法(需要設(shè)置防火墻或者關(guān)閉防火墻)
這篇文章主要介紹了Postgresql設(shè)置遠(yuǎn)程訪問(wèn)的方法(需要設(shè)置防火墻或者關(guān)閉防火墻),本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
PostgreSQL是一個(gè)免費(fèi)的對(duì)象-關(guān)系型數(shù)據(jù)庫(kù)服務(wù)器（ORDBMS)，遵循靈活的開(kāi)源協(xié)議BSD。這篇文章主要介紹了PostgreSQL入門(mén)簡(jiǎn)介,需要的朋友可以參考下2020-12-12
PostgreSQL 查找當(dāng)前數(shù)據(jù)庫(kù)的所有表操作
這篇文章主要介紹了PostgreSQL 查找當(dāng)前數(shù)據(jù)庫(kù)的所有表操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
Postgresql開(kāi)啟SQL執(zhí)行語(yǔ)句收集過(guò)程
本文介紹如何修改PostgreSQL的配置文件來(lái)啟用SQL執(zhí)行語(yǔ)句的日志記錄,包括打開(kāi)配置文件、修改日志相關(guān)設(shè)置以及查看日志文件的方法,關(guān)鍵步驟包括刪除注釋符號(hào)、設(shè)定日志存儲(chǔ)路徑和文件名、選擇記錄的SQL語(yǔ)句類(lèi)型,此操作有助于數(shù)據(jù)庫(kù)管理和問(wèn)題調(diào)試2024-10-10
PostgreSQL創(chuàng)建自增序列、查詢(xún)序列及使用序列代碼示例
數(shù)據(jù)庫(kù)中主鍵的生成一般是通過(guò)序列來(lái)生成,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL創(chuàng)建自增序列、查詢(xún)序列及使用序列的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11
這篇文章主要介紹了PostgreSQL 遠(yuǎn)程連接配置操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL之分區(qū)表(partitioning)
通過(guò)合理的設(shè)計(jì)，可以將選擇一定的規(guī)則，將大表切分多個(gè)不重不漏的子表，這就是傳說(shuō)中的partitioning。比如，我們可以按時(shí)間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢(xún)的效能2016-11-11
PostgreSQL創(chuàng)建新用戶(hù)所遇見(jiàn)的權(quán)限問(wèn)題以及解決辦法
這篇文章主要給大家介紹了關(guān)于PostgreSQL創(chuàng)建新用戶(hù)所遇見(jiàn)的權(quán)限問(wèn)題以及解決辦法, 在PostgreSQL中創(chuàng)建一個(gè)新用戶(hù)非常簡(jiǎn)單,但可能會(huì)遇到權(quán)限問(wèn)題,需要的朋友可以參考下2023-09-09