欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL 實現(xiàn)列轉行問題

 更新時間：2021年01月11日 09:49:01   作者：dev_hui  
這篇文章主要介紹了PostgreSQL 實現(xiàn)列轉行問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

1 測試表數(shù)據(jù)

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, ',') 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),',')

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開始內置了數(shù)據(jù)透視表pivot table這一功能，可以用來實現(xiàn)行列轉換的功能，但是在數(shù)據(jù)量較大的時候使用性能就會較差。

pivot語法為：

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

oracle pivot使用例子：

–創(chuàng)建測試表并插入數(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進行行列轉換

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來實現(xiàn)列轉行。

unpivot語法為：

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

oracle unpivot使用例子：

–創(chuàng)建測試表并插入數(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進行列轉行

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中該如何實現(xiàn)oracle的pivot/unpivot的行列轉行功能呢？pg中自帶的tablefunc插件可以實現(xiàn)，我們可以使用該插件中的crosstab函數(shù)接口進行行列轉換。

pg行轉列例子：

–建表插入測試數(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;

–行轉列

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 ， 數(shù)據(jù)源。  
bill(#  -- 排序字段為group by字段 ，最后一個字段為轉換后的內容字段，導數(shù)第二個字段為行列變換的字段（內容為枚舉，比如月份）  
bill(#  -- （必須在下一個參數(shù)中提取出對應的所有枚舉值）  
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, -- 第一個參數(shù)SQL內對應的order by對應的字段（1個或多個）  
bill(#  Jan numeric, -- 第一個參數(shù)SQL內對應導數(shù)第二個字段的枚舉值，（行轉列）  
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)

–列轉行

bill=# with a as ( -- A對應原始數(shù)據(jù)（即需要列轉行的數(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(#  -- 這個是需要進行行列變換的源SQL ， 數(shù)據(jù)源。  
bill(#  -- 排序字段為group by字段 ，最后一個字段為轉換后的內容字段，導數(shù)第二個字段為行列變換的字段（內容為枚舉，比如月份）  
bill(#  -- （必須在下一個參數(shù)中提取出對應的所有枚舉值）  
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, -- 第一個參數(shù)SQL內對應的order by對應的字段（1個或多個）  
bill(#  Jan numeric, -- 第一個參數(shù)SQL內對應導數(shù)第二個字段的枚舉值，（行轉列）  
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)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

相關文章

  • PostgreSQL中查看當前時間和日期的幾種常用方法

    PostgreSQL中查看當前時間和日期的幾種常用方法

    在 PostgreSQL 中,有多個函數(shù)可以用來查看當前時間和日期,這些函數(shù)在處理時間戳、日期和時間的計算時非常有用,以下是幾種常用的查看當前時間和日期的函數(shù)及示例,需要的朋友可以參考下
    2024-10-10
  • Postgresql設置遠程訪問的方法(需要設置防火墻或者關閉防火墻)

    Postgresql設置遠程訪問的方法(需要設置防火墻或者關閉防火墻)

    這篇文章主要介紹了Postgresql設置遠程訪問的方法(需要設置防火墻或者關閉防火墻),本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2021-03-03
  • PostgreSQL入門簡介

    PostgreSQL入門簡介

    PostgreSQL是一個免費的對象-關系型數(shù)據(jù)庫服務器（ORDBMS)，遵循靈活的開源協(xié)議BSD。這篇文章主要介紹了PostgreSQL入門簡介,需要的朋友可以參考下
    2020-12-12
  • PostgreSQL 查找當前數(shù)據(jù)庫的所有表操作

    PostgreSQL 查找當前數(shù)據(jù)庫的所有表操作

    這篇文章主要介紹了PostgreSQL 查找當前數(shù)據(jù)庫的所有表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • Postgresql開啟SQL執(zhí)行語句收集過程

    Postgresql開啟SQL執(zhí)行語句收集過程

    本文介紹如何修改PostgreSQL的配置文件來啟用SQL執(zhí)行語句的日志記錄,包括打開配置文件、修改日志相關設置以及查看日志文件的方法,關鍵步驟包括刪除注釋符號、設定日志存儲路徑和文件名、選擇記錄的SQL語句類型,此操作有助于數(shù)據(jù)庫管理和問題調試
    2024-10-10
  • PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列代碼示例

    PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列代碼示例

    數(shù)據(jù)庫中主鍵的生成一般是通過序列來生成,下面這篇文章主要給大家介紹了關于PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
    2023-11-11
  • PostgreSQL 遠程連接配置操作

    PostgreSQL 遠程連接配置操作

    這篇文章主要介紹了PostgreSQL 遠程連接配置操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL之分區(qū)表(partitioning)

    PostgreSQL之分區(qū)表(partitioning)

    通過合理的設計，可以將選擇一定的規(guī)則，將大表切分多個不重不漏的子表，這就是傳說中的partitioning。比如，我們可以按時間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢的效能
    2016-11-11
  • PostgreSQL創(chuàng)建新用戶所遇見的權限問題以及解決辦法

    PostgreSQL創(chuàng)建新用戶所遇見的權限問題以及解決辦法

    這篇文章主要給大家介紹了關于PostgreSQL創(chuàng)建新用戶所遇見的權限問題以及解決辦法, 在PostgreSQL中創(chuàng)建一個新用戶非常簡單,但可能會遇到權限問題,需要的朋友可以參考下
    2023-09-09
  • 關于PostgreSQL錯誤日志與慢查詢日志收集

    關于PostgreSQL錯誤日志與慢查詢日志收集

    這篇文章主要介紹了關于PostgreSQL錯誤日志與慢查詢日志收集，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01

最新評論