CODEWITHSUNDEEP
sqlamazon-redshift
PeeteKeesel
PeeteKeesel

Reputation: 772

How to get the minimal value for all rows before x days in the past in Redshift

Let's say that I have the following table tbl with all rows having the same id to simplify things

timestamp   amount
------------------
01-01-2021  10      
02-01-2021  15      
03-01-2021  11      
02-02-2021  20      
01-04-2021  9

I want as a new column prev_min the minimal amount for all orders which are at least x days in the past. This would result in:

timestamp   amount   prev_min
--------------------------------
01-01-2021  10       NULL
02-01-2021  15       NULL
03-01-2021  11       NULL
02-02-2021  20       10           -- since 11 & 15 are not > 30 days in the past
01-04-2021  9        11           -- since 11 is > 30 days in the past

My approach is something like that

SELECT 
    MIN(CASE WHEN timestamp < timestamp - INTERVAL '30 DAYS' 
        THEN amount 
        ELSE 0 END) 
    OVER (
        PARTITION BY id
        ORDER BY timestamp ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND 30 PRECEDING
    ) AS prev_min
FROM tbl

I know that the CASE WHEN ... is wrong. And I also know that the 30 PRECEDING is starting from 30 rows before the current row and thus is also wrong. And I don't want e.g. 30 rows in the past but rather 30 days in the past. I can't use RANGE however. So is there a way to do that without using RANGE?

Thanks!

Upvotes: 0

Views: 730

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

You're going to need to have rows for every date so you can use the frame clause of the window function the way you want. I used a recursive CTE to generate all the dates of 2021 but you can expand this as you need.

Test case:

Setup:

create table t (dt date, amount int);
  
insert into t values  
('01-01-2021', 10),     
('01-02-2021', 15),    
('01-03-2021', 11),    
('02-02-2021', 8),    
('04-01-2021', 9);

Query:

with recursive dates(d) as
( select '2021-01-01'::date as d
    union all
    select (d + 1)::date as d
    from dates d
    where d.d <= '2021-12-31'::date
    )
select dt, amount, prev_min
from (
  select dt, sum(amount) as amount, max(orig) as orig,
  min(sum(amount)) over (
        ORDER BY dt ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND 30 PRECEDING) as prev_min
  from (
    select dt, amount, 1 as orig from t 
    union all
    select d as dt, null as amount, 0 as orig from dates
  ) as a
  group by dt
) b
where orig = 1
order by dt
;

Adding in your id partition and any other specifics should be straight forward from here.

Upvotes: 1

Related Questions