homepage Welcome to WebmasterWorld Guest from 54.198.139.141
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Mysql self join to match current record to previous record
deejay




msg:4211472
 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




msg:4211689
 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




msg:4211886
 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




msg:4211910
 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




msg:4211913
 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




msg:4211949
 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




msg:4211990
 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




msg:4212003
 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




msg:4212028
 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




msg:4212043
 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




msg:4212044
 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 :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved