PostgreSQL：查询没有匹配退款的最后一笔交易

结论

如果退款总是按时间匹配最早一笔尚未退款的购买，可以分别按时间为购买和退款编号，再用编号配对。没有对应退款编号的购买就是未退款记录，最后按购买时间倒序取第一条即可。

WITH purchases AS (
    SELECT
        t.*,
        row_number() OVER (
            PARTITION BY customer, product_id
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions AS t
    WHERE amount > 0
),
refunds AS (
    SELECT
        customer,
        product_id,
        row_number() OVER (
            PARTITION BY customer, product_id
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions
    WHERE amount < 0
)
SELECT p.*
FROM purchases AS p
LEFT JOIN refunds AS r
    ON r.customer = p.customer
   AND r.product_id = p.product_id
   AND r.match_no = p.match_no
WHERE r.match_no IS NULL
ORDER BY p.date_purchased DESC, p.id DESC
LIMIT 1;

在示例数据中：

• 购买记录按顺序编号为 1、2、3，对应 id = 1、3、4
• 退款记录按顺序编号为 1、2，对应 id = 2、5
• 前两笔购买分别与两笔退款匹配
• 第三笔购买没有对应退款，所以查询返回 id = 4

为什么这样匹配

FIFO 退款规则可以直接转换成顺序编号：

编号相同的购买和退款组成一对。经过 LEFT JOIN 后，退款一侧为空的购买就是未退款记录。

由于 date_purchased 可能重复，排序时还需要加入唯一的 id，确保匹配顺序稳定。

查询指定客户和商品

如果只查询 Jack 对商品 10 的最后一笔未退款购买，可以提前限定数据范围：

WITH purchases AS (
    SELECT
        t.*,
        row_number() OVER (
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions AS t
    WHERE customer = 'jack'
      AND product_id = 10
      AND amount > 0
),
refunds AS (
    SELECT
        row_number() OVER (
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions
    WHERE customer = 'jack'
      AND product_id = 10
      AND amount < 0
)
SELECT p.*
FROM purchases AS p
LEFT JOIN refunds AS r USING (match_no)
WHERE r.match_no IS NULL
ORDER BY p.date_purchased DESC, p.id DESC
LIMIT 1;

查询每个客户、每种商品的最后一笔未退款购买

如果要分别返回每个 (customer, product_id) 组合的结果，可以使用 PostgreSQL 的 DISTINCT ON：

WITH purchases AS (
    SELECT
        t.*,
        row_number() OVER (
            PARTITION BY customer, product_id
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions AS t
    WHERE amount > 0
),
refunds AS (
    SELECT
        customer,
        product_id,
        row_number() OVER (
            PARTITION BY customer, product_id
            ORDER BY date_purchased, id
        ) AS match_no
    FROM transactions
    WHERE amount < 0
),
unrefunded AS (
    SELECT p.*
    FROM purchases AS p
    LEFT JOIN refunds AS r
        ON r.customer = p.customer
       AND r.product_id = p.product_id
       AND r.match_no = p.match_no
    WHERE r.match_no IS NULL
)
SELECT DISTINCT ON (customer, product_id) *
FROM unrefunded
ORDER BY
    customer,
    product_id,
    date_purchased DESC,
    id DESC;

注意事项

这个方案要求业务数据满足以下条件：

• 每笔退款完整对应一笔购买，不涉及部分退款。
• 退款严格匹配同一客户、同一商品下最早的未退款购买。
• 数据中不存在退款早于对应购买等异常情况。
• amount = 0 既不属于购买，也不属于退款。
• 如果退款还要按币种、门店、订单或金额匹配，需要把相关字段加入 PARTITION BY 和连接条件。

如果支持部分退款，或者一笔退款可以覆盖多笔购买，顺序编号就无法准确处理。这种情况需要根据累计金额做区间匹配，或者直接在表中保存明确的 original_transaction_id。从数据建模的角度看，让退款记录直接引用原购买记录，通常比每次查询时重新推导对应关系更可靠。

备注：内容仅供参考。