如何识别支付周期重叠的 duplicated payments

2026-10-24支付阿杰

结论

可以通过自连接比较同一 customer_id 下的两条 payment 记录。判断两个闭区间是否重叠的条件是：

p1.starts_on <= p2.ends_on
AND p2.starts_on <= p1.ends_on

两条记录还必须满足 state <> 'refunded'。连接条件中的 p1.id < p2.id 可以避免记录与自身比较，也不会重复返回同一组记录。

查询重复支付组合

SELECT
    p1.id AS payment_id_1,
    p2.id AS payment_id_2,
    p1.customer_id,
    p1.starts_on AS starts_on_1,
    p1.ends_on AS ends_on_1,
    p2.starts_on AS starts_on_2,
    p2.ends_on AS ends_on_2
FROM payment AS p1
JOIN payment AS p2
    ON p2.customer_id = p1.customer_id
   AND p2.id > p1.id
   AND p1.starts_on <= p2.ends_on
   AND p2.starts_on <= p1.ends_on
WHERE p1.state <> 'refunded'
  AND p2.state <> 'refunded';

查询会返回每一组日期重叠的 payment。例如：

两条记录都是 1 Jan 到 31 Jan，会返回。
一条是 1 Jan 到 31 Jan，另一条是 6 Jan 到 5 Feb，会返回。
两条记录的日期相同，但其中一条已经是 refunded，不会返回。
一条结束于 10 Jan，另一条开始于 10 Jan，由于这里按闭区间处理，也会视为重叠。

换一种说法，只要不是一条记录完全位于另一条记录之前，两个日期范围就有重叠。

查询所有涉及重复支付的记录

如果需要完整的 payment 记录，而不是成对展示，可以使用 EXISTS：

SELECT p.*
FROM payment AS p
WHERE p.state <> 'refunded'
  AND EXISTS (
      SELECT 1
      FROM payment AS other
      WHERE other.customer_id = p.customer_id
        AND other.id <> p.id
        AND other.state <> 'refunded'
        AND p.starts_on <= other.ends_on
        AND other.starts_on <= p.ends_on
  );

这条查询会找出所有参与日期重叠的未退款 payment，而且每条记录只返回一次，适合用来生成待人工核对或退款的清单。

只标记其中一条作为退款候选

判断是否存在重复，与决定应该退掉哪一条，是两个不同的问题。如果业务规则是保留较早创建的记录，将 id 较大的记录作为退款候选，可以这样查询：

SELECT DISTINCT p2.*
FROM payment AS p1
JOIN payment AS p2
    ON p2.customer_id = p1.customer_id
   AND p2.id > p1.id
   AND p1.starts_on <= p2.ends_on
   AND p2.starts_on <= p1.ends_on
WHERE p1.state <> 'refunded'
  AND p2.state <> 'refunded';

但 id 较大不一定意味着该记录确实应该退款。实际处理前，需要先明确保留规则，例如：

保留最早创建或最早成功入账的记录；
保留已经完成后续投资处理的记录；
根据金额、支付渠道或退款关联状态决定；
人工确认链式重叠的情况。

比如 A 与 B 重叠，B 与 C 重叠，但 A 与 C 不重叠。遇到这种情况，简单保留最小的 id 未必符合业务要求。

`period_type` 是否需要参与比较

如果是否重复只取决于实际的 starts_on 和 ends_on，就不需要比较 period_type。Monthly payment 和 Weekly payment 只要覆盖的日期有重叠，也会被查出来。

如果业务规则规定只有相同的 period_type 才算重复，可以在连接条件中加入：

AND p2.period_type = p1.period_type

是否需要这个条件，应根据实际退款规则决定，无法只凭表结构判断。

注意事项

日期字段为 NULL 时，SQL 的范围比较不会成立。如果日期为空属于异常数据，可以单独检查：

SELECT *
FROM payment
WHERE starts_on IS NULL
   OR ends_on IS NULL
   OR starts_on > ends_on;

另外，state <> 'refunded' 不会将 state IS NULL 视为未退款。如果 NULL 状态也需要参与检查，可以改成：

COALESCE(state, '') <> 'refunded'

数据量较大时，可以增加组合索引，改善同一 customer 下的范围匹配：

CREATE INDEX idx_payment_customer_state_dates
    ON payment (customer_id, state, starts_on, ends_on);

不要根据查询结果直接删除记录。更稳妥的做法是先锁定退款候选，完成退款并写入 refund_id，确认退款成功后，再更新状态或删除记录。退款和数据变更最好放在可追踪、可重试的业务流程中。