Re: obtain the difference between successive rows

Berend Tober wrote:
> Raymond O'Donnell wrote:
>> On 20/10/2012 17:02, Berend Tober wrote:
>>> Thalis Kalfigkopoulos wrote:
>>> How would you get the previous reading (and perhaps the
>>> previous read
>>> date) to also appear ...
>>
>> Just include them in the SELECT:
>
> Well, that is surprisingly easy!
>
> How about this then: the table includes data for more than one
> meter....


Almost answering my own question. Adding the meter key to the lag:

SELECT
   electric_meter_pk,
   lag(reading_date)
      OVER(ORDER BY electric_meter_pk,reading_date)
      as prev_date,
   reading_date,
   lag(meter_reading)
      OVER(ORDER BY electric_meter_pk,reading_date)
      AS prev_reading,
   meter_reading,
   meter_reading - lag(meter_reading)
      OVER(ORDER BY electric_meter_pk,reading_date)
      AS kWh_diff,
   reading_date - lag(reading_date)
      OVER(ORDER BY electric_meter_pk,reading_date)
      as num_service_days
FROM home.electric
order by 1,3;

Gives all good as far as lining up dates, except it does not
cross the new-meter boundary gracefully:

  2 | 2009-07-14 | 2009-08-12 | 149808 | 151584 |    1776 | 29
  2 | 2009-08-12 | 2009-09-14 | 151584 | 152941 |    1357 | 33
*3 | 2009-09-14 | 2009-06-26 | 152941 |  68502 |  -84439 |-80
  3 | 2009-06-26 | 2009-08-13 |  68502 |  69738 |    1236 | 48
  3 | 2009-08-13 | 2009-09-12 |  69738 |  70934 |    1196 | 30
  ...
  3 | 2012-05-04 | 2012-06-07 | 116091 | 117469 |    1378 | 34
  3 | 2012-06-07 | 2012-07-06 | 117469 | 118953 |    1484 | 29
  3 | 2012-07-06 | 2012-07-18 | 118953 | 119185 |     232 | 12
*4 | 2012-07-18 | 2012-07-18 | 119185 |      0 | -119185 |  0
  4 | 2012-07-18 | 2012-08-06 |      0 |    887 |     887 | 19
  4 | 2012-08-06 | 2012-09-07 |    887 |   2158 |    1271 | 32
  4 | 2012-09-07 | 2012-10-05 |   2158 |   3018 |     860 | 28


The first-row-initialization problem is what lead me to consider
a recursive CTE. I have something that works and does not use
window functions, but I think it requires more detailed
explanation than I have prepared at this time.