使用PostgreSQL怎么合并多行并轉(zhuǎn)為列

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)使用PostgreSQL怎么合并多行并轉(zhuǎn)為列，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

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

轉(zhuǎn)化后效果：

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

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

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

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

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

問(wèn)題

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

有這樣的一張表

"Student_score"表：

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

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

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

靜態(tài)寫(xiě)法

當(dāng)我們要轉(zhuǎn)換的值字段是數(shù)值型時(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('張三','語(yǔ)文',80);
INSERT INTO Student_score VALUES('李四','語(yǔ)文',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 '語(yǔ)文' then 分?jǐn)?shù) end) as 語(yǔ)文
from Student_score
GROUP BY 1

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

"Student_grade"表：

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

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

select 姓名

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

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

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

語(yǔ)法：

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, 語(yǔ)文 int);

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

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

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

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

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

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

實(shí)現(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)用示例：

SELECT long_to_wide('Student_grade', '姓名','課程', '等級(jí)')

生成的表名為Student_grade_wide

上述就是小編為大家分享的使用PostgreSQL怎么合并多行并轉(zhuǎn)為列了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。