如何比较 payments 与 remaining balance 并识别重复记录

结论

仅凭现有四个字段，无法可靠判断哪些记录重复。示例中的 PAYMENT_DATE 完全相同，表中也没有记录写入顺序或 Process X 执行批次的字段。Oracle 表本身不存在可依赖的”物理行顺序”。

如果要根据余额变化识别重复 payment，至少需要增加一个稳定递增的标识，如 PAYMENT_ID 或 LOAD_SEQ。之后按该标识逐条处理，并以”上一笔有效 payment 的余额”作为校验基准：

expected_balance = previous_valid_balance + PAYMENT_AMT

示例中的 PAYMENT_AMT 以负数保存，因此这里使用加法。第二条记录的计算结果为：

894.64 + (-15.45) = 879.19
879.19 != 860.99

计算余额与记录中的余额不一致，所以第二条是重复记录。跳过它以后，第三条仍与上一笔有效余额 894.64 比较：

894.64 + (-33.65) = 860.99

结果一致，因此第三条是有效 payment。

先补充可靠的排序字段

可以先增加一个递增主键：

ALTER TABLE CUSTOMER_PAYMENTS ADD (
    PAYMENT_ID NUMBER GENERATED ALWAYS AS IDENTITY
);

ALTER TABLE CUSTOMER_PAYMENTS ADD CONSTRAINT CUSTOMER_PAYMENTS_PK
    PRIMARY KEY (PAYMENT_ID);

如果还要区分 Process X 的每次执行，建议同时保存批次标识：

ALTER TABLE CUSTOMER_PAYMENTS ADD (
    PROCESS_RUN_ID NUMBER
);

对于现有数据，新增 identity 只能确定后续记录的顺序，无法证明历史记录原本的写入顺序。历史顺序需要通过日志、批次表或其他可信来源补充。

使用递归查询识别重复记录

这里不能简单使用 LAG(REM_BALANCE)。一旦某条重复记录被当作比较基准，下一条记录的判断也会受到影响。查询需要持续保存”最近一笔有效记录的余额”，并确保重复记录不会更新这个值。

下面是适用于 Oracle 递归子查询分解的写法：

WITH
ordered_payments (
    customer_no,
    payment_id,
    payment_date,
    payment_amt,
    rem_balance,
    rn
) AS (
    SELECT customer_no,
           payment_id,
           payment_date,
           payment_amt,
           rem_balance,
           ROW_NUMBER() OVER (
               PARTITION BY customer_no
               ORDER BY payment_id
           ) AS rn
    FROM customer_payments
),
payment_check (
    customer_no,
    payment_id,
    payment_date,
    payment_amt,
    rem_balance,
    rn,
    previous_valid_balance,
    expected_balance,
    status
) AS (
    /* 假设每位 customer 的第一条记录是有效起点 */
    SELECT customer_no,
           payment_id,
           payment_date,
           payment_amt,
           rem_balance,
           rn,
           CAST(NULL AS NUMBER) AS previous_valid_balance,
           rem_balance AS expected_balance,
           CAST('VALID' AS VARCHAR2(10)) AS status
    FROM ordered_payments
    WHERE rn = 1

    UNION ALL

    SELECT p.customer_no,
           p.payment_id,
           p.payment_date,
           p.payment_amt,
           p.rem_balance,
           p.rn,
           c.expected_balance AS previous_valid_balance,
           CASE
               WHEN ROUND(c.expected_balance + p.payment_amt, 2)
                    = ROUND(p.rem_balance, 2)
               THEN p.rem_balance
               ELSE c.expected_balance
           END AS expected_balance,
           CASE
               WHEN ROUND(c.expected_balance + p.payment_amt, 2)
                    = ROUND(p.rem_balance, 2)
               THEN 'VALID'
               ELSE 'DUPLICATE'
           END AS status
    FROM payment_check c
    JOIN ordered_payments p
      ON p.customer_no = c.customer_no
     AND p.rn = c.rn + 1
)
SELECT customer_no,
       payment_id,
       payment_date,
       payment_amt,
       rem_balance,
       previous_valid_balance,
       ROUND(previous_valid_balance + payment_amt, 2)
           AS calculated_balance,
       status
FROM payment_check
ORDER BY customer_no, payment_id;

对于示例数据，判断结果应为：

如果能够取得初始余额

上述查询把每位 customer 的第一条记录当作可信起点。如果客户账户表中保存了初始余额 910.09，第一条记录也应接受校验：

910.09 + (-15.45) = 894.64

这种情况下，应将账户初始余额作为递归查询的初始状态，不再无条件接受第一条记录。这样可以验证完整的 payment 链条。

注意事项

• 不要用 ROWID 表示业务顺序。数据移动、导入或表重建后，ROWID 可能发生变化。
• 示例中的 PAYMENT_DATE 完全相同，不能单独用于排序。即使时间精确到秒，同一秒内也可能发生多笔交易。
• 金额字段为 NUMBER(20,4)，需要明确业务采用几位小数。示例按货币的两位小数使用 ROUND(..., 2)；如果业务允许四位小数，应改用 ROUND(..., 4)。
• 更稳妥的做法是让 Process X 写入 PROCESS_RUN_ID，再在同一批次内识别此前批次已经出现的 payment。余额校验适合作为一致性检查，但不能代替支付流水号、外部交易号等真正的幂等键。
• 如果缺少可靠顺序、初始余额、批次标识或交易唯一标识，任何查询都只能根据假设进行推断，无法保证结果正确。

备注：内容仅供参考。