Soma contínua com valor parcial do último dia

Uma função de janela com uma moldura personalizada vem à mente, como:

... sum(stock) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND _offset FOLLOWING)

Mas há duas questões :

1. _offseta cláusula in the frame não permite variáveis.

2. _offseta cláusula in the frame não processa números fracionários. Se você passar um tipo numérico com parte fracionária, ele será arredondado na conversão para inteiro. Isso é particularmente problemático com tipos de ponto flutuante como double precision, onde 2,5 pode ser arredondado para 2 (!) devido ao armazenamento inexato do valor. (Eu não envolveria tipos de ponto flutuante nesses tipos de cálculo para começar!)

O manual:

Nas opções de quadro and , the deve ser uma expressão que não contenha nenhuma variável, função de agregação ou função de janela. O significado de the depende do modo de quadro:offset PRECEDINGoffset FOLLOWINGoffsetoffset

• No ROWSmodo, o offsetdeve produzir um inteiro não nulo e não negativo, e a opção significa que o quadro inicia ou termina o número especificado de linhas antes ou depois da linha atual.

Para contornar o problema 1, faça um loop nos itens e execute SQL dinâmico em PL/pgSQL.

Para contornar o problema 2, trunque o limite superior e adicione outro valor lead()multiplicado pelo restante:

Assumindo integridade referencial, e todas as colunas não NOT NULLe valores no intervalo permitido. Senão, você precisa fazer mais.

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$;

violino

Solicite todos os itens:

SELECT * FROM my_rolling_sum();  -- no argument

Solicitação de itens fornecidos:

SELECT * FROM my_rolling_sum('{Blade}');

Certifique-se de ter um índice stock(item)para tornar isso rápido. Idealmente em stock(item, date) INCLUDE (stock), mas isso pode ser muito especializado.

A função agregada any_value()foi adicionada com o Postgres 16. Substitua por min()em versões mais antigas. (E lembre-se de declarar sua versão do Postgres em todas as perguntas.)

Produz seu resultado. Há sutilezas com arredondamento, tratamento de nulos, ordem de classificação e exibição, que a pergunta não esclareceu.