Re: obtain the difference between successive rows

Jasen Betts wrote:

> 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

Wrap-around can be handled pretty easily. It's meter replacement
that is a challenge.  :-)

SELECT
   current_reading_date as "reading date",
   lag(current_meter_reading, 1)
     over (order by current_reading_date) as "prior reading",
   current_meter_reading as "current reading",
   (1000000000 + current_meter_reading
     - lag(current_meter_reading, 1)
         over (order by current_reading_date)) % 1000000 as usage
 from electricity;

reading date | prior reading | current reading | usage
--------------+---------------+-----------------+-------
2012-05-07   |               |          997743 |      
2012-06-06   |        997743 |          999601 |  1858
2012-07-05   |        999601 |             338 |   737
2012-08-06   |           338 |            1290 |   952
2012-09-07   |          1290 |            2158 |   868
2012-10-05   |          2158 |            3018 |   860
(6 rows)

-Kevin