PAYATHON 2026

Postgres/SQL：按 payment source 计算 charges 与 refunds

支付小周

结论

先按 charge_id 汇总退款，再把每笔 charge 的净使用金额按 credit_id 汇总。这样，每个 charges.amount 只会参与一次计算，不会因关联多条 refunds 而被重复累计。

WITH refund_totals AS (
    SELECT
        charge_id,
        SUM(amount) AS refunded_amount
    FROM refunds
    GROUP BY charge_id
),
credit_usage AS (
    SELECT
        ch.credit_id,
        SUM(ch.amount - COALESCE(rt.refunded_amount, 0)) AS used_amount
    FROM charges AS ch
    LEFT JOIN refund_totals AS rt
        ON rt.charge_id = ch.id
    GROUP BY ch.credit_id
)
SELECT
    c.*,
    c.amount - COALESCE(cu.used_amount, 0) AS unspent_amount
FROM credits AS c
LEFT JOIN credit_usage AS cu
    ON cu.credit_id = c.id
WHERE c.amount > COALESCE(cu.used_amount, 0)
LIMIT 1;

原查询为什么会重复计算

原查询直接连接了三张表：

credits → charges → refunds

如果一笔 charge 对应两条 refund，连接后的中间结果会出现两行：

charge.amount | refund.amount
--------------+--------------
25.0          | 20.0
25.0          | 5.0

这时执行：

SUM(charges.amount)

结果是 50.0，而不是 25.0。问题不在 SUM() 本身，而是一对多 JOIN 在聚合前复制了 charge 行。

查询如何计算

第一步是按 charge_id 汇总退款：

SELECT
    charge_id,
    SUM(amount) AS refunded_amount
FROM refunds
GROUP BY charge_id;

示例数据的结果是：

charge_id | refunded_amount
----------+----------------
1         | 25.0

第二步计算每笔 charge 实际占用的 credit：

net charge = charge amount - refunded amount

再按 credit_id 汇总：

SUM(ch.amount - COALESCE(rt.refunded_amount, 0))

最后计算剩余额度：

unspent amount = credit amount - used amount

对于示例数据：

used_amount    = 25.0 - (20.0 + 5.0) = 0.0
unspent_amount = 25.0 - 0.0          = 25.0

最终结果为：

id | amount | unspent_amount
---+--------+---------------
1  | 25.0   | 25.0

不要使用 SUM(DISTINCT charges.amount)

一种看似可行的写法是：

SUM(DISTINCT charges.amount)

但这种写法并不正确。DISTINCT 去重的是 amount 值，而不是 charge ID。假设同一个 credit 有两笔不同的 charge，金额刚好都是 10.0，SUM(DISTINCT ...) 只会计算一次，结果会从正确的 20.0 变成 10.0。

正确的做法是先把一对多关系聚合到每个 charge_id 一行，再进行上层汇总。

注意事项

  • LEFT JOIN 会保留完全没有 charges 的 credit，此时 COALESCE(..., 0) 会把 used_amount 处理为零。
  • 没有 refunds 的 charge 也能正常计算，其退款金额按零处理。
  • 如果退款总额可以超过 charge 金额，used_amount 可能为负数，导致 unspent_amount 大于 credits.amount。是否要限制这种情况，应由业务规则决定。
  • LIMIT 1 没有搭配 ORDER BY 时，PostgreSQL 不保证返回哪一条符合条件的 credit。如果需要固定选择顺序，应明确指定，例如：
ORDER BY c.id
LIMIT 1;

备注：内容仅供参考。