题目

Weather 表：

+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

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

+----+
| id |
+----+
| 2  |
| 4  |
+----+

2015-01-02 的温度比前一天高（10 -> 25）,2015-01-04 的温度比前一天高（20 -> 30）

前置知识

LAG 函数

LAG 窗口函数返回位于分区中当前行的上方（之前）的某个给定偏移量位置的行的值。例如：

SELECT 
    *,
    LAG(temperature,1) over(order by recorddate) as pre_temperature,
    LAG(recorddate,1) over(order by recorddate) as pre_recorddate
FROM Weather

获得上一条数据的温度和日期

| id | recordDate | temperature | pre_temperature | pre_recorddate |
| -- | ---------- | ----------- | --------------- | -------------- |
| 1  | 2015-01-01 | 10          | null            | null           |
| 2  | 2015-01-02 | 25          | 10              | 2015-01-01     |
| 3  | 2015-01-03 | 20          | 25              | 2015-01-02     |
| 4  | 2015-01-04 | 30          | 20              | 2015-01-03     |

日期计算

我们可以使用 DATEDIFF 函数来计算日期的差值。

解题思路

通过使用 LAG 函数，我们可以先日期进行排序，然后使用 LAG 函数来获取上一条数据的温度pre_temperature以及日期pre_recorddate。

SELECT 
    *,
    LAG(temperature,1) over(order by id) as pre_temperature,
    LAG(recorddate,1) over(order by id) as pre_recorddate
FROM Weather

再找出今日的温度比昨日高的数据的id即可，并且日期的差值为1。

SELECT
    t.id
FROM (
    SELECT 
    *,
    LAG(temperature,1) over(order by recorddate) as pre_temperature,
    LAG(recorddate,1) over(order by recorddate) as pre_recorddate
FROM Weather
) t
WHERE t.temperature > t.pre_temperature
AND DATEDIFF(recorddate,pre_recorddate) = 1