2012/10/20 Berend Tober <btober@xxxxxxxxxxxxxxx>: > Your suggestion almost worked as is for this, except that you have to note > that reading for meter #2 and meter #3 overlap (I briefly owned two houses), > and that seemed to confuse the lag() function: > > SELECT > electric_meter_pk, > lag(reading_date) > OVER(ORDER BY reading_date) as prev_date, > reading_date, > lag(meter_reading) > OVER(ORDER BY reading_date) AS prev_reading, > meter_reading, > meter_reading - lag(meter_reading) > OVER(ORDER BY reading_date) AS kWh_diff, > reading_date - lag(reading_date) > OVER(ORDER BY reading_date) as num_service_days > FROM electric > order by 1,3; > > 2 | 2009-04-09 | 2009-05-11 | 145595 | 146774 | 1179 |32 > 2 | 2009-05-11 | 2009-06-10 | 146774 | 148139 | 1365 |30 > 2 | 2009-06-26 | 2009-07-14 | 68502 | 149808 | 81306 |18 > 2 | 2009-07-14 | 2009-08-12 | 149808 | 151584 | 1776 |29 > 2 | 2009-09-12 | 2009-09-14 | 70934 | 152941 | 82007 | 2 > 3 | 2009-06-10 | 2009-06-26 | 148139 | 68502 |-79637 |16 > 3 | 2009-08-12 | 2009-08-13 | 151584 | 69738 |-81846 | 1 > 3 | 2009-08-13 | 2009-09-12 | 69738 | 70934 | 1196 |30 > 3 | 2009-09-14 | 2009-10-14 | 152941 | 71918 |-81023 |30 > 3 | 2009-10-14 | 2009-11-11 | 71918 | 72952 | 1034 |28 You can do … OVER(PARTITION BY electric_meter_pk ORDER BY reading_date) to split you data by meter. -- Victor Y. Yegorov -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
