Re: obtain the difference between successive rows

On 2012-10-20, Berend Tober <btober(at)broadstripe(dot)net> wrote:
> Thalis Kalfigkopoulos wrote:
>> On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>>> On 20/10/2012 11:54, ochaussavoine wrote:
>>>> I have a table 'tmvt' with a field 'created' in the row, and would like to
>>>> compute the difference between successive rows. The solution I found is:
>>>
>>> I think you can do it with a window function.
>>>
>>
>> In particular you're looking probably for the lag() window function.
>
> What about if there is more than one column you want the
> difference for (... coincidentally I am writing a article on this
> topic right now! ...), say a table which is used to record a
> metered quantity at not-quite regular intervals:
>
> CREATE TABLE electricity
> (
> current_reading_date date,
> current_meter_reading integer
> );
>
>
> with sample data:
>
>
> '2012-09-07',2158
> '2012-10-05',3018
>
>
>
> and I want an output such as:
>
>
> Meter Read on October 5
>
> Current Previous kWh
> Reading Reading Used
> -----------------------------------
> 3018 - 2158 = 860
>
> Number service days = 28
>
>
> I am working on a write-up of a neat solution using CTE's, but
> would be interested in other's views.

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

--
⚂⚃ 100% natural