SQLite查询同表符合三条件的重复行结果异常排查

原语句问题分析 #

原SQL存在的核心问题是：子查询对description、amount字段分组后，直接在SELECT列表中包含了非分组、非聚合的created_at字段，SQLite 对于这类字段会随机返回分组内的某一行值。当同分组下有多条记录但时间差超出60秒时，子查询返回的随机时间可能匹配到分组内的某一条记录，导致错误返回不符合要求的行。

修正方案 #

方案1：自连接实现（写法简单，适合有主键的表） #

SELECT DISTINCT t.*
FROM transactions t
INNER JOIN transactions t2
  ON t.description = t2.description
  AND t.amount = t2.amount
  AND t.id <> t2.id -- 排除自身和自身匹配的情况
  AND ABS(strftime('%s', t.created_at) - strftime('%s', t2.created_at)) <= 60
WHERE t.account_id = 1
  AND t2.account_id = 1;

方案2：窗口函数实现（性能更优，无需额外去重） #

WITH marked_transactions AS (
  SELECT 
    *,
    -- 判断和同分组前一行的时间差是否在60秒内
    ABS(strftime('%s', created_at) - strftime('%s', LAG(created_at) OVER (
      PARTITION BY description, amount ORDER BY created_at
    ))) <= 60 AS match_prev,
    -- 判断和同分组后一行的时间差是否在60秒内
    ABS(strftime('%s', created_at) - strftime('%s', LEAD(created_at) OVER (
      PARTITION BY description, amount ORDER BY created_at
    ))) <= 60 AS match_next
  FROM transactions
  WHERE account_id = 1
)
SELECT description, amount, created_at 
FROM marked_transactions
WHERE match_prev = 1 OR match_next = 1;

效果验证 #

两个方案均能满足所有测试样例要求：

• 样例1：两条记录amount字段不同，不会触发匹配，无返回结果
• 样例2：两条记录时间差为120秒，超出阈值，无返回结果
• 样例3：两条记录满足所有重复条件，正常返回两条数据

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