Home>

When there is a table like the following,
To extract the difference from the previous day (get the previous record, not the actual date)
What should I do?

* Table definition *
create table uriage
(date DATE NOT NULL,
price double precision);

date price
2010-01-01 100
2010-01-02 120
2010-01-05 130

Supplemental information (FW/tool version etc.)

Postgresql 11 is assumed.

  • Answer # 1

    lag is for the purpose.

    select price-lag (price) over (order by date) as difference from table

    In practice, you will also usepartition by.