From: Baron Schwartz (baronxaprb.com)
Date: Tue Aug 14 2007 - 08:00:22 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi,

Christian High wrote:
> On 8/14/07, Baron Schwartz <baronxaprb.com> 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]