MySQL温度对比查询优化求助：1400条数据存在1%失败率

原代码存在的问题 #

1. 日期差计算逻辑错误：recordDate - LAG(recordDate, 1)的写法仅在recordDate为纯DATE类型时能正确得到天数差，若为DATETIME/TIMESTAMP类型，会返回时间戳差值（如秒数），导致diff=1的条件无法匹配真正间隔1天的记录，这是大数据集下出现失败的核心原因。
2. 冗余的DISTINCT：嵌套查询和外层的DISTINCT完全多余，既增加计算开销，还可能在极端场景下意外过滤掉有效记录。
3. 窗口函数逻辑不严谨：仅按recordDate排序使用LAG，若存在同一天多条记录，会错误关联同日期的相邻行，而非真正的“昨日”记录。

优化后的实现方案 #

方案1：修正窗口函数逻辑（推荐单表扫描场景） #

使用数据库原生的日期差函数替代直接减法，确保日期间隔判断准确：

SELECT id
FROM (
    SELECT 
        id,
        temperature,
        recordDate,
        LAG(temperature) OVER (ORDER BY recordDate) AS prev_temperature,
        LAG(recordDate) OVER (ORDER BY recordDate) AS prev_recordDate
    FROM Weather
) AS temp_data
WHERE temperature > prev_temperature
  -- 根据数据库类型选择对应日期差函数：
  -- MySQL: DATEDIFF(recordDate, prev_recordDate) = 1
  -- PostgreSQL: recordDate - prev_recordDate = INTERVAL '1 day'
  -- SQL Server: DATEDIFF(day, prev_recordDate, recordDate) = 1
  AND DATEDIFF(recordDate, prev_recordDate) = 1
ORDER BY id ASC;

方案2：自连接实现（逻辑直观，兼容性强） #

通过自连接直接关联当日与昨日的记录，逻辑更清晰，避免窗口函数的排序依赖：

SELECT w1.id
FROM Weather w1
INNER JOIN Weather w2 
  -- 同样根据数据库类型调整日期差判断
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature
ORDER BY w1.id ASC;

关键优化点说明 #

• 替换错误的日期差计算方式，使用数据库专用的日期函数确保间隔判断准确。
• 移除所有冗余的DISTINCT，减少不必要的计算开销。
• 两种方案均能避免原代码中因日期类型或同日期多条记录导致的匹配错误，在大数据集下稳定性更强。

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