Re: obtain the difference between successive rows - Mailing list pgsql-general

From Jasen Betts
Subject Re: obtain the difference between successive rows
Date
Msg-id k8orvi$f35$1@reversiblemaps.ath.cx
Whole thread Raw
In response to obtain the difference between successive rows  (ochaussavoine <olivier.chaussavoine@gmail.com>)
List pgsql-general
On 2012-10-20, Berend Tober <btober@broadstripe.net> wrote:
> Thalis Kalfigkopoulos wrote:
>> On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell <rod@iol.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

pgsql-general by date:

Previous
From: Vlad
Date:
Subject: Re: High SYS CPU - need advise
Next
From: "Kevin Grittner"
Date:
Subject: Re: obtain the difference between successive rows