Re: obtain the difference between successive rows

From: Berend Tober <btober(at)broadstripe(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: obtain the difference between successive rows
Date: 2012-10-20 15:21:47
Message-ID: 5082C18B.8020106@computer.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Berend Tober 2012-10-20 15:30:23 Re: obtain the difference between successive rows
Previous Message Thalis Kalfigkopoulos 2012-10-20 13:06:27 Re: obtain the difference between successive rows