Question 190749 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Data Compare

Looking to compare today’s temperature to yesterday’s. If today’s is higher than I want to return that row’s id.

Table has 3 columns: id, RecordDate, and temperature.

Code works on small data sets but looking at a data set of 1400 seeing a 1% failure rate.

SELECT DISTINCT
    id

FROM
    (
        SELECT DISTINCT
            id,
            recordDate, 
            recordDate - LAG(recordDate, 1) OVER (ORDER BY recordDate) AS diff, 
            temperature - LAG(temperature, 1) OVER (ORDER BY recordDate) AS dif

        FROM
            Weather
    ) AS t

WHERE
    dif > 0
    AND
    diff = 1

ORDER BY
    id ASC;

I expect this to sort the data by recordDate.

There are 2 conditions that must be met for the ID to be captured

  1. Today’s temperature is greather than yesterday (dif > 0)
  2. and yesterday is in fact yesterday – there can only be a 1 day difference between rows (diff = 1)

Let me know how you’d improve this! Have been grinding for a while and could use the help. Feel like there might be an easier way to do this even.

Tags:

2

Answers


  1. 0 
    SELECT DISTINCT id
FROM (
    SELECT id,
           recordDate,
           temperature - LAG(temperature) OVER (ORDER BY recordDate) AS dif
    FROM Weather
) AS t
WHERE dif > 0
  AND EXTRACT(DAY FROM (recordDate - LAG(recordDate) OVER (ORDER BY recordDate))) = 1
ORDER BY id ASC;
    Login or Signup to reply.
  2. 0

    I’m not sure about performance, but what do you think of this?

    SELECT w2.id from Weather w1 join Weather w2 
ON w2.temperature>w1.temperature 
AND date(w1.recordDate) = date(date_add(w2.recordDate, interval 1 day))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search