From: Jan Steinman
Date: November 17 2009 6:35pm
Subject: Techniques for queries on a series?
List-Archive: http://lists.mysql.com/mysql/219395
Message-Id: <5DEAF917-3054-4A90-AE22-1DE5062C768E@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v936)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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