• 售前

  • 售后

热门帖子
入门百科

PostgreSQL 实现将多行归并转为列

[复制链接]
加菲猫419 显示全部楼层 发表于 2021-10-26 13:16:53 |阅读模式 打印 上一主题 下一主题
需求将下列表格雷同id的name拼接起来输出成一列
idName
1peter
1lily
2john
转化后效果:
idName
1peter;lily
2john;
实现方式利用 array_to_string 和 array_agg 函数,具体语句如下:
string_agg(expression, delimiter) 把表达式酿成一个数组
string_agg(expression, delimiter) 直接把一个表达式酿成字符串
  1. 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()函数:
  1. CREATE TABLE Student_score(姓名 varchar, 课程 varchar, 分数 int);
  2. INSERT INTO Student_score VALUES('张三','数学',83);
  3. INSERT INTO Student_score VALUES('张三','物理',93);
  4. INSERT INTO Student_score VALUES('张三','语文',80);
  5. INSERT INTO Student_score VALUES('李四','语文',74);
  6. INSERT INTO Student_score VALUES('李四','数学',84);
  7. INSERT INTO Student_score VALUES('李四','物理',94);
  8. select 姓名
  9.   ,sum(case 课程 when '数学' then 分数 end) as 数学
  10.   ,sum(case 课程 when '物理' then 分数 end) as 物理
  11.   ,sum(case 课程 when '语文' then 分数 end) as 语文
  12. from Student_score
  13. GROUP BY 1
复制代码
当我们要转换的值字段是字符型时,比如我们的表是如许的:
"Student_grade"表:
      姓名      数学      物理      语文                  张三      优      良      合格              李四      良      优      合格    我们可以用string_agg()函数:
  1. CREATE TABLE Student_grade(姓名 varchar, 课程 varchar, 等级 varchar);
  2. INSERT INTO Student_grade VALUES('张三','数学','优');
  3. INSERT INTO Student_grade VALUES('张三','物理','良');
  4. INSERT INTO Student_grade VALUES('张三','语文','及格');
  5. INSERT INTO Student_grade VALUES('李四','语文','及格');
  6. INSERT INTO Student_grade VALUES('李四','数学','良');
  7. INSERT INTO Student_grade VALUES('李四','物理','优');
复制代码
select 姓名
  1.   ,string_agg((case 课程 when '数学' then 等级 end),'') as 数学
  2.   ,string_agg((case 课程 when '物理' then 等级 end),'') as 物理
  3.   ,string_agg((case 课程 when '语文' then 等级 end),'') as 语文
  4. from Student_grade
  5. GROUP BY 1
复制代码
内置函数(半动态)

Postgresql内置了tablefunc可实现pivot table的功能。
语法:
  1. SELECT *
  2. FROM crosstab(
  3. 'select row_name,cat,value
  4.   from table
  5.   order by 1,2')
  6. AS (row_name type, category_1 type, category_2 type, category_3 type, ...);
复制代码
比方:
  1. SELECT *
  2. FROM crosstab(
  3. 'select 姓名,课程,分数
  4.   from Student_score
  5.   order by 1,2')
  6. 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),转成列
实当代码示例:
  1. CREATE or REPLACE FUNCTION
  2. long_to_wide(
  3. table_name VARCHAR,
  4. row_name VARCHAR,
  5. cat VARCHAR,
  6. value_field VARCHAR)
  7. returns void as
  8. $$
  9. /*
  10. table_name : 表名
  11. row_name : 行名字段
  12. cat : 转换为列名的字段
  13. value_field : 转换为值的字段
  14. */
  15. DECLARE v_sql text;
  16. arow record;
  17. value_type VARCHAR;
  18. BEGIN
  19.   
  20.   v_sql='
  21.   drop table if exists temp_table;
  22.   CREATE TABLE temp_table as
  23.   SELECT distinct '||cat||' as col from '||table_name||'
  24.   order by '||cat;
  25.   execute v_sql;
  26.   v_sql='
  27.   SELECT t.typname AS type
  28.   FROM pg_class c
  29.   ,pg_attribute a
  30.   ,pg_type t
  31.   WHERE c.relname = lower('''||table_name||''')
  32.   and a.attnum > 0
  33.   and a.attrelid = c.oid
  34.   and a.atttypid = t.oid
  35.   and a.attname='''||value_field||'''
  36.   ORDER BY a.attnum
  37.   ';
  38.   execute v_sql into value_type;--获取值字段的数据类型
  39.   
  40.   v_sql='select '||row_name;
  41.   IF value_type in ('numeric','int8','int4','int')--判断值字段是否是数值型
  42.     THEN  
  43.     FOR arow in (SELECT col FROM temp_table) loop
  44.    
  45.     v_sql=v_sql||'
  46.         ,sum(case '||cat||' when '''||arow.col||''' then '||value_field||' else 0 end) '||cat||'_'||arow.col;
  47.     end loop;
  48.   ELSE
  49.     FOR arow in (SELECT col FROM temp_table) loop
  50.    
  51.     v_sql=v_sql||'
  52.     ,string_agg((case '||cat||' when '''||arow.col||''' then '||value_field||' else '''' end),'''') '||cat||'_'||arow.col;
  53.     end loop;
  54.   END IF;
  55.    
  56.   v_sql='
  57.         drop table if exists '||table_name||'_wide;
  58.         CREATE TABLE '||table_name||'_wide as
  59.         '||v_sql||'
  60.         from '||table_name||'
  61.         group by '||row_name||';
  62.         drop table if exists temp_table
  63.         ';
  64.   
  65.   execute v_sql;
  66. end;
  67. $$ LANGUAGE plpgsql;
复制代码
调用示例:
  1. SELECT long_to_wide('Student_grade', '姓名','课程', '等级')
复制代码
天生的表名为Student_grade_wide
以上为个人履历,希望能给各人一个参考,也希望各人多多支持脚本之家。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作