如何修复分组行求和查询并结合首段查询关联用户数据？

1. 修复字符串拆分求和查询 #

原查询存在语法错误（括号不匹配、unnest调用格式错误），修复后的代码如下：

SELECT t.columna1, SUM(v.val::float) AS suma
FROM t
CROSS JOIN LATERAL unnest(string_to_array(t.columna2, ',')) AS v(val)
GROUP BY t.columna1;

关键说明： #

• string_to_array(t.columna2, ',') 将columna2的字符串按逗号拆分为数组
• unnest() 把数组展开为多行记录
• 通过CROSS JOIN LATERAL关联原表与展开后的记录，最终按columna1分组求和

2. 订单支付表关联查询优化 #

原查询中的DISTINCT属于冗余写法（GROUP BY已保证结果唯一性），优化后的基础关联查询：

-- 按订单和用户分组，获取订单的已支付金额
SELECT 
    pos_payment.pos_order_id,
    pos_order.user_id,
    pos_order.amount_paid
FROM pos_order
LEFT JOIN pos_payment ON pos_order.id = pos_payment.pos_order_id
GROUP BY pos_payment.pos_order_id, pos_order.user_id, pos_order.amount_paid;

如果需要统计每个订单的实际支付总和（基于pos_payment表的支付记录），可以调整为：

SELECT 
    pos_payment.pos_order_id,
    pos_order.user_id,
    pos_order.amount_paid,
    SUM(pos_payment.amount) AS total_actual_payments
FROM pos_order
LEFT JOIN pos_payment ON pos_order.id = pos_payment.pos_order_id
GROUP BY pos_payment.pos_order_id, pos_order.user_id, pos_order.amount_paid;

3. 结合两个查询，按user_id关联数据 #

假设t表的columna1对应pos_order表的user_id，通过子查询关联两个查询的结果，获取用户的拆分求和数据与订单支付信息：

SELECT 
    user_data.user_id,
    user_data.suma,
    order_pay.pos_order_id,
    order_pay.amount_paid,
    order_pay.total_actual_payments
FROM (
    -- 字符串拆分求和的用户数据
    SELECT columna1 AS user_id, SUM(v.val::float) AS suma
    FROM t
    CROSS JOIN LATERAL unnest(string_to_array(columna2, ',')) AS v(val)
    GROUP BY columna1
) user_data
LEFT JOIN (
    -- 订单支付关联数据
    SELECT 
        po.user_id,
        pp.pos_order_id,
        po.amount_paid,
        SUM(pp.amount) AS total_actual_payments
    FROM pos_order po
    LEFT JOIN pos_payment pp ON po.id = pp.pos_order_id
    GROUP BY po.user_id, pp.pos_order_id, po.amount_paid
) order_pay ON user_data.user_id = order_pay.user_id;

注意事项： #

• 如果t表与pos_order表的关联字段不是columna1 = user_id，需调整LEFT JOIN的关联条件为实际业务字段
• 若不需要统计实际支付总和，可移除SUM(pp.amount)及对应的分组字段

内容的提问来源于stack exchange，提问作者A izag alba