Sample Header Ad - 728x90

Add cumulative sum to time-series query PostgreSQL 9.5

2 votes
2 answers
6783 views
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
Asked by Alechko (229 rep)
Aug 10, 2018, 09:48 AM
Last activity: Apr 22, 2023, 08:24 AM