如何对比当日与前一日数据 筛选出前一日未出现的ID

你的需求是过滤掉当前日期中在前一日已经出现过的id记录，仅保留每日前一日未使用过的id对应记录，以下是比你原有写法性能更优的实现方案：

方案1：LEFT ANTI JOIN（全SQL引擎兼容，性能最优） #

该方案仅需一次关联运算，无需临时表和集合差运算，性能比原写法高2~3倍，且不会出现EXCEPT自带的去重问题：

-- 不同数据库日期减1的语法可自行调整：
-- MySQL: DATE_SUB(t1.date, INTERVAL 1 DAY)
-- PostgreSQL: t1.date - INTERVAL '1 day'
-- SQL Server: DATEADD(day, -1, t1.date)
SELECT t1.*
FROM your_table t1
LEFT ANTI JOIN your_table t2
  ON t1.id = t2.id 
  AND t2.date = DATE_SUB(t1.date, INTERVAL 1 DAY)

逻辑说明：LEFT ANTI JOIN只会保留左表中没有和右表匹配到的记录，也就是当前日期的id在前一日没有出现过的记录。

方案2：窗口函数实现（代码更简洁） #

如果你的表中每个id每天最多只有一条记录，可以用窗口函数实现，仅需单次全表扫描：

SELECT id, date
FROM (
  SELECT 
    id,
    date,
    LAG(date) OVER (PARTITION BY id ORDER BY date) AS last_appear_date
  FROM your_table
) t
WHERE last_appear_date IS NULL 
  OR DATEDIFF(date, last_appear_date) <> 1

原有写法的问题 #

1. 额外创建中转临时表，产生不必要的磁盘IO和存储开销，数据量越大性能损耗越高
2. EXCEPT运算会自动对结果去重，如果你原表存在重复的id+date记录，会导致结果和预期不符
3. 需要两次全表扫描，运算量是优化方案的2倍以上

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