Re: obtain the difference between successive rows

Поиск
Список
Период
Сортировка
От Raymond O'Donnell
Тема Re: obtain the difference between successive rows
Дата
Msg-id 5082CD07.10108@iol.ie
обсуждение исходный текст
Ответ на Re: obtain the difference between successive rows  (Berend Tober <btober@broadstripe.net>)
Ответы Re: obtain the difference between successive rows
Список pgsql-general
On 20/10/2012 17:02, Berend Tober wrote:
> Thalis Kalfigkopoulos wrote:
>> 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;
>
> How would you get the previous reading (and perhaps the previous read
> date) to also appear on the same output row? The sample table with the
> subtraction I showed for illustration is literally what is printed on
> the bill ... they are not just presenting the quantity used and the
> number of days, but actually the dates and meter readings used to do the
> arithmetic.

Just include them in the SELECT:

SELECT
  lag(current_meter_reading) OVER(ORDER BY current_reading_date) AS
kWh_prev,
  current_meter_reading - lag(current_meter_reading) OVER(ORDER BY
current_reading_date) AS kWh_diff,

  (...etc...)


Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie


В списке pgsql-general по дате отправления:

Предыдущее
От: Berend Tober
Дата:
Сообщение: Re: obtain the difference between successive rows
Следующее
От: Tom Lane
Дата:
Сообщение: Re: obtain the difference between successive rows