CodingInCircles CodingInCircles - 21 days ago 4
SQL Question

How to find the sum of local maximas per day in Redshift?

I have some numeric data with ascending time stamps, as follows:

amount | received_at
_______|______________
30 | 2016-11-18 10:21:35 AM
60 | 2016-11-18 10:22:05 AM
90 | 2016-11-18 10:22:35 AM
120 | 2016-11-18 10:23:05 AM
150 | 2016-11-18 10:23:35 AM
160 | 2016-11-18 10:24:05 AM
0 | 2016-11-18 10:26:00 AM
20 | 2016-11-18 10:26:20 AM
40 | 2016-11-18 10:26:40 AM
55 | 2016-11-18 10:26:50 AM


I need to add 160 and 55 to get the total number. This is not limited to only two numbers, but for a given group of columns, can be multiple such maximas that need to be added, on a daily basis. Is there a straightforward (enough) solution for this?

I'm using Redshift to calculate this number.

Answer

Assuming that your table is create table t(amount int, received_at timestamptz);

1) Stored function:

create function foo() returns setof t language plpgsql immutable as $$
declare
  r t;
  c t;
begin
  r := null;
  for c in (select * from t order by received_at) loop
    if r is null or r.amount < c.amount then
      r := c;
    else
      return next r;
      r := null;
    end if;
  end loop;
  if r is not null then
    return next r;
  end if;
end $$;

select * from foo();

2) Window function:

with cte as (
  select
    amount,
    received_at,
    case 
      when coalesce(lead(amount) over (order by received_at), 0) < amount then 1
      else 0 
    end as flag
  from t)
select amount, received_at from cte where flag = 1;

Result:

╔════════╤════════════════════════╗
║ amount │      received_at       ║
╠════════╪════════════════════════╣
║    160 │ 2016-11-18 10:24:05+02 ║
║     55 │ 2016-11-18 10:26:50+02 ║
╚════════╧════════════════════════╝

Disclaimer: I am not sure what you want to do at the day's crossing.