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

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!

CodePudding user response：

I am afraid you cannot use window functions for this because range ... and interval '30 days' preceding is not supported. Efficiency aside, you can use old fashioned correlated subquery to get the desired result:

select curr.*, (
    select min(amount)
    from t as prev
    where prev.timestamp <= curr.timestamp - interval '30 days'
    -- and prev.id = curr.id
) as prev_min
from t as curr

CodePudding user response：

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.