clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130805 fiddles created (16558 in the last week).

drop function if exists bar(); drop table if exists foo; drop aggregate if exists add_pos_or_zero(int); drop function if exists int_add_pos_or_zero(int, int); create table foo as select x as pkid, (random()*100-50)::int as numvalue, (random()*1000)::int groupid from generate_series(1,100000) as x; create index idx_foo on foo(groupid, pkid); analyse foo; create function int_add_pos_or_zero(int, int) returns int as $$ begin return greatest($1 + $2, 0); end; $$ language plpgsql immutable; create aggregate add_pos_or_zero(int) ( sfunc = int_add_pos_or_zero, stype = int, initcond = 0 ); drop function if exists bar(); create function bar() returns table (data foo, strange_sum int) language plpgsql as $$ declare g int; begin strange_sum := 0; g := -1; for data in (select * from foo order by groupid, pkid) loop if g <> data.groupid then g := data.groupid; strange_sum := 0; end if; strange_sum := greatest(strange_sum + data.numvalue, 0); return next; end loop; end $$
100000 rows affected
 hidden batch(es)


explain analyse select pkid, groupid, numvalue, add_pos_or_zero(numvalue) over (partition by groupid order by pkid) from foo;
QUERY PLAN
WindowAgg (cost=0.29..6518.28 rows=100000 width=16) (actual time=0.172..216.004 rows=100000 loops=1)
-> Index Scan using idx_foo on foo (cost=0.29..4768.28 rows=100000 width=12) (actual time=0.033..60.967 rows=100000 loops=1)
Planning time: 0.192 ms
Execution time: 221.219 ms
 hidden batch(es)


explain analyse select dt.*, -- find the lowest previous cumsum < 0 -- and adjust the current cumsum to zero max(case when cumsum < 0 then -cumsum else 0 end) over (partition by groupid order by pkid rows unbounded preceding) + cumsum as adjustedsum from ( select pkid, numvalue, groupid, -- calculate a standard cumulative sum sum(numvalue) over (partition by groupid order by pkid rows unbounded preceding) as cumsum from foo ) as dt;
QUERY PLAN
WindowAgg (cost=0.29..10018.28 rows=100000 width=28) (actual time=0.029..154.940 rows=100000 loops=1)
-> WindowAgg (cost=0.29..6518.28 rows=100000 width=20) (actual time=0.022..96.415 rows=100000 loops=1)
-> Index Scan using idx_foo on foo (cost=0.29..4768.28 rows=100000 width=12) (actual time=0.016..46.127 rows=100000 loops=1)
Planning time: 0.076 ms
Execution time: 160.405 ms
 hidden batch(es)


explain analyse select (data).*, strange_sum from bar();
QUERY PLAN
Function Scan on bar (cost=0.25..10.25 rows=1000 width=16) (actual time=151.188..182.898 rows=100000 loops=1)
Planning time: 0.025 ms
Execution time: 187.577 ms
 hidden batch(es)