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. 1130747 fiddles created (16586 in the last week).

-- test setup by Abelisto CREATE TABLE foo AS SELECT pkid , (random()* 100 - 50)::int AS numvalue , (random()* 1000)::int AS groupid FROM generate_series(1,100000) pkid; CREATE INDEX idx_foo ON foo(groupid, pkid); ANALYZE foo; -- aggregate function by Evan 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 ); -- plpgsql function by Abeilsto 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)


-- Evan's aggregate function EXPLAIN (ANALYZE, TIMING OFF) -- to see unburdened aboslute time 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.05 rows=100000 width=16) (actual rows=100000 loops=1)
-> Index Scan using idx_foo on foo (cost=0.29..4768.05 rows=100000 width=12) (actual rows=100000 loops=1)
Planning time: 0.150 ms
Execution time: 173.875 ms
 hidden batch(es)


-- dnoeth's query with 2 window functions EXPLAIN (ANALYZE, TIMING OFF) 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.05 rows=100000 width=28) (actual rows=100000 loops=1)
-> WindowAgg (cost=0.29..6518.05 rows=100000 width=20) (actual rows=100000 loops=1)
-> Index Scan using idx_foo on foo (cost=0.29..4768.05 rows=100000 width=12) (actual rows=100000 loops=1)
Planning time: 0.069 ms
Execution time: 134.166 ms
 hidden batch(es)


-- Abelisto's plpgsql function EXPLAIN (ANALYZE, TIMING OFF) SELECT (data).*, strange_sum FROM bar();
QUERY PLAN
Function Scan on bar (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1)
Planning time: 0.025 ms
Execution time: 167.098 ms
 hidden batch(es)


-- Erwin's query with 2 window functions EXPLAIN (ANALYZE, TIMING OFF) SELECT groupid, pkid , simple_sum - LEAST(MIN(simple_sum) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING), 0) AS rolling_sum FROM ( SELECT pkid, numvalue, groupid , SUM(numvalue) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING) AS simple_sum FROM foo ) sub;
QUERY PLAN
WindowAgg (cost=0.29..9518.05 rows=100000 width=16) (actual rows=100000 loops=1)
-> WindowAgg (cost=0.29..6518.05 rows=100000 width=20) (actual rows=100000 loops=1)
-> Index Scan using idx_foo on foo (cost=0.29..4768.05 rows=100000 width=12) (actual rows=100000 loops=1)
Planning time: 0.083 ms
Execution time: 129.010 ms
 hidden batch(es)


-- Erwin's plpgsql function CREATE OR REPLACE FUNCTION f_special_rolling_sum() RETURNS TABLE (groupid int, pkid int, rolling_sum int) AS $func$ DECLARE numvalue int; last_groupid int; BEGIN FOR groupid, pkid, numvalue IN SELECT f.groupid, f.pkid, f.numvalue FROM foo f ORDER BY f.groupid, f.pkid LOOP IF last_groupid = groupid THEN -- partition continues rolling_sum := GREATEST(rolling_sum + numvalue, 0); ELSE -- new partition (and first row) last_groupid := groupid; rolling_sum := GREATEST(numvalue, 0); END IF; RETURN NEXT; END LOOP; END $func$ LANGUAGE plpgsql;
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM f_special_rolling_sum();
QUERY PLAN
Function Scan on f_special_rolling_sum (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1)
Planning time: 0.033 ms
Execution time: 143.686 ms
 hidden batch(es)


CREATE INDEX idx_foo_covering ON foo(groupid, pkid, numvalue); -- faster, yet, with covering index
 hidden batch(es)


VACUUM foo;
 hidden batch(es)


-- Evan's aggregate function EXPLAIN (ANALYZE, TIMING OFF) -- to see unburdened aboslute time select pkid, groupid, add_pos_or_zero(numvalue) over (partition by groupid order by pkid) from foo;
QUERY PLAN
WindowAgg (cost=0.42..4802.42 rows=100000 width=12) (actual rows=100000 loops=1)
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)
Heap Fetches: 0
Planning time: 0.107 ms
Execution time: 171.277 ms
 hidden batch(es)


-- Abelisto's plpgsql function EXPLAIN (ANALYZE, TIMING OFF) SELECT (data).*, strange_sum FROM bar();
QUERY PLAN
Function Scan on bar (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1)
Planning time: 0.027 ms
Execution time: 144.257 ms
 hidden batch(es)


-- Erwin's plpgsql function EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM f_special_rolling_sum();
QUERY PLAN
Function Scan on f_special_rolling_sum (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1)
Planning time: 0.025 ms
Execution time: 103.555 ms
 hidden batch(es)


-- dnoeth's query EXPLAIN (ANALYZE, TIMING OFF) select dt.*, 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, sum(numvalue) over (partition by groupid order by pkid rows unbounded preceding) as cumsum from foo ) as dt;
QUERY PLAN
WindowAgg (cost=0.42..8302.42 rows=100000 width=28) (actual rows=100000 loops=1)
-> WindowAgg (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1)
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)
Heap Fetches: 0
Planning time: 0.092 ms
Execution time: 103.008 ms
 hidden batch(es)


-- Erwin's query EXPLAIN (ANALYZE, TIMING OFF) SELECT groupid, pkid , simple_sum - LEAST(MIN(simple_sum) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING), 0) AS rolling_sum FROM ( SELECT pkid, numvalue, groupid , SUM(numvalue) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING) AS simple_sum FROM foo ) sub;
QUERY PLAN
WindowAgg (cost=0.42..7802.42 rows=100000 width=16) (actual rows=100000 loops=1)
-> WindowAgg (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1)
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)
Heap Fetches: 0
Planning time: 0.091 ms
Execution time: 96.214 ms
 hidden batch(es)