I have my table like this one:

And I want to move my data one down according dates (like excel's lookup function).

1 day down for column conversion1

2 days down for column conversion2

3 days down for column conversion3

Desired output should look like this:

Thanx for helping me!

CodePudding user response：

Have you tried using LAG?

https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html

Something like this should work

Select Statement

SELECT date,
       coalesce(lag(conversion1) over (order by date), 0) conversion1,
       coalesce(lag(conversion2, 2) over (order by date), 0) conversion2,
       coalesce(lag(conversion3, 3) over (order by date), 0) conversion3
FROM TBL

Schema Build

  tbl (
    Date date,
    conversion1 float,
    conversion2 float,
    conversion3 float
  );

INSERT INTO
  tbl
VALUES
  ('2020-10-01', 0.5, 0.7, 0.9),
  ('2020-10-02', 0.4, 0.6, 0.8),
  ('2020-10-03', 0.3, 0.5, 0),
  ('2020-10-04', 0.2, 0, 0)

SQL Fiddle (Postgres): http://sqlfiddle.com/#!17/8bd32/4