2012/10/20 Berend Tober <btober@broadstripe.net>:
> 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
