charge-java/database/8-dedupe-pay-log.sql

-- ============================================================
-- t_pay_log 重复商户订单号：诊断 + 去重（建唯一索引的前置步骤）
--
-- 【执行状态 · 2026-10-02】生产库残留的 10 组重复已由人工手工清理完毕。
--   本文件不再需要执行，仅保留备查；若日后 8.sql 的【执行前检查 1】再次返回结果，
--   可按本文件流程处理。
--   【3.定损】只用于评估历史多入账金额，业务已决定不追溯、不追回，跳过即可。
--   清理结果仍必须通过 8.sql 末尾的【上线后对账 1/2/3】验收（那属于数据正确性，与追款无关）。
--
-- 背景：8.sql 的执行前检查 1 返回了 10 条重复（同一 out_trade_no 有多行）。
--       同一个 out_trade_no 只可能来自同一笔微信支付，多行意味着该笔回调被处理了多次，
--       或有人工补录。必须先去重才能建 uk_pay_log_out_trade_no。
--
-- ⚠️ 顺序：先跑【1.诊断】→【2.判定】→【3.定损】→【4.备份并去重】→ 再回到 8.sql 建索引。
-- ⚠️ 删除 pay_log 行 = 把"账本"改成唯一，**不等于**把多充进余额的钱收回；这两件事分开决策（见第 3 步）。
-- ⚠️ 第 4 步只自动处理"同一用户 + 同一交易号 + 同一金额"的明确重复组，
--    其余组会列出来要求人工判断，避免误删别人真实收款的记录。
-- ============================================================


-- ============================================================
-- 【1.诊断】10 组重复的画像
-- ============================================================
SELECT g.out_trade_no,
       g.cnt                                   AS `行数`,
       g.tx_cnt                                AS `交易号种类`,
       g.user_cnt                              AS `用户数`,
       g.amount_cnt                            AS `金额种类`,
       g.first_create                          AS `首次记录时间`,
       g.last_create                           AS `末次记录时间`,
       TIMESTAMPDIFF(SECOND, g.first_create, g.last_create) AS `间隔秒`,
       g.ids,
       g.txs
FROM (
    SELECT out_trade_no,
           COUNT(*)                        AS cnt,
           COUNT(DISTINCT transaction_id)  AS tx_cnt,
           COUNT(DISTINCT user_id)         AS user_cnt,
           COUNT(DISTINCT total)           AS amount_cnt,
           MIN(create_time)                AS first_create,
           MAX(create_time)                AS last_create,
           GROUP_CONCAT(id ORDER BY id)             AS ids,
           GROUP_CONCAT(transaction_id ORDER BY id) AS txs
    FROM charge_app.t_pay_log
    GROUP BY out_trade_no
    HAVING COUNT(*) > 1
) g
ORDER BY g.first_create;


-- ============================================================
-- 【2.判定】逐行看全字段（人工确认每条重复的成因，别盲删）
-- ============================================================
SELECT p.*
FROM charge_app.t_pay_log p
JOIN (
    SELECT out_trade_no
    FROM charge_app.t_pay_log
    GROUP BY out_trade_no
    HAVING COUNT(*) > 1
) d ON d.out_trade_no = p.out_trade_no
ORDER BY p.out_trade_no, p.id;

-- 判定口径：
--   交易号种类 = 1 且 间隔秒很小(秒级)  → 同一笔回调被并发/重试处理了两次：真实资金只收了一次，
--                                        多出的那行对应"多入账"，保留 MIN(id)，其余删除（第 3、4 步）
--   交易号种类 = 1 且 间隔很大(天/月)    → 可能是事后人工补录（历史上的补单）：
--                                        先确认当时余额是否已补过，再决定删哪行
--   交易号种类 > 1                       → 异常数据，逐条人工判断，不要批量删
--   用户数 > 1                           → 订单号撞号（不同用户同一订单号），必须单独定损
--   金额种类 > 1                         → 数据不一致，逐条人工判断

-- 安全校验：确认没有其他表引用这些 pay_log 行
-- （充值场景 wallet_detail.transaction_id 实际未写入，这里仍做一次兜底检查；有结果先处理引用再删）
SELECT w.id, w.user_id, w.order_no, w.transaction_id
FROM charge_app.t_wallet_detail w
JOIN (
    SELECT out_trade_no
    FROM charge_app.t_pay_log
    GROUP BY out_trade_no
    HAVING COUNT(*) > 1
) d ON d.out_trade_no = w.order_no
WHERE w.transaction_id IS NOT NULL;


-- ============================================================
-- 【3.定损】多记了多少、涉及谁、有没有叠加影响退款
-- ============================================================
-- 3.1 疑似多入账金额（"多出的行数 × 单笔金额"估算，单位：分）
SELECT g.user_id,
       COUNT(*)                        AS `重复订单数`,
       SUM((g.cnt - 1) * g.total_each) AS `疑似多入账金额分`
FROM (
    SELECT user_id, out_trade_no, COUNT(*) AS cnt, MIN(total) AS total_each
    FROM charge_app.t_pay_log
    GROUP BY user_id, out_trade_no
    HAVING COUNT(*) > 1
) g
GROUP BY g.user_id;

-- 3.2 受影响用户的当前余额与充值口径（去重前后对照，用于和用户/财务核对）
--     为避免依赖窗口函数（兼容 MySQL 5.7 / MariaDB 10.2 以下），用 MIN(id) 自连接实现
SELECT g.user_id,
       g.`重复订单数`,
       g.`疑似多入账金额分`,
       a.balance                    AS `当前余额分`,
       a.discount_amount            AS `不可退金额分`,
       w.wallet_recharge_total      AS `钱包已确认充值分`,
       p.pay_log_total_with_dup     AS `pay_log合计_含重复分`,
       p.pay_log_total_dedup        AS `pay_log合计_去重后分`
FROM (
    SELECT user_id, COUNT(*) AS `重复订单数`, SUM((cnt - 1) * total_each) AS `疑似多入账金额分`
    FROM (
        SELECT user_id, out_trade_no, COUNT(*) AS cnt, MIN(total) AS total_each
        FROM charge_app.t_pay_log
        GROUP BY user_id, out_trade_no
        HAVING COUNT(*) > 1
    ) x
    GROUP BY user_id
) g
LEFT JOIN charge_app.t_account a ON a.user_id = g.user_id
LEFT JOIN (
    SELECT user_id, SUM(amount) AS wallet_recharge_total
    FROM charge_app.t_wallet_detail
    WHERE type = 1 AND status = 1
    GROUP BY user_id
) w ON w.user_id = g.user_id
LEFT JOIN (
    SELECT p.user_id,
           SUM(p.total) AS pay_log_total_with_dup,
           SUM(CASE WHEN p.id = k.keep_id THEN p.total ELSE 0 END) AS pay_log_total_dedup
    FROM charge_app.t_pay_log p
    LEFT JOIN (
        SELECT out_trade_no, MIN(id) AS keep_id
        FROM charge_app.t_pay_log
        GROUP BY out_trade_no
    ) k ON k.out_trade_no = p.out_trade_no
    WHERE p.trade_state = 'SUCCESS'
    GROUP BY p.user_id
) p ON p.user_id = g.user_id
ORDER BY g.`疑似多入账金额分` DESC;

-- 3.3 是否有人靠"虚高的充值合计"通过了退款校验（applyWxRefund 用 SUM(pay_log.total) 判断可退额度）
--     若有重复记录用户成功退款的，需单独评估是否超退
SELECT r.user_id, r.out_trade_no, r.total, r.refund, r.status, r.create_time
FROM charge_app.t_refund_log r
WHERE r.user_id IN (
    SELECT user_id FROM charge_app.t_pay_log
    GROUP BY user_id, out_trade_no HAVING COUNT(*) > 1
)
ORDER BY r.user_id, r.create_time;

-- 说明：第 3 步只做"定损"。是否向用户追回多入账金额属于业务/财务决策，
--       不要在未经确认的情况下直接扣减余额（余额被扣成负数会影响后续充电与退款判断）。


-- ============================================================
-- 【4.备份并去重】
-- ============================================================
-- 4.1 备份全部重复组（完整审计留痕；确认无误后再由人工决定是否清理该备份表）
CREATE TABLE charge_app.t_pay_log_dup_bak_20261002 AS
SELECT p.*
FROM charge_app.t_pay_log p
JOIN (
    SELECT out_trade_no
    FROM charge_app.t_pay_log
    GROUP BY out_trade_no
    HAVING COUNT(*) > 1
) d ON d.out_trade_no = p.out_trade_no;

-- 4.2 需人工处理的"不安全组"（同一订单号跨用户/跨交易号/跨金额）——应为 0 行；
--     若有结果，必须逐条人工确认后再决定，不要交给下面的自动删除
SELECT out_trade_no,
       COUNT(*)                       AS `行数`,
       COUNT(DISTINCT user_id)        AS `用户数`,
       COUNT(DISTINCT transaction_id) AS `交易号种类`,
       COUNT(DISTINCT total)          AS `金额种类`,
       GROUP_CONCAT(id ORDER BY id)   AS ids
FROM charge_app.t_pay_log
GROUP BY out_trade_no
HAVING COUNT(*) > 1
   AND (COUNT(DISTINCT user_id) > 1
     OR COUNT(DISTINCT transaction_id) > 1
     OR COUNT(DISTINCT total) > 1);

-- 4.3 待删除行明细核对（仅"安全组"：同一用户 + 同一交易号 + 同一金额，保留 MIN(id)）
SELECT p.id, p.out_trade_no, p.user_id, p.total, p.transaction_id, p.success_time, p.create_time
FROM charge_app.t_pay_log p
JOIN (
    SELECT out_trade_no, MIN(id) AS keep_id
    FROM charge_app.t_pay_log
    GROUP BY out_trade_no
    HAVING COUNT(*) > 1
       AND COUNT(DISTINCT user_id) = 1
       AND COUNT(DISTINCT transaction_id) = 1
       AND COUNT(DISTINCT total) = 1
) d ON d.out_trade_no = p.out_trade_no
WHERE p.id <> d.keep_id
ORDER BY p.out_trade_no, p.id;

-- 4.4 正式删除（用临时表承接 keep_id，绕开 MySQL "不能在同一语句里对目标表做子查询" 的限制 1093）
CREATE TEMPORARY TABLE tmp_pay_log_keep AS
SELECT out_trade_no, MIN(id) AS keep_id
FROM charge_app.t_pay_log
GROUP BY out_trade_no
HAVING COUNT(*) > 1
   AND COUNT(DISTINCT user_id) = 1
   AND COUNT(DISTINCT transaction_id) = 1
   AND COUNT(DISTINCT total) = 1;

DELETE p
FROM charge_app.t_pay_log p
JOIN tmp_pay_log_keep k ON k.out_trade_no = p.out_trade_no
WHERE p.id <> k.keep_id;

DROP TEMPORARY TABLE tmp_pay_log_keep;

-- 4.5 复核：应为 0 行（若仍有行，说明都属于 4.2 的不安全组，需人工处理）
SELECT out_trade_no, COUNT(*) AS cnt
FROM charge_app.t_pay_log
GROUP BY out_trade_no
HAVING COUNT(*) > 1;


-- ============================================================
-- 【5.回到 8.sql】
-- ============================================================
-- 4.5 返回 0 行后，再执行 database/8.sql 建唯一索引 uk_pay_log_out_trade_no。