A window function with a custom frame comes to mind, like:
... sum(stock) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND _offset FOLLOWING)
But there are two issues:
_offset in the frame clause does not allow variables.
_offset in the frame clause does not process fractional numbers.
If you pass a numeric type with fractional part, it's rounded in the cast to integer. This is particularly problematic with floating point types like double precision, where 2.5 can round to 2 (!) due to inexact storage of the value. (I would not involve floating point types in these kinds of calculation to begin with!)
The manual:
In the offset PRECEDING and offset FOLLOWING frame
options, the offset must be an expression not containing any
variables, aggregate functions, or window functions. The meaning of
the offset depends on the frame mode:
- In
ROWS mode, the offset must yield a non-null, non-negative integer, and the option means that the frame starts or
ends the specified number of rows before or after the current row.
To work around issue 1, loop over items and execute dynamic SQL in PL/pgSQL.
To work around issue 2, truncate the upper bound, and add another value with lead() multiplied by the remainder:
Assuming referential integrity, and all columns not NOT NULL and values in the allowed range. Else you need to do more.
CREATE OR REPLACE FUNCTION my_rolling_sum(_items text[] = null)
RETURNS TABLE (
item text
, stock numeric
, date date
, cum_sum numeric
, lead_sum numeric
)
LANGUAGE plpgsql AS
$func$
DECLARE
_rule record;
_sql text;
BEGIN
FOR _rule IN
SELECT r.item
, any_value(r.value) FILTER (WHERE r.rule = 'cum_sum') AS cum_value
, any_value(r.value) FILTER (WHERE r.rule = 'lead_sum') AS lead_value
FROM rule r
WHERE (_items IS NULL OR r.item = ANY(_items))
GROUP BY r.item
ORDER BY r.item -- ? suppose that's the desired order?
LOOP
_sql := format(
$q$
SELECT s.item, s.stock, s.date
, round ( sum(s.stock) OVER (ORDER BY s.date ROWS BETWEEN CURRENT ROW AND %1$s FOLLOWING)
+ lead(s.stock, %1$s + 1, 0) OVER (ORDER BY s.date) * $1
, 2) -- AS cum_sum
, round (COALESCE(sum(s.stock) OVER (ORDER BY s.date ROWS BETWEEN 1 FOLLOWING AND %2$s FOLLOWING), 0)
+ lead(s.stock, %2$s + 1, 0) OVER (ORDER BY s.date) * $2
, 2) -- AS lead_sum
FROM stock s
WHERE s.item = %3$L
$q$
, trunc(_rule.cum_value) - 1
, trunc(_rule.lead_value)
, _rule.item
);
-- RAISE NOTICE '%', _sql; -- debug?
RETURN QUERY EXECUTE _sql -- execute
USING _rule.cum_value::numeric % 1
, _rule.lead_value::numeric % 1; -- % 1 gets fractional remainder
END LOOP;
END
$func$;
fiddle
Call for all items:
SELECT * FROM my_rolling_sum(); -- no argument
Call for given items:
SELECT * FROM my_rolling_sum('{Blade}');
Be sure to have an index on stock(item) to make this fast. Ideally on stock(item, date) INCLUDE (stock), but that may be to specialized.
The aggregate function any_value() was added with Postgres 16. Substitute with min() in older versions. (And remember to declare your version of Postgres in all questions.)
Produces your result.
There are subtleties with rounding, null-handling, sort order and display, which the question didn't clarify.
