PostgreSQL中如何拆分行并基于拆分列合并行？

问题描述 #

原始表结构及数据：

期望得到的结果：

尝试过unnest(string_to_array(ID,','))拆分ID，结合concat_ws(',', "A's price", "B's price", "C's price", "D's price")拼接价格，但无法得到预期输出，询问是否可在PostgreSQL中实现，或是否需要改用pandas。

解决方案（PostgreSQL实现） #

完全可以在PostgreSQL中实现，无需借助pandas，核心思路是拆分ID后匹配对应价格列，再聚合有效价格，具体步骤如下：

1. 拆分ID列：用unnest(string_to_array(ID, ','))把多值ID拆成单行，同时保留原表的所有价格列。
2. 匹配对应价格：根据拆分后的单个ID，提取对应列的价格值（比如ID为A时取"A's price"，ID为B时取"B's price"）。
3. 过滤空值并聚合：按拆分后的ID分组，把非空的价格用逗号连接起来。

具体SQL代码 #

WITH split_ids AS (
    -- 拆分ID，保留所有价格列
    SELECT 
        unnest(string_to_array(ID, ',')) AS single_id,
        "A's price", "B's price", "C's price", "D's price"
    FROM your_table_name
),
matched_prices AS (
    -- 根据单个ID匹配对应的价格值
    SELECT 
        single_id,
        CASE single_id
            WHEN 'A' THEN "A's price"
            WHEN 'B' THEN "B's price"
            WHEN 'C' THEN "C's price"
            WHEN 'D' THEN "D's price"
        END AS price_val
    FROM split_ids
)
-- 分组聚合非空价格，用逗号连接
SELECT 
    single_id AS ID,
    string_agg(price_val::TEXT, ',' ORDER BY price_val) AS price
FROM matched_prices
WHERE price_val IS NOT NULL
GROUP BY single_id
ORDER BY single_id;

代码解释 #

• split_ids CTE：把原表的多值ID拆分成单行，每一行对应一个单个ID和原表的所有价格数据。
• matched_prices CTE：通过CASE语句，根据单个ID匹配对应的价格列，得到每个ID对应的价格值。
• 最后一步：过滤掉空的价格值，按单个ID分组，用string_agg聚合价格，并用ORDER BY保证价格顺序（可选）。

替代方案（用JSONB简化映射） #

如果价格列较多，用CASE写起来繁琐，可以借助JSONB来动态匹配：

WITH split_ids AS (
    SELECT 
        unnest(string_to_array(ID, ',')) AS single_id,
        to_jsonb(t) AS price_json
    FROM your_table_name t
)
SELECT 
    single_id AS ID,
    string_agg((price_json ->> (single_id || '''s price'))::TEXT, ',' ORDER BY (price_json ->> (single_id || '''s price'))) AS price
FROM split_ids
WHERE (price_json ->> (single_id || '''s price')) IS NOT NULL
GROUP BY single_id
ORDER BY single_id;

这个方法通过把整行转成JSONB，然后根据单个ID拼接价格列名（比如A拼接成"A's price"），直接提取对应的值，适合列较多的场景。

总结 #

上述两种方法都能在PostgreSQL中完成需求，无需切换到pandas。如果数据量极大，PostgreSQL的性能通常也会优于pandas处理，推荐优先用SQL实现。

内容的提问来源于stack exchange，提问作者M Shen