PostgreSQL 實現將多行合並轉為列

Posted on by

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

id Name
1 peter
1 lily
2 john

轉化後效果：

id Name
1 peter；lily
2 john；

實現方式使用 array_to_string 和 array_agg 函數，具體語句如下：

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

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

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

補充：Postgresql實現動態的行轉列

問題

在數據處理中，常遇到行轉列的問題，比如有如下的問題：

有這樣的一張表

“Student_score”表：

姓名 課程 分數
張三 數學 83
張三 物理 93
張三 語文 80
李四 語文 74
李四 數學 84
李四 物理 94

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

姓名 數學 物理 語文
李四 84 94 74
張三 83 93 80

當數據量比較少時，我們可以在Excel中使用數據透視表pivot table的功能實現這個需求，但當數據量較大，或者我們還需要在數據庫中進行後續的數據處理時，使用數據透視表就顯得不那麼高效。

下面，介紹如何在Postgresql中實現數據的行轉列。

靜態寫法

當我們要轉換的值字段是數值型時，我們可以用SUM()函數：

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

當我們要轉換的值字段是字符型時，比如我們的表是這樣的：

“Student_grade”表：

姓名 數學 物理 語文
張三 優 良 及格
李四 良 優 及格

我們可以用string_agg()函數：

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

select 姓名

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

內置函數（半動態）

Postgresql內置瞭tablefunc可實現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 姓名,課程,分數
  from Student_score
  order by 1,2')
AS (姓名 varchar, 數學 int, 物理 int, 語文 int);

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

自建函數（動態）

動態的行轉列我們通過plpgsql實現，大致的思路如下：

判斷value字段的數據類型，如果是數值型，則轉入2.，否則轉入3.

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

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

實現代碼示例：

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 : 轉換為列名的字段
value_field : 轉換為值的字段
*/
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;--獲取值字段的數據類型
  
  v_sql='select '||row_name;
  IF value_type in ('numeric','int8','int4','int')--判斷值字段是否是數值型 
    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;

調用示例：

SELECT long_to_wide(‘Student_grade’, ‘姓名’,’課程’, ‘等級’)

生成的表名為Student_grade_wide

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：