PostgreSQL 實現(xiàn)將多行合并轉(zhuǎn)為列

更新時間：2020年12月28日 15:59:33 作者：碎心煉心

這篇文章主要介紹了PostgreSQL 實現(xiàn)將多行合并轉(zhuǎn)為列的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

需求將下列表格相同id的name拼接起來輸出成一列

id	Name
1	peter
1	lily
2	john

轉(zhuǎn)化后效果：

id	Name
1	peter；lily
2	john；

實現(xiàn)方式使用 array_to_string 和 array_agg 函數(shù)，具體語句如下：

string_agg(expression, delimiter) 把表達(dá)式變成一個數(shù)組

string_agg(expression, delimiter) 直接把一個表達(dá)式變成字符串

select id， array_to_string( array_agg(Name), ';' ) from table group by id

補(bǔ)充：Postgresql實現(xiàn)動態(tài)的行轉(zhuǎn)列

問題

在數(shù)據(jù)處理中，常遇到行轉(zhuǎn)列的問題，比如有如下的問題：

有這樣的一張表

"Student_score"表：

姓名	課程	分?jǐn)?shù)
張三	數(shù)學(xué)	83
張三	物理	93
張三	語文	80
李四	語文	74
李四	數(shù)學(xué)	84
李四	物理	94

我們想要得到像這樣的一張表：

姓名	數(shù)學(xué)	物理	語文
李四	84	94	74
張三	83	93	80

當(dāng)數(shù)據(jù)量比較少時，我們可以在Excel中使用數(shù)據(jù)透視表pivot table的功能實現(xiàn)這個需求，但當(dāng)數(shù)據(jù)量較大，或者我們還需要在數(shù)據(jù)庫中進(jìn)行后續(xù)的數(shù)據(jù)處理時，使用數(shù)據(jù)透視表就顯得不那么高效。

下面，介紹如何在Postgresql中實現(xiàn)數(shù)據(jù)的行轉(zhuǎn)列。

靜態(tài)寫法

當(dāng)我們要轉(zhuǎn)換的值字段是數(shù)值型時，我們可以用SUM()函數(shù)：

CREATE TABLE Student_score(姓名 varchar, 課程 varchar, 分?jǐn)?shù) int);
INSERT INTO Student_score VALUES('張三','數(shù)學(xué)',83);
INSERT INTO Student_score VALUES('張三','物理',93);
INSERT INTO Student_score VALUES('張三','語文',80);
INSERT INTO Student_score VALUES('李四','語文',74);
INSERT INTO Student_score VALUES('李四','數(shù)學(xué)',84);
INSERT INTO Student_score VALUES('李四','物理',94);
select 姓名
  ,sum(case 課程 when '數(shù)學(xué)' then 分?jǐn)?shù) end) as 數(shù)學(xué)
  ,sum(case 課程 when '物理' then 分?jǐn)?shù) end) as 物理
  ,sum(case 課程 when '語文' then 分?jǐn)?shù) end) as 語文
from Student_score
GROUP BY 1

當(dāng)我們要轉(zhuǎn)換的值字段是字符型時，比如我們的表是這樣的：

"Student_grade"表：

姓名	數(shù)學(xué)	物理	語文
張三	優(yōu)	良	及格
李四	良	優(yōu)	及格

我們可以用string_agg()函數(shù)：

CREATE TABLE Student_grade(姓名 varchar, 課程 varchar, 等級 varchar);
INSERT INTO Student_grade VALUES('張三','數(shù)學(xué)','優(yōu)');
INSERT INTO Student_grade VALUES('張三','物理','良');
INSERT INTO Student_grade VALUES('張三','語文','及格');
INSERT INTO Student_grade VALUES('李四','語文','及格');
INSERT INTO Student_grade VALUES('李四','數(shù)學(xué)','良');
INSERT INTO Student_grade VALUES('李四','物理','優(yōu)');

select 姓名

  ,string_agg((case 課程 when '數(shù)學(xué)' then 等級 end),'') as 數(shù)學(xué)
  ,string_agg((case 課程 when '物理' then 等級 end),'') as 物理
  ,string_agg((case 課程 when '語文' then 等級 end),'') as 語文
from Student_grade
GROUP BY 1

內(nèi)置函數(shù)（半動態(tài)）

Postgresql內(nèi)置了tablefunc可實現(xiàn)pivot table的功能。

語法：

SELECT *
FROM crosstab(
 'select row_name,cat,value
  from table
  order by 1,2')
AS (row_name type, category_1 type, category_2 type, category_3 type, ...);

例如：

SELECT *
FROM crosstab(
 'select 姓名,課程,分?jǐn)?shù)
  from Student_score
  order by 1,2')
AS (姓名 varchar, 數(shù)學(xué) int, 物理 int, 語文 int);

需要注意的是crosstab( text sql) 中的sql語句必須按順序返回row_name, category , value，并且必須聲明輸出的各列的列名和數(shù)據(jù)類型。當(dāng)原表中的cat列有很多不同的值，那我們將會得到一個有很多列的表，并且我們需要手動聲明每個列的列名及數(shù)據(jù)類型，顯然這種體驗非常不友好。那有沒有更好的方式呢，我們可以通過手動建立存儲過程（函數(shù)）實現(xiàn)。

自建函數(shù)（動態(tài)）

動態(tài)的行轉(zhuǎn)列我們通過plpgsql實現(xiàn)，大致的思路如下：

判斷value字段的數(shù)據(jù)類型，如果是數(shù)值型，則轉(zhuǎn)入2.，否則轉(zhuǎn)入3.

對cat列中的每個distinct值使用sum(case when)，轉(zhuǎn)成列

對cat列中的每個distinct值使用string_agg(case when)，轉(zhuǎn)成列

實現(xiàn)代碼示例：

CREATE or REPLACE FUNCTION 
long_to_wide(
table_name VARCHAR,
row_name VARCHAR,
cat VARCHAR,
value_field VARCHAR)
returns void as
$$
/*
table_name : 表名
row_name : 行名字段
cat : 轉(zhuǎn)換為列名的字段
value_field : 轉(zhuǎn)換為值的字段
*/
DECLARE v_sql text;
arow record;
value_type VARCHAR;
BEGIN
  
  v_sql='
  drop table if exists temp_table;
  CREATE TABLE temp_table as 
  SELECT distinct '||cat||' as col from '||table_name||'
  order by '||cat;
  execute v_sql;
  v_sql='
  SELECT t.typname AS type
  FROM pg_class c
  ,pg_attribute a
  ,pg_type t 
  WHERE c.relname = lower('''||table_name||''') 
  and a.attnum > 0 
  and a.attrelid = c.oid 
  and a.atttypid = t.oid 
  and a.attname='''||value_field||'''
  ORDER BY a.attnum
  '; 
  execute v_sql into value_type;--獲取值字段的數(shù)據(jù)類型
  
  v_sql='select '||row_name;
  IF value_type in ('numeric','int8','int4','int')--判斷值字段是否是數(shù)值型 
    THEN  
    FOR arow in (SELECT col FROM temp_table) loop
    
    v_sql=v_sql||'
        ,sum(case '||cat||' when '''||arow.col||''' then '||value_field||' else 0 end) '||cat||'_'||arow.col;
    end loop;
  ELSE 
    FOR arow in (SELECT col FROM temp_table) loop
    
    v_sql=v_sql||'
    ,string_agg((case '||cat||' when '''||arow.col||''' then '||value_field||' else '''' end),'''') '||cat||'_'||arow.col;
    end loop;
  END IF;
    
  v_sql='
        drop table if exists '||table_name||'_wide;
        CREATE TABLE '||table_name||'_wide as 
        '||v_sql||' 
        from '||table_name||' 
        group by '||row_name||';
        drop table if exists temp_table
        ';
  
  execute v_sql;
end;
$$ LANGUAGE plpgsql;

調(diào)用示例：