PostgreSQL 实现将多行合并转为列
需求将下列表格相同id的name拼接起来输出成一列
转化后效果:
实现方式使用array_to_string和array_agg函数,具体语句如下:
string_agg(expression,delimiter)把表达式变成一个数组
string_agg(expression,delimiter)直接把一个表达式变成字符串
selectid,array_to_string(array_agg(Name),';')fromtablegroupbyid
补充:Postgresql实现动态的行转列
问题
在数据处理中,常遇到行转列的问题,比如有如下的问题:
有这样的一张表
"Student_score"表:
姓名 | 课程 | 分数 |
---|---|---|
张三 | 数学 | 83 |
张三 | 物理 | 93 |
张三 | 语文 | 80 |
李四 | 语文 | 74 |
李四 | 数学 | 84 |
李四 | 物理 | 94 |
我们想要得到像这样的一张表:
姓名 | 数学 | 物理 | 语文 |
---|---|---|---|
李四 | 84 | 94 | 74 |
张三 | 83 | 93 | 80 |
当数据量比较少时,我们可以在Excel中使用数据透视表pivottable的功能实现这个需求,但当数据量较大,或者我们还需要在数据库中进行后续的数据处理时,使用数据透视表就显得不那么高效。
下面,介绍如何在Postgresql中实现数据的行转列。
静态写法
当我们要转换的值字段是数值型时,我们可以用SUM()函数:
CREATETABLEStudent_score(姓名varchar,课程varchar,分数int); INSERTINTOStudent_scoreVALUES('张三','数学',83); INSERTINTOStudent_scoreVALUES('张三','物理',93); INSERTINTOStudent_scoreVALUES('张三','语文',80); INSERTINTOStudent_scoreVALUES('李四','语文',74); INSERTINTOStudent_scoreVALUES('李四','数学',84); INSERTINTOStudent_scoreVALUES('李四','物理',94); select姓名 ,sum(case课程when'数学'then分数end)as数学 ,sum(case课程when'物理'then分数end)as物理 ,sum(case课程when'语文'then分数end)as语文 fromStudent_score GROUPBY1
当我们要转换的值字段是字符型时,比如我们的表是这样的:
"Student_grade"表:
姓名 | 数学 | 物理 | 语文 |
---|---|---|---|
张三 | 优 | 良 | 及格 |
李四 | 良 | 优 | 及格 |
我们可以用string_agg()函数:
CREATETABLEStudent_grade(姓名varchar,课程varchar,等级varchar); INSERTINTOStudent_gradeVALUES('张三','数学','优'); INSERTINTOStudent_gradeVALUES('张三','物理','良'); INSERTINTOStudent_gradeVALUES('张三','语文','及格'); INSERTINTOStudent_gradeVALUES('李四','语文','及格'); INSERTINTOStudent_gradeVALUES('李四','数学','良'); INSERTINTOStudent_gradeVALUES('李四','物理','优');
select姓名
,string_agg((case课程when'数学'then等级end),'')as数学 ,string_agg((case课程when'物理'then等级end),'')as物理 ,string_agg((case课程when'语文'then等级end),'')as语文 fromStudent_grade GROUPBY1
内置函数(半动态)
Postgresql内置了tablefunc可实现pivottable的功能。
语法:
SELECT* FROMcrosstab( 'selectrow_name,cat,value fromtable orderby1,2') AS(row_nametype,category_1type,category_2type,category_3type,...);
例如:
SELECT* FROMcrosstab( 'select姓名,课程,分数 fromStudent_score orderby1,2') AS(姓名varchar,数学int,物理int,语文int);
需要注意的是crosstab(textsql)中的sql语句必须按顺序返回row_name,category,value,并且必须声明输出的各列的列名和数据类型。当原表中的cat列有很多不同的值,那我们将会得到一个有很多列的表,并且我们需要手动声明每个列的列名及数据类型,显然这种体验非常不友好。那有没有更好的方式呢,我们可以通过手动建立存储过程(函数)实现。
自建函数(动态)
动态的行转列我们通过plpgsql实现,大致的思路如下:
判断value字段的数据类型,如果是数值型,则转入2.,否则转入3.
对cat列中的每个distinct值使用sum(casewhen),转成列
对cat列中的每个distinct值使用string_agg(casewhen),转成列
实现代码示例:
CREATEorREPLACEFUNCTION long_to_wide( table_nameVARCHAR, row_nameVARCHAR, catVARCHAR, value_fieldVARCHAR) returnsvoidas $$ /* table_name:表名 row_name:行名字段 cat:转换为列名的字段 value_field:转换为值的字段 */ DECLAREv_sqltext; arowrecord; value_typeVARCHAR; BEGIN v_sql=' droptableifexiststemp_table; CREATETABLEtemp_tableas SELECTdistinct'||cat||'ascolfrom'||table_name||' orderby'||cat; executev_sql; v_sql=' SELECTt.typnameAStype FROMpg_classc ,pg_attributea ,pg_typet WHEREc.relname=lower('''||table_name||''') anda.attnum>0 anda.attrelid=c.oid anda.atttypid=t.oid anda.attname='''||value_field||''' ORDERBYa.attnum '; executev_sqlintovalue_type;--获取值字段的数据类型 v_sql='select'||row_name; IFvalue_typein('numeric','int8','int4','int')--判断值字段是否是数值型 THEN FORarowin(SELECTcolFROMtemp_table)loop v_sql=v_sql||' ,sum(case'||cat||'when'''||arow.col||'''then'||value_field||'else0end)'||cat||'_'||arow.col; endloop; ELSE FORarowin(SELECTcolFROMtemp_table)loop v_sql=v_sql||' ,string_agg((case'||cat||'when'''||arow.col||'''then'||value_field||'else''''end),'''')'||cat||'_'||arow.col; endloop; ENDIF; v_sql=' droptableifexists'||table_name||'_wide; CREATETABLE'||table_name||'_wideas '||v_sql||' from'||table_name||' groupby'||row_name||'; droptableifexiststemp_table '; executev_sql; end; $$LANGUAGEplpgsql;
调用示例:
SELECTlong_to_wide('Student_grade','姓名','课程','等级')
生成的表名为Student_grade_wide
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。