PostgreSQL 9.5 如何拆分多数组并转置人员-项目-角色关联表
正确实现方案
你当前crosstab用法的核心问题
- 你用的单值crosstab天生不匹配这个场景:这类crosstab的每个分类列只能存储1个独立值,但目标结构需要每列组成对存储项目名+对应角色两个关联值。
- 分类维度选择错误:你原查询的第二个分类参数是取所有项目名当列值,但目标结构的列是按每个人参与项目的顺序排列的(第1个参与的项目对应
project_1/role_1,第2个对应project_2/role_2),和项目名固定列的逻辑完全不匹配。 - 你写的数组拆分逻辑是正确的,只要在拆分后的数据集上补一个参与序号标记,使用支持多值输出的crosstab重载版本即可得到目标结果。
具体可运行的代码逻辑
- 前置准备
首先安装crosstab依赖的扩展:CREATE EXTENSION IF NOT EXISTS tablefunc; - 完整查询语句
SELECT * FROM crosstab( -- 源查询结构要求:第1列为行标识(人员名),第2列为分类值(参与序号),后续列为要转置的实际值(项目名、角色) ' WITH person_project_base AS ( -- 把原表的数组字段、固定leader字段全部拆成统一的纵向记录 SELECT t.person, p.project, t.role FROM tmp_projects p CROSS JOIN LATERAL ( SELECT ''leader''::text AS role, p.leader AS person UNION ALL SELECT ''coordination'', x FROM unnest(p.coordination) AS x UNION ALL SELECT ''staff'', x FROM unnest(p.staff) AS x UNION ALL SELECT ''support'', x FROM unnest(p.support) AS x ) t ), -- 给每个人员的参与记录按顺序打上从1开始的序号 person_with_seq AS ( SELECT person, project, role, ROW_NUMBER() OVER (PARTITION BY person ORDER BY project) AS seq FROM person_project_base ) SELECT person, seq, project, role FROM person_with_seq ORDER BY 1, 2 ', -- 分类维度:按业务中最大的参与项目数生成序号,示例中最多有4个参与经历,所以生成1-4的分类值 'SELECT generate_series(1, 4) AS seq' ) AS ct ( "person" text, "project_1" text, "role_1" text, "project_2" text, "role_2" text, "project_3" text, "role_3" text, "project_4" text, "role_4" text );
关键调整说明
- 当crosstab匹配到序号为1的记录时,会自动把对应的项目值写入
project_1、角色值写入role_1,序号为2的记录自动写入project_2/role_2,完全匹配目标输出的列结构。 - 列数可灵活调整:如果实际业务中最多有人参与5个项目,只需要把分类查询里
generate_series(1,4)的上限改成5,同时在ct的列定义里补一组"project_5" text, "role_5" text即可。 - 没有对应序号的位置会自动返回NULL,和示例中最后两列空白的效果完全一致。
内容的提问来源于stack exchange,提问作者derhelge
相关产品推荐
相关产品推荐

