嘿，我来帮你搞定这个重复支付记录的识别问题！根据你给出的规则，咱们可以用SQL精准定位这些需要处理的重复记录。

首先先明确咱们的核心判定规则：同一客户下，只要存在另一条未退款（state != 'refunded'）的支付记录，两者的支付周期有重叠（完全相同、部分交叉、互相包含都算），这条记录就属于重复支付。

核心需求回顾 #

咱们的payment表结构：

id int(11)
customer_id int(11)
period_type varchar(255)  -- 枚举值：Monthly/FourWeekly/Weekly/Annually/Fortnightly/BiAnnually/Quarterly
state varchar(255)
starts_on date
ends_on date
refund_id int(11)

实现SQL查询 #

下面这个自连接查询可以直接找出所有符合条件的重复支付记录，同时列出对应的重叠记录：

SELECT 
    p1.id AS duplicate_payment_id,
    p1.customer_id,
    p1.period_type,
    p1.state,
    p1.starts_on AS duplicate_starts,
    p1.ends_on AS duplicate_ends,
    p2.id AS overlapping_payment_id,
    p2.starts_on AS overlapping_starts,
    p2.ends_on AS overlapping_ends
FROM 
    payment p1
JOIN 
    payment p2 
    ON p1.customer_id = p2.customer_id
    -- 排除自身匹配
    AND p1.id != p2.id
WHERE 
    -- 只考虑未退款的记录
    p1.state != 'refunded'
    AND p2.state != 'refunded'
    -- 关键：判断两个时间区间是否重叠
    AND p1.starts_on <= p2.ends_on
    AND p1.ends_on >= p2.starts_on
ORDER BY 
    p1.customer_id, p1.starts_on;

查询逻辑解释 #

1. 自连接筛选同一客户：通过p1.customer_id = p2.customer_id把同一个客户的支付记录配对。
2. 排除自身：用p1.id != p2.id避免记录和自己匹配。
3. 过滤未退款记录：state != 'refunded'确保只参与有效支付的判定，退款记录不干扰结果。
4. 时间重叠判定：p1.starts_on <= p2.ends_on AND p1.ends_on >= p2.starts_on是标准的时间区间重叠判断，能覆盖所有你提到的场景：
   • 完全相同的周期（比如两条1 Jan-31 Jan的记录）
   • 部分交叉的周期（比如1 Jan-31 Jan和6 Jan-5 Feb）
   • 一个周期完全包含另一个的情况

扩展：标记需删除的重复记录 #

如果需要明确标记哪些记录可以删除（比如保留每个重叠组中最早创建的记录，假设id是自增主键），可以用窗口函数来实现：

WITH ranked_payments AS (
    SELECT 
        p.*,
        -- 按客户分组，按起始日期排序，给每条记录排名
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY starts_on, id
        ) AS payment_rank
    FROM payment p
    WHERE p.state != 'refunded'
),
duplicate_flags AS (
    SELECT 
        rp.*,
        -- 判断当前记录是否存在同客户的其他重叠记录
        CASE WHEN EXISTS (
            SELECT 1 FROM ranked_payments rp2
            WHERE rp2.customer_id = rp.customer_id
            AND rp2.id != rp.id
            AND rp2.starts_on <= rp.ends_on
            AND rp2.ends_on >= rp.starts_on
        ) THEN 1 ELSE 0 END AS is_duplicate
    FROM ranked_payments rp
)
-- 保留排名1的记录，标记其他重复记录为待删除
SELECT 
    id,
    customer_id,
    period_type,
    state,
    starts_on,
    ends_on,
    CASE WHEN is_duplicate = 1 AND payment_rank > 1 THEN '待删除' ELSE '保留' END AS handle_status
FROM duplicate_flags;

示例验证 #

咱们用你给出的三个例子测试：

• 客户A：两条1 Jan-31 Jan的invested记录 → 都会被查询出来，互相匹配为重复。
• 客户B：一条refunded一条invested → refunded记录被过滤，invested记录找不到其他重叠的未退款记录，不会被标记为重复。
• 客户C：1 Jan-31 Jan和6 Jan-5 Feb的invested记录 → 满足重叠条件，都会被标记为重复。

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