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)用示例:
SELECT long_to_wide('Student_grade', '姓名','課程', '等級')
生成的表名為Student_grade_wide
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。
相關(guān)文章
PostgreSQL數(shù)據(jù)庫中如何保證LIKE語句的效率(推薦)
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫中如何保證LIKE語句的效率,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-03-03
postgreSQL數(shù)據(jù)庫默認(rèn)用戶postgres常用命令分享
這篇文章主要介紹了postgreSQL數(shù)據(jù)庫默認(rèn)用戶postgres常用命令分享,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL數(shù)據(jù)類型格式化函數(shù)操作
這篇文章主要介紹了PostgreSQL數(shù)據(jù)類型格式化函數(shù)操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
淺談PostgreSQL中的孤兒文件用法(orphaned data files)
這篇文章主要介紹了淺談PostgreSQL中的孤兒文件用法(orphaned data files),具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql psql文件執(zhí)行與批處理多個sql文件操作
這篇文章主要介紹了Postgresql psql文件執(zhí)行與批處理多個sql文件操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgreSQL自動生成隨機(jī)數(shù)值的實例
這篇文章主要介紹了postgreSQL自動生成隨機(jī)數(shù)值的實例,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 兩表關(guān)聯(lián)更新操作
這篇文章主要介紹了postgresql 兩表關(guān)聯(lián)更新操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL使用SQL實現(xiàn)俄羅斯方塊的示例
基于PostgreSQL實現(xiàn)的俄羅斯方塊游戲項目Tetris-SQL,通過純SQL代碼和數(shù)據(jù)庫操作重構(gòu)了經(jīng)典游戲邏輯,展現(xiàn)了SQL語言的圖靈完備性和技術(shù)潛力,本文介紹PostgreSQL使用SQL實現(xiàn)俄羅斯方塊的示例,感興趣的朋友一起看看吧2022-04-04

