day1 rising temperature -- three methods

topic

Topic source: Rising temperature

id for all dates with a warmer temperature than the previous (yesterday) date.

The MySQL8.0 is used, and it has not been MySQL5.6 in the 061df9ce718599 version, so it is not guaranteed to be correct.

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: Method 1

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

Parse

There is only one table, now to find out the date id today's temperature is warmer than yesterday's.

Therefore, it is necessary to use self-connection, that is, to connect weather and weather by themselves.

After the self-connection, the self-connected table needs to be w1 . If the alias is not taken, the two weather table names will conflict. Here the weather as table today, w1 as yesterday table.

After the two tables are self-connected, a connection condition is required. The connection condition is today and yesterday's date .

MySQL provides the datediff function to compare the time difference between two dates. If the difference between the two dates is 1 days, then it is today and the question.

Finally, the data where today's temperature is higher than yesterday's temperature are filtered out.

SQL: Method 2

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

Parse

The idea is the same as that of Method 1, the difference is that the method of calculating today and yesterday is different.

adddate function provided by MySQL is used here. This function is to offset the date function with a regular pattern.

SQL: Method 3

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;

Parse

Using the window function lead , it is offset from back to 1 with an offset of 061df9ce718730 days.

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 me say here that the window function also has a 061df9ce71884f offset from front to back, and the usage is the same as lead Here is an example of lead

The first three columns are the weather , and the last three columns are the data calculated lead

Why is the offset 1 ?

Because the comparison is today and yesterday, and the date here is continuous, so use 1 .

Then query the data as a temporary table temp .

The filter condition is nextTemp > temperature , and finally use datediff compare the difference between the two dates, which can be written or not, because the dates here are consecutive.

More reference for solving problems: https://github.com/astak16/blog-mysql