Hi,
Christian High wrote:
On 8/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:
Hi,
Christian High wrote:
I have a table that includes a date and a scale reading like
date scale_reading
2007-08-01 150
2007-08-02 125
these reading may or may not be taken everyday. I need to develop a
query that will subtract the scale reading on one day from the scale
reading on the next most recent reading. any ideas?
This may explain what you're looking for:
http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/
Very nice article that I no doubt can make use of. But in this
instance, unless I am missing it, it doesn't help. My problem seems to
be that the data is not necessarily entered in order. For example the
users may enter July 7th data then enter July 5th data. I can get
around this by ordering on the date. The problem that I cannot seem to
get around is they may not have any data for a particular date. when I
tried test the suggestions in the article against my data if there was
a hole in the date column, which is what i joined on because it needs
to subtract one day from the next most recent, it excluded these
instances because it did not meet the join criteria.
t1 is a select * view ordered by date on the above mentioned table
select t1.date_column, t1.reading_column, p.date_column, p.reading_column
join t1 p on
(t1.date_column = p.date_column + 1).
If I am missing something I would appreciate it if someone could point it out.
I think what I need is a way to find the most recent date as commpared
with a given date whether that date is the day before or 3 days
before.
I misunderstood your question. I think what you need is something like
the following. Working from the inside out,
1) find the most recent date previous to the current date
select max(date) from t1 as inner_t1 where inner_t1.date < ?
This query will not run efficiently; MySQL can't yet optimize it. A
logical equivalent that will be fast, if date is indexed, will be:
select date from t1 as inner_t1 where inner_t1.date < ?
order by date desc limit 1
That's the most recent date before any given date and will be the inmots
query. Now we need to find the corresponding scale_reading:
select scale_reading from t1 as mid_t1
where mid_t1.date = ( ... inmost query ... )
That's the middle query. Now you can place that in a subquery:
select date, scale_reading - ( ... middle query ... )
from t1 as outer_t1
Finally, resolve the ? reference in the correlated subquery:
select date, scale_reading - (
select scale_reading from t1 as mid_t1
where mid_t1.date = (
select date from t1 as inner_t1 where inner_t1.date < outer_t1.date
order by date desc limit 1
)
)
from t1 as outer_t1
I'm not attempting to run this, just reasoning about it, so I might be
wrong or there might be a syntax error.
The next issue is t1 is a view -- it might perform terribly. You might
be better off doing it another way, either querying the tables directly,
or using a user variable:
set @most_recent_reading := null;
select date,
scale_reading - @most_recent_reading,
@most_recent_reading := scale_reading
from t1;
Again untested, but hopefully you get the idea.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
