You need to enable JavaScript to run this app. PostgreSQL 9.5 如何拆分多数组并转置人员-项目-角色关联表
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL 9.5 如何拆分多数组并转置人员-项目-角色关联表

正确实现方案

你当前crosstab用法的核心问题

  • 你用的单值crosstab天生不匹配这个场景:这类crosstab的每个分类列只能存储1个独立值,但目标结构需要每列组成对存储项目名+对应角色两个关联值。
  • 分类维度选择错误:你原查询的第二个分类参数是取所有项目名当列值,但目标结构的列是按每个人参与项目的顺序排列的(第1个参与的项目对应project_1/role_1,第2个对应project_2/role_2),和项目名固定列的逻辑完全不匹配。
  • 你写的数组拆分逻辑是正确的,只要在拆分后的数据集上补一个参与序号标记,使用支持多值输出的crosstab重载版本即可得到目标结果。

具体可运行的代码逻辑

  1. 前置准备
    首先安装crosstab依赖的扩展:
    CREATE EXTENSION IF NOT EXISTS tablefunc;
    
  2. 完整查询语句
    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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 16:54:03