如何在PostgreSQL中拆分合并的数据行？拆分尝试失败求助

问题描述 #

执行以下PostgreSQL SQL语句后，结果出现合并的数据行，尝试拆分未成功：

SELECT
        t1.pay_id,
        t1.order_no,
        t1.payer_member_id agent_id,
        t1.business_amount,
        t1.pay_status,
        t2.goods_name,
        t2.quantity,
        t2.array_skus,
        dateadd ( HOUR, 1, t1.update_time ) update_time,
        t5.order_no refund_order_no,
        t5.status refund_status,
        t5.refund_count,
        t5.refund_type,
        dateadd ( HOUR, 1, t5.create_time ) refund_create_time,
        dateadd ( HOUR, 1, t5.update_time ) AS refund_update_time
    from report_mongo.ng_t_pay_flow_dms t1 
    left join report_mongo.ng_t_merchant_order_goods t2 on t1.order_no =t2.order_no 
    LEFT JOIN  report_mongo.ng_t_refund_order T5 on t2.order_no=t5.pre_order_no
    WHERE t1.trans_type='u0' 

执行结果存在合并行（如图：），拆分尝试失败（如图：）。

解决方案 #

核心分析 #

合并行通常源于两个原因：一是array_skus为数组类型，多个元素被打包在同一行；二是多表一对多关联产生笛卡尔积，导致同一基础记录对应多条关联数据。以下是针对性解决方法：

1. 拆分数组字段 #

如果array_skus是PostgreSQL原生数组类型，直接用unnest函数将数组元素拆分为单行：

SELECT
    t1.pay_id,
    t1.order_no,
    t1.payer_member_id AS agent_id,
    t1.business_amount,
    t1.pay_status,
    t2.goods_name,
    t2.quantity,
    unnest(t2.array_skus) AS sku, -- 拆分数组为单行
    dateadd(HOUR, 1, t1.update_time) AS update_time,
    t5.order_no AS refund_order_no,
    t5.status AS refund_status,
    t5.refund_count,
    t5.refund_type,
    dateadd(HOUR, 1, t5.create_time) AS refund_create_time,
    dateadd(HOUR, 1, t5.update_time) AS refund_update_time
FROM report_mongo.ng_t_pay_flow_dms t1 
LEFT JOIN report_mongo.ng_t_merchant_order_goods t2 
    ON t1.order_no = t2.order_no 
LEFT JOIN report_mongo.ng_t_refund_order t5 
    ON t2.order_no = t5.pre_order_no
WHERE t1.trans_type = 'u0'

如果array_skus是逗号分隔的字符串（非原生数组），先转成数组再拆分：

unnest(string_to_array(t2.array_skus, ',')) AS sku

2. 处理关联产生的重复行 #

如果是多表关联导致的笛卡尔积重复，可通过窗口函数筛选唯一行：

WITH ranked_data AS (
    SELECT
        t1.pay_id,
        t1.order_no,
        t1.payer_member_id AS agent_id,
        t1.business_amount,
        t1.pay_status,
        t2.goods_name,
        t2.quantity,
        unnest(t2.array_skus) AS sku,
        dateadd(HOUR, 1, t1.update_time) AS update_time,
        t5.order_no AS refund_order_no,
        t5.status AS refund_status,
        t5.refund_count,
        t5.refund_type,
        dateadd(HOUR, 1, t5.create_time) AS refund_create_time,
        dateadd(HOUR, 1, t5.update_time) AS refund_update_time,
        -- 按支付ID、商品名、SKU分组，取最新的退款记录
        ROW_NUMBER() OVER (PARTITION BY t1.pay_id, t2.goods_name, sku ORDER BY t5.update_time DESC) AS rn
    FROM report_mongo.ng_t_pay_flow_dms t1 
    LEFT JOIN report_mongo.ng_t_merchant_order_goods t2 
        ON t1.order_no = t2.order_no 
    LEFT JOIN report_mongo.ng_t_refund_order t5 
        ON t2.order_no = t5.pre_order_no
    WHERE t1.trans_type = 'u0'
)
SELECT *
FROM ranked_data
WHERE rn = 1;

内容的提问来源于stack exchange，提问作者lin youcehng