高频 SQL 50 题（基础版）_197. 上升的温度

表： Weather

id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 无顺序要求 。
结果格式如下例子所示。

波峰波谷处理

SELECT id
FROM
(
    SELECT id, temperature, recordDate,
        LAG(temperature, 1) OVER (PARTITION BY ids ORDER BY recordDate) AS lag_temp,
        LAG(recordDate, 1) OVER (PARTITION BY ids ORDER BY recordDate) AS lag_date
    FROM (
        SELECT 1 AS ids, id, recordDate, temperature
        FROM Weather
    ) AS subquery
) AS qb
WHERE temperature > lag_temp
  AND recordDate = DATE_ADD(lag_date, INTERVAL 1 DAY)

两表联结

select w1.id from Weather w1, Weather w2
where w1.recordDate - interval 1 day = w2.recordDate 
and w1.Temperature > w2.Temperature