Postgresql – Add cumulative sum to time-series query PostgreSQL 9.5

aggregatepostgresqlrunning-totalstime-series-database

I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval:

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

The query takes every row of sale_event table, truncates the created date to some interval (aligns the created timestamp with the time-series wanted granularity), groups by this time interval and sums up the revenue columns on the rows where event_type is session_closed.

This works very well and gives me the revenue in the specified interval. The result may look like:

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

When the provided range is 2018-08-05 - 2018-08-09 and interval = '1 day'.

I want to add to the result the sum of revenue up to that date. So if before 2018-08-05 there a total revenue of 10.0, the result would be:

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

Best Answer

If I get it right you can just add a window function outside of your query like:

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

Another option is to apply the window function directly, and use a FILTER clause for campaign_revenue

Related Question