Mysql self join to match current record to previous record

Forum Moderators: open

Message Too Old, No Replies

Mysql self join to match current record to previous record

deejay

8:31 am on Oct 5, 2010 (gmt 0)

Hiya

I have a set of data which is a bunch of
readings taken at various dates, eg:

Siteid | Date | Measurement
1 | 2008-11-20 | 28.432
1 | 2008-12-04 | 37.681
1 | 2009-01-12 | 59.529
1 | 2009-02-04 | 67.831

What I need is to be able to match each date's reading against the reading for the max previous date for each site, ie,

The reading for 2009-02-04 needs to be matched to the reading for 2009-01-12, and the reading for 2009-01-12 in turn needs to be matched against 2008-12-04. Basically I need to be able to evaluate the measurement's growth rate between the two dates.

At the moment I have:

SELECT * FROM readings AS curr INNER JOIN readings AS prev ON curr.siteid=prev.siteid AND curr.date>prev.date WHERE curr.siteid=1

Which does match them correctly, but gives ALL the previous reading dates, when I just need the one record with the maximum previous date.

I've messed about with some max() subqueries, but ended up getting further away from a result.

Help? Please?

enigma1

3:11 pm on Oct 5, 2010 (gmt 0)

have you tried to append limit 1 at the end of the query?
...WHERE curr.siteid=1 order by curr.date limit 1

deejay

8:00 pm on Oct 5, 2010 (gmt 0)

DOesn't work in this case Enigma1, as I need all matches, not just one, but only the match where the previous date is immediately prior to the current date.

Yeah, I know.. I confuse myself too.

The query example I gave will produce a result with X rows that matches

Current = Previous
2008-12-04 = 2008-11-20
2009-01-12 = 2008-11-20
2009-01-12 = 2008-12-04
2009-02-04 = 2008-11-20
2009-02-04 = 2008-12-04
2009-02-04 = 2009-01-12

The result set I need to get to is:

Current = Previous
2008-12-04 = 2008-11-20
2009-01-12 = 2008-12-04
2009-02-04 = 2009-01-12

.. of course with the correct readings alongside.

Does that help?

Demaestro

8:24 pm on Oct 5, 2010 (gmt 0)

deejay,

The only way I can think of that wouldn't be a nightmare of code would be to put a sequential id into the READINGS table.

If you can get your data like this:

Siteid | Date | Measurement | uniq_sequence_id
1 | 2008-11-20 | 28.432 | 1
1 | 2008-12-04 | 37.681 | 2
1 | 2009-01-12 | 59.529 | 3
1 | 2009-02-04 | 67.831 | 4

Then all you have to do is select the row you want then add 1 to the sequence and grab the row with that sequence id, then subtract 1 from the sequence and grab that row.

SELECT *
FROM readings AS curr
left join readings as prev on (curr.uniq_sequence_id - 1) = prev.uniq_sequence_id
left join readings next on (curr.uniq_sequence_id + 1) = next.uniq_sequence_id

This logic will only work if the sequence doesn't skip any numbers.

deejay

8:30 pm on Oct 5, 2010 (gmt 0)

Shoot.

Thanks DeMaestro. That's going to be awkward at best and probably not reliable - too many other variables in this case.

I might have to accept that it ain't going to fly in mysql alone and get stuck into some php as well.

Demaestro

9:11 pm on Oct 5, 2010 (gmt 0)

Ya, write a php function to deal with the data.

I noticed when I saw the site_id that the sequential thing was most likely too 'hacky' to pull off.

With a simple data set it would do the trick though.

It is on my brain now, usually when something sticks with me like this the answer will come to me while sitting on the toilet or just before/after I sleep.

Basically what you need is a function that gets closest future date and a function that gets closest past date, then you pass it the curr row and let it find those other rows for you then return all three from the function.

deejay

11:02 pm on Oct 5, 2010 (gmt 0)

*lol* well if inspiration strikes, do come back and share please!

It's silly isn't it? This isn't the first time over the years I've had to do just this - mostly in Access though - it seems silly that there isn't a clean way round it.

Ideally maybe a subquery, but I need to be able to delimit the subquery by a field from the top query.

Gaah... would just be so much cleaner if I could do it in the initial query.

johnblack

12:01 am on Oct 6, 2010 (gmt 0)

select measurement as curr_measurement,
(select top 1 measurement
from readings r2
where r2.date < r1.date
and r2.site_id = 1
order by r2.date desc) as prev_measurement
from readings r1
where r1.site_id = 1

That's really off the top of my head and syntactically incorrect, but you get the gist of returning a value from a subquery.

deejay

1:28 am on Oct 6, 2010 (gmt 0)

now that is interesting johnblack. I have to admit, whenever I have to use a subquery my biggest hurdle is deciding just where in the darn top query it goes (select, join, where, etc).

I think top 1 is sql syntax, rather than mysql, but it does raise the option of using limit in the subquery, which might just be what I'm looking for.

I'll go have a play and report back. -thanks.

johnblack

2:05 am on Oct 6, 2010 (gmt 0)

Yeah sorry at my day job at the mo where I use SQL server, but my evening job involves mySQL :)

I'm not even sure if that 'subquery in the select' works in mySQL as I think I've only used it in SQL Server

Sorry for the vagueness but I hope I've pointed you in a helpful direction!

deejay

2:21 am on Oct 6, 2010 (gmt 0)

MORE than helpful johnblack = check out this baby!


SELECT siteid AS curr_siteid, date AS curr_date, measurement AS curr_meas, ( 

SELECT date
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC 
LIMIT 1 
) AS prev_date, ( 

SELECT measurement
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC 
LIMIT 1 
) AS prev_meas, ( 

SELECT curr_meas - measurement
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC 
LIMIT 1 
) AS diff_meas, ( 

SELECT Datediff( curr_date, date ) 
FROM readings AS prev
WHERE siteid =1
AND TYPE =1
AND date < curr_date
ORDER BY date DESC 
LIMIT 1 
) AS diff_date
FROM readings AS curr
WHERE siteid =1
AND TYPE =1
ORDER BY curr_date DESC

Obviously that does a little more than my initial question - it matches each reading to the one with the date immediately prior, and displays the dates and readings, as well as the difference between the two readings and the interval between the dates. Voila:

curr_siteid | curr_date | curr_meas | prev_date | prev_meas | diff_meas | diff_date
1 | 2009-02-04 | 67.831 | 2009-01-12 | 59.529 | 8.302 | 23
1 | 2009-01-12 | 59.529 | 2008-12-04 | 37.681 | 21.848 | 39
1 | 2008-12-04 | 37.681 | 2008-11-20 | 28.432 | 9.249 | 14
1 | 2008-11-20 | 28.432 | NULL | NULL | NULL | NULL

I'm heading towards calculating a growth rate per day, which this all but does at this point.

Hopefully this might help some other weary traveller at some point :)