day1 上升的温度--三种方法
文章目录
题目
题目来源:上升的温度
描述:查找与之前(昨天)日期相比温度更高的所有日期的 id 。
使用的是 MySQL8.0,没有在 MySQL5.6 版本中测验过,不保证正确。
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 比较两个日期差可写可不写,因为这里日期是连续的。
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论