如何删除同操作同币种、价格2%区间内的重复告警数据？

一、两种去重方案对比 #

• 插入时过滤（推荐优先采用）
  插入新告警前先查询，判断是否存在同CoinID、同op且价格在±2%范围内的已有告警，存在则放弃插入。

  • 优势：从源头杜绝重复，不会出现重复告警的时间窗口，数据始终干净；只要给CoinID+op建立联合索引，查询过滤的耗时几乎可以忽略，完全不用担心性能问题。
  • 劣势：插入逻辑多了一步查询，但索引优化后影响极小。

• 定时批量删除
  每小时执行一次脚本清理重复告警。

  • 优势：插入逻辑简单，不影响插入性能。
  • 劣势：会存在1小时内的重复告警，可能导致重复通知；如果表数据量大，批量删除操作也会带来一定的性能开销。

二、删除已有重复记录的SQL方法 #

你当前的查询会返回所有两两匹配的重复对，无法直接用来精准删除重复项。可以用以下SQL保留每组重复告警中最早插入的记录（假设ID是自增主键，ID越小插入时间越早），删除其余重复项：

DELETE FROM alert a
WHERE EXISTS (
    SELECT 1
    FROM alert b
    WHERE b.CoinID = a.CoinID
      AND b.op = a.op
      AND b.price BETWEEN a.price * 0.98 AND a.price * 1.02
      AND b.ID < a.ID
);

逻辑说明： #

对于每条告警记录a，如果存在另一条更早插入的记录b满足：同币种、同操作方向、价格偏差在2%以内，那么a就是重复记录，执行删除。这样会自动保留每组重复里的第一条记录。

注意事项： #

1. 执行删除前建议先执行SELECT * FROM alert a WHERE EXISTS (...)，验证要删除的记录是否符合预期。
2. 操作前务必备份数据，避免误删。
3. 给CoinID+op建立联合索引，可以大幅提升这个删除语句的执行效率。

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