基于行顺序生成分组列：PostgreSQL表头转分类列方案问询

这个方案完全可行，核心是利用你确认的「分类行的responsibility字段为空」这个特征，结合插入顺序（通过生成行号模拟），用窗口函数向前填充最近的分类值到对应数据行的category列。

具体实现步骤 #

假设你的原表名为imported_data，结构包含need和responsibility两列，以下是示例数据和转换SQL：

1. 模拟原表数据

-- 原表结构
CREATE TABLE imported_data (
    need text,
    responsibility text
);

-- 导入后的示例数据（分类行的responsibility为空）
INSERT INTO imported_data (need, responsibility) VALUES
('项目管理', NULL),
('制定项目计划', '项目经理'),
('跟踪进度', '项目经理'),
('技术开发', NULL),
('编写代码', '开发工程师'),
('单元测试', '开发工程师');

2. 转换SQL

WITH categorized_rows AS (
    SELECT
        need,
        responsibility,
        -- 把分类行的need值标记为候选分类
        CASE WHEN responsibility IS NULL THEN need ELSE NULL END AS category_candidate,
        -- 生成行号模拟插入顺序（如果有明确排序字段，替换成ORDER BY 你的字段）
        row_number() OVER () AS rn
    FROM imported_data
),
category_groups AS (
    SELECT
        need,
        responsibility,
        rn,
        -- 为每行找到之前最近的分类行的行号
        MAX(CASE WHEN category_candidate IS NOT NULL THEN rn END) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS category_rn
    FROM categorized_rows
)
SELECT
    cg.need,
    cg.responsibility,
    cr.category_candidate AS category
FROM category_groups cg
JOIN categorized_rows cr ON cg.category_rn = cr.rn
-- 过滤掉原分类行（如果需要保留，去掉此WHERE条件）
WHERE cg.responsibility IS NOT NULL
ORDER BY cg.rn;

逻辑说明 #

• categorized_rows CTE：给每行生成唯一行号rn，同时标记出分类行的候选分类值。
• category_groups CTE：用MAX() OVER()窗口函数，从当前行向前查找最近的分类行的行号，把这个行号绑定到当前数据行。
• 最终关联查询：通过分类行的行号，把分类值填充到对应的数据行中，同时可选择是否保留原分类行。

注意事项 #

• 如果表中有主键、创建时间这类能明确顺序的字段，把row_number() OVER ()改成row_number() OVER (ORDER BY 你的排序字段)会更可靠，避免因PostgreSQL存储结构变化导致顺序错乱。
• 必须保证导入时分类行在对应数据行之前，这个逻辑才能生效。

内容的提问来源于stack exchange，提问作者Jon Wilson