查看原文
其他

PostgreSQL实现动态行转列的方法汇总

Alitrack alitrack 2024-03-28


PostgreSQL 提供了一个 tablefunc 模块,内置了多个函数,其中就有 crosstab(交叉表,又叫行转列,或者长表转宽表),具体使用参见 PostgreSQL 文档(中文[1]英文[2])。

如果不清楚生成的宽表有多少列,或者列太多,手工敲很容易吃力不讨好,那么可以借助这个函数(pivotcode)来简化工作,

-- PL/pgSQL code to create pivot tables with automatic column names
-- Eric Minikel, CureFFI.org - 2013-03-19

-- prerequisite: 安装插件tablefunc
create extension tablefunc;

-tablename:要透视的源表的名称
-rowc:要成为行的源表中的列的名称
-colc:想成为列的源表中的列的名称
-cellc:确定将如何创建单元格值的聚合表达式
-celldatatype:单元格所需的数据类型

create or replace function pivotcode (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varcharreturns varchar language plpgsql as $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'select * from crosstab (
 ''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
 ''select distinct '||colc||' from '||tablename||' order by 1''
 )
 as newtable (
 '||rowc||' varchar,'||columnlist||'
 );';
    return dynsql2;
end
$$

使用示例,

-- toy example to show how it works
create table table_to_pivot (
   rowname varchar,
   colname varchar,
   cellval numeric
);
insert into table_to_pivot values ('row1','col1',11);
insert into table_to_pivot values ('row1','col2',12);
insert into table_to_pivot values ('row1','col3',13);
insert into table_to_pivot values ('row2','col1',21);
insert into table_to_pivot values ('row2','col2',22);
insert into table_to_pivot values ('row2','col3',23);
insert into table_to_pivot values ('row3','col1',31);
insert into table_to_pivot values ('row3','col2',32);
insert into table_to_pivot values ('row3','col3',33);
select pivotcode('table_to_pivot','rowname','colname','max(cellval)','integer');

执行 pivotcode 生成的 SQL

select * from crosstab (
    'select rowname,colname,max(cellval) from table_to_pivot group by 1,2 order by 1,2',
    'select distinct colname from table_to_pivot order by 1'
    )
    as newtable (
    rowname varchar,_col1 integer,_col2 integer,_col3 integer
    );

得到你想要的透视表

但这个函数,有如下的缺点,

  • 如果返回的列有空格,需要自己手工修改
  • 可能会有大量的 Null 值,而不是 0
  • 返回的是一个 SQL,您需要复制出来后自己再执行(虽然也可以改进下,直接生成表)

另外几个方案也可以考虑下,

  • Apache MADlib 的Pivot 函数[3]
  • 基于 PL/Python 的实现:pivotmytable[4]
  • 自己使用 PL/R 封装
  • psql command:\crosstabview[5]
  • 另外一个基于 crosstab 的实现,dynamic_pivot[6]

参考资料

[1]

中文: http://www.postgres.cn/docs/12/tablefunc.html

[2]

英文: https://www.postgresql.org/docs/current/tablefunc.html

[3]

Pivot函数: https://madlib.apache.org/docs/latest/group__grp__pivot.html

[4]

pivotmytable: https://github.com/jtornero/pivotmytable

[5]

\crosstabview: https://wiki.postgresql.org/wiki/Crosstabview

[6]

dynamic_pivot: https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html




继续滑动看下一个
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存