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

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 varchar) returns 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