CodingInCircles - 1 year ago 83
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.

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,
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download