On 2012-10-20, Berend Tober <btober@xxxxxxxxxxxxxxx> wrote: > Thalis Kalfigkopoulos wrote: >> On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell <rod@xxxxxx> wrote: >>> On 20/10/2012 11:54, ochaussavoine wrote: >>>> I have a table 'tmvt' with a field 'created' in the row, and would like to >>>> compute the difference between successive rows. The solution I found is: >>> >>> I think you can do it with a window function. >>> >> >> In particular you're looking probably for the lag() window function. > > What about if there is more than one column you want the > difference for (... coincidentally I am writing a article on this > topic right now! ...), say a table which is used to record a > metered quantity at not-quite regular intervals: > > CREATE TABLE electricity > ( > current_reading_date date, > current_meter_reading integer > ); > > > with sample data: > > > '2012-09-07',2158 > '2012-10-05',3018 > > > > and I want an output such as: > > > Meter Read on October 5 > > Current Previous kWh > Reading Reading Used > ----------------------------------- > 3018 - 2158 = 860 > > Number service days = 28 > > > I am working on a write-up of a neat solution using CTE's, but > would be interested in other's views. electricity meter may bis a bad example as usage meters often have fewer digits than are needed to track all historical usage eg: '2012-05-07',997743 '2012-06-06',999601 '2012-07-05',000338 '2012-08-06',001290 '2012-09-07',002158 '2012-10-05',003018 -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
