CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-s3amazon-athena
Smasell
Smasell

Reputation: 1228

SQL Athena lookup function

I have my table like this one:

current_date conversion1 conversion2 conversion3
2021-10-01 0.5 0.7 0.9
2021-10-02 0.4 0.6 0.8
2021-10-03 0.3 0.5 0.0
2021-10-04 0.2 0.0 0.0

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:

current_date conversion1 conversion2 conversion3
2021-10-01 0.0 0.0 0.0
2021-10-02 0.5 0.0 0.0
2021-10-03 0.4 0.7 0.0
2021-10-04 0.3 0.6 0.9

Thanx for helping me!

Upvotes: 0

Views: 480

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1781

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

Upvotes: 2

Related Questions