將累積和添加到時間序列查詢 PostgreSQL 9.5

我編寫了查詢，它為我提供了某個日期範圍和間隔的時間序列，顯示每個時間間隔的收入：

SELECT
   interval_date,
   coalesce(campaign_revenue,0) AS campaign_revenue,
FROM
   -- generate_series helps fill the empty gaps in the following JOIN
   generate_series(
       $2::timestamp,
       $3::timestamp,
       $4) AS interval_date -- could be '1 day', '1 hour' or '1 minute'.
LEFT OUTER JOIN
   -- This SELECT gets all timeseries rows that have data
   (SELECT
       date_trunc($4, s.created) AS interval,
       SUM(s.revenue) campaign_revenue
   FROM
       sale_event AS s
   WHERE
       s.campaignid = $1 AND s.created BETWEEN $2 AND $3 AND s.event_type = 'session_closed'
   GROUP BY
       interval) results
ON
   (results.interval = interval_date);

查詢獲取表的每一行sale_event，將創建的日期截斷為某個時間間隔（將created時間戳與所需的時間序列粒度對齊），按此時間間隔進行分組，並對行中的列revenue求和。event_type``session_closed

這非常有效，並在指定的時間間隔內為我提供了收入。結果可能如下所示：

interval_date   |   campaign_revenue
------------------------------------
2018-08-05     |   0.0
2018-08-06     |   1.5
2018-08-07     |   0.0
2018-08-08     |   0.5
2018-08-09     |   1.0

當提供的範圍是2018-08-05 - 2018-08-09和時interval = '1 day'。

我想將截至該日期的收入總和添加到結果中。因此，如果在2018-08-05總收入為之前10.0，結果將是：

interval_date   |   campaign_revenue   |   total_campaign_revenue
-----------------------------------------------------------------
2018-08-05     |   0.0                |   10.0
2018-08-06     |   1.5                |   11.5
2018-08-07     |   0.0                |   11.5
2018-08-08     |   0.5                |   12.0
2018-08-09     |   1.0                |   13.0

如果我做對了，您可以在查詢之外添加一個視窗函式，例如：

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

另一種選擇是直接應用視窗函式，並使用 FILTER 子句campaign_revenue

引用自：https://dba.stackexchange.com/questions/214577