Is the only way of achieving cumulative sum through materialized views?

TimescaleDB and PostgreSQL
, ,
1

Hey there, title says it all really.

The only cumulative sum is such a standard in time series handling that I find it hard to believe that we require the creation of a specific materialized views to achieve, never the less all the examples I see in the docs & community are pretty much around this approach which I would prefer to avoid.

CREATE MATERIALIZED VIEW response_times_five_min
WITH (timescaledb.continuous)
AS SELECT api_id,
    time_bucket('5 min'::interval, ts) as bucket,
    stats_agg(response_time)
FROM response_times
GROUP BY 1, 2;

SELECT bucket, 
	average(rolling(stats_agg) OVER last30), 
	stddev(rolling(stats_agg) OVER last30)
FROM response_times_five_min
WHERE api_id = 32
WINDOW last30 as 
(ORDER BY bucket RANGE '30 min' PRECEDING);

Anyone knows anything that I’m missing?

2

This should work:

WITH response_times_five_min
AS (SELECT api_id,
    time_bucket('5 min'::interval, ts) as bucket,
    stats_agg(response_time)
FROM response_times
GROUP BY 1, 2)
SELECT bucket, 
	average(rolling(stats_agg) OVER last30), 
	stddev(rolling(stats_agg) OVER last30)
FROM response_times_five_min
WHERE api_id = 32
WINDOW last30 as 
(ORDER BY bucket RANGE '30 min' PRECEDING);
3

This was great, thanks!

4

@sven Relating to your answer, that window as i understand it is a rolling window of 30 days

WINDOW last30 as (ORDER BY bucket RANGE '30 min' PRECEDING);

For a true cumulative sum, I’d like for it to have a fixed start date. To achieve something like:

[ (t_0, x_0), (t_1, x_0 + x_1), (t_2, x_0 + x_1 + x_2), ....]

In the above example the first entry of each tuple is a timesamp and the second a value. In this case the fixed start date would be t_0.

Do you have any suggestions for this? Really appreciate the help. I seem to be having some difficulty finding these examples in the docs or in the community :frowning:

I imagine it would look something like

WINDOW compliance_window as (ORDER BY bucket SINCE '2022-08-20 00:00:00');
5

Hmm not sure I understand what you are looking for. You are looking for the cumulative sum of the average of the 5 minute buckets?

Does this return what you are looking for?

WITH response_times_five_min AS (
  SELECT time_bucket('5min',ts), api_id, avg(response_time)
  FROM response_times GROUP BY 1,2
)
SELECT time_bucket, api_id, sum(avg) OVER (PARTITION BY api_id ORDER BY time_bucket) FROM response_times_five_min;
6

Hey, i really appreciate the help, that wasn’t exactly what I needed but I ended up figuring it out.

Turns out (i didnt know at least) that he window functionality is not timescaledb specific. It’s posgresql specific! So i read the docs reggarding window functionalities & saw some examples and ended up replacing the window logic with:

(...)
sum(rolling(ts.stats_agg) over compliance_window) as value
(...)
 WINDOW compliance_window as (ORDER BY bucket rows between UNBOUNDED PRECEDING AND current row)

That way if we specify a starting date with a where clause, the window will only move forward.

1 Like