Adicionar soma cumulativa à consulta de série temporal PostgreSQL 9.5

Normalmente, é mais rápido ler todas as linhas relevantes da tabela subjacente em uma única verificação.
Você pode executar uma função de janela sobre uma função agregada no mesmo arquivo SELECT.

Teste com EXPLAIN (ANALYZE, TIMING OFF)para verificar se é realmente mais rápido:

SELECT interval_ts
     , coalesce(revenue      , 0) AS campaign_revenue
     , coalesce(total_revenue, 0) AS total_campaign_revenue    
FROM   generate_series($2::timestamp, $3::timestamp, $4) AS interval_ts
LEFT   JOIN (
   SELECT date_trunc($4, created) AS interval_ts
        , sum(revenue)                                              AS revenue
        , sum(sum(revenue)) OVER (ORDER BY date_trunc($4, created)) AS total_running
   FROM   sale_event AS s
   WHERE  campaignid = $1
   AND    created <= $3                   -- read all relevant rows in one scan
   AND    event_type = 'session_closed'
   GROUP  BY date_trunc($4, created)
   ) results USING (interval_ts);

O JOINexclui linhas excedentes principais na subconsulta automaticamente.

sum(sum(revenue)) OVER (ORDER BY date_trunc($4, created))funciona porque, citando o manual :

A opção de enquadramento padrão é RANGE UNBOUNDED PRECEDING, que é a mesma que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Com ORDER BY, isso define o quadro para que todas as linhas da partição sejam iniciadas até o último ORDER BYpar da linha atual.

Exatamente o que você precisa.

Relacionado:

• Calculando a soma cumulativa no PostgreSQL
• PostgreSQL: executando contagem de linhas para uma consulta 'por minuto'
• Como obtenho a agregação de uma função de janela no Postgres?

Ponto fraco restante: o total está ausente para intervalos sem receita. Se isso não for aceitável, podemos usar esta técnica para corrigir:

• Carregue uma longa sequência de valores ausentes com o Postgres

SELECT interval_ts, campaign_revenue, total_revenue
     , coalesce(first_value(total_revenue) OVER (PARTITION BY grp ORDER BY interval_ts), 0) AS total_campaign_revenue    
FROM  (
   SELECT interval_ts
        , coalesce(revenue, 0) AS campaign_revenue
        , total_revenue
        , count(total_revenue) OVER (ORDER BY interval_ts) AS grp
   FROM  (
      SELECT interval_ts
           , coalesce(revenue, 0) AS campaign_revenue
           , count(total_revenue) OVER (ORDER BY interval_ts) AS grp
      FROM   generate_series($2::timestamp, $3::timestamp, $4) AS interval_ts
      LEFT   JOIN (
         SELECT date_trunc($4, created) AS interval_ts
              , sum(revenue) AS revenue
              , sum(sum(revenue)) OVER (ORDER BY date_trunc($4, created)) AS total_running
         FROM   sale_event AS s
         WHERE  campaignid = $1
         AND    created <= $3                   -- read all relevant rows in one scan
         AND    event_type = 'session_closed'
         GROUP  BY date_trunc($4, created)
         ) results USING (interval_ts)
      ) sub1
   ) sub2;

Com a sobrecarga adicional, não tenho certeza se pode competir. Ainda pode se sua seleção for pequena e a mesa for grande.

Notas menores:

• Você não precisa de parênteses em torno das condições de junção.

• Não chame seu carimbo de data/hora de "data". Isso é enganoso. Eu uso interval_tsem vez de interval_date.

• Prefiro não usar a palavra- chave SQL interval como alias de coluna - mesmo que isso seja permitido no Postgres.

• Trabalhar com o mesmo alias de coluna interval_tspara permitir a USINGsintaxe mais curta - o que requer parênteses. Isso expõe apenas uma instância das colunas unidas interval_tsà consulta externa, portanto, o nome não qualificado ainda não é ambíguo.

• Não omita a ASpalavra-chave para aliases de coluna (enquanto isso é bom para aliases de tabela).

Se eu acertar, você pode simplesmente adicionar uma função de janela fora da sua consulta, como:

SELECT interval_date, campaign_revenue
     , SUM(campaign_revenue) OVER (ORDER BY interval_date) 
      + (SELECT SUM(revenue) 
         FROM sale_event
         WHERE s.campaignid = $1
           AND s.created < $2
           AND s.event_type = 'session_closed') as total_campaign_revenue
FROM (
    SELECT interval_date
         , coalesce(campaign_revenue,0) AS campaign_revenue
    FROM
        -- generate_series helps fill the empty gaps in the following JOIN
        ...
        interval) results
    ON (results.interval = interval_date)
);

Outra opção é aplicar a função window diretamente e usar uma cláusula FILTER paracampaign_revenue