Re: obtain the difference between successive rows

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. I moved (I've been keeping this data for two decades ...
yes, I know...) to a new house, and in the new house, the utility
company has replaced the meter (one of those "smart" meters). So
the table has a foreign key reference to the primary key
identifying the meter:

CREATE TABLE electricity
(
   electric_meter_pk integer,
   current_reading_date date,
   current_meter_reading integer
);

with sample data:

  2 | 1997-04-14   |    0
  2 | 1997-05-08   |  573
  2 | 1997-06-12   | 1709
  ...
  2 | 2009-09-14   |152941
  3 | 2009-06-26   | 68502
  3 | 2009-08-13   | 69738
...
  3 | 2012-07-06   |118953
  3 | 2012-07-18   |119185
  4 | 2012-07-18   |     0
  4 | 2012-08-06   |   887
  4 | 2012-09-07   |  2158
  4 | 2012-10-05   |  3018


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