> 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
No problem with that either.
$ SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER
BY current_reading_date) AS kWh_diff, extract('days' FROM
current_reading_date - lag(current_reading_date) OVER(ORDER BY
current_reading_date)) as num_service_days FROM mytable;
Note how ORDER BY is in both cases done by current_reading_date. This
is because the current_reading_date defines the concept of
previous/next row whose values (either current_meter_reading or
current_reading_date) I want to be comparing.
regards,
Thalis
