1 上升的温度

[astak16]

题目

查找与之前（昨天）日期相比温度更高的所有日期的 id 。

create table weather (
	id int primary key auto_increment,
	recordDate date,
	temperature int
);

insert into weather(recordDate, temperature) values
('2015-01-01', 10),
('2015-01-02', 25),
('2015-01-03', 20),
('2015-01-04', 30);

SQL：方法一

select weather.id from weather join weather w1
on datediff(weather.recordDate, w1.recordDate) = 1
and weather.temperature > w1.temperature;

解析

只有一张表，现在要找出今天温度比昨天温度高的日期 id 。

所以需要用自连接，也就是把 weather 和 weather 进行自身连接。

在自连之后，需要将自连后的表取个别名 w1 ，如果不取别名的话，两个 weather 表名会冲突。这里把 weather 作为今天表， w1 作为昨天表。

两表自连之后需要有连接条件，连接条件是 今天和昨天的日期。

MySQL 提供了datediff 函数，用来比较两个日期之间的时间差，如果两个时间之间相差 1 天，那么就是今天和做题。

最后在筛选出今天的温度高于昨天温度的数据。

SQL：方法二

select weather.id from weather join weather w1
on weather.recordDate = adddate(w1.recordDate, interval 1 day)
and weather.temperature > w1.temperature;

解析

思路和方法一的思路是一样的，区别在于计算今天和昨天的方法不一样。

这里使用 MySQL 提供的 adddate 函数。这个函数是将日期函数一个规律进行偏移。

SQL：方法三

select id from (
	select
	temperature,
	recordDate ,
	lead(id, 1) over(order by recordDate) as id,
	lead(recordDate, 1) over(order by recordDate) as 'nextDate',
	lead(temperature, 1) over(order by recordDate) as 'nextTemp'
	from weather
) temp
where nextTemp > temperature and datediff(nextDate, recordDate) = 1;

解析

使用窗口函数 lead ，它是从后往前偏移，偏移量为 1 天。

select
	temperature,
	recordDate ,
	lead(id, 1) over(order by recordDate) as nextId,
	lead(recordDate, 1) over(order by recordDate) as 'nextDate',
	lead(temperature, 1) over(order by recordDate) as 'nextTemp'
from weather;

id	recordDate	temperature	nextId	nextDate	nextTemp
1	2015-01-01	10	2	2015-01-02	25
2	2015-01-02	25	3	2015-01-03	20
3	2015-01-03	20	4	2015-01-04	30
4	2015-01-04	30	null	null	null

这里说一下，窗口函数还有一个 lag 是从前往后偏移的，用法和 lead 是一样的。这里就用 lead 来举例。

前三列是 weather 原数据，后三列是使用窗口函数 lead 算出来的数据。

为什么是偏移 1 呢？

因为比较的是今天和昨天，而且这里日期是连续的，所以用 1 。

然后查询出来的数据作为一个临时表 temp 。

筛选条件就是 nextTemp > temperature ，最后使用 datediff 比较两个日期差可写可不写，因为这里日期是连续的。

Tips

datediff 语法
adddate 语法

[astak16]

想问一下，把时间用来做笔记和把时间用来做更多的题，这两个时间怎么权衡？

看自己的需要什么，你想要做更多的题，就把时间放在做题上面，笔记就简单记录一下；你想要留下点什么，就把笔记写好一点