sql - Creating a Separate Column for Prior Week Values (PostgreSQL)

Question

How would I go about having a separate column that shows the prior week's value? For example, if Product A's value for 01/03/2021 was 100, I would like 01/10/2021 to show its date value as well as the 01/03/2021 value in a separate column.

Desired table below (for simplicity sake I added random numbers for the prior week values for 01/03 and 01/04):

Date	Product	Value	Prior Week Value
01/03/2021	Product A	100	50
01/04/2021	Product A	200	55
01/10/2021	Product A	600	100
01/11/2021	Product A	700	200
01/03/2021	Product B	250	40
01/04/2021	Product B	550	45
01/10/2021	Product B	460	250
01/11/2021	Product B	100	550

标签： sqlpostgresql

Answer 1

If you want exactly 7 days before, you can use window functions with a range specification:

select t.*,
       max(value) over (partition by product
                        order by date
                        range between '7 day' preceding and '7 day' preceding
                       ) as value_prev_week
from t;