From: Peter Brawley
Date: November 17 2009 6:41pm
Subject: Re: Techniques for queries on a series?
List-Archive: http://lists.mysql.com/mysql/219396
Message-Id: <4B02EE52.3030400@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070209090909000906020606"

--------------070209090909000906020606
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jan,

 > I often need a pattern where one record refers to the one "before" 
it, based on the order of some field.

Some ideas under "Sequences" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

----

Jan Steinman wrote:
> I often need a pattern where one record refers to the one "before" it, 
> based on the order of some field.
>
> For example, a vehicle log, in which the field "odometer" is the 
> current odometer reading upon an event, such as a trip completion, 
> fueling, maintenance, etc. Very often one wants to calculate the 
> difference in odometer readings, which means accessing two consecutive 
> records, when ordered by odometer. I currently put both readings in 
> one record, which seems an unnecessary de-normalization and extra work 
> in data entry.
>
> Another example: an amortization database, where the value of the loan 
> principle depends on the payment, interest rate, but also the previous 
> record's principle. Someone makes a payment on a loan, which needs to 
> be entered along with the declining balance, but that depends on the 
> balance of the previous record.
>
> Quite often, I see this pattern in time series data. Data is logged 
> and time-stamped, and many queries depend on the difference in 
> time-stamps between two consecutive records. For example, milk 
> production records: with milk goats, if milking is early or late, the 
> amount of milk is lower or higher. I need to do an analysis of 
> short-term milk production, which means daily production needs to be 
> normalized for variations in time, which means I need to refer to time 
> and volume deltas from two consecutive records, ordered by time.
>
> Are there some good techniques for dealing with this common pattern in 
> SQL? Or do I need to do it all with two queries and a programming 
> language?
>
> Pointers to good web references are welcome. I have googled quite a 
> bit, and haven't turned up anything apropos.
>
> Thanks for whatever insight you can offer!
>
> :::: A virus has marked this email as being virus-free! ::::
> :::: Jan Steinman http://www.VeggieVanGogh.com ::::
>
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.69/2508 - Release Date: 11/17/09 07:40:00
>
>   

--------------070209090909000906020606--