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 ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
