My question is the same as the one asked here, except the chosen answer goes "assuming you want to restart the rolling average after each 15 minute interval." What if I don't? I.e. what if I want a different rolling average for every row and the next fifteen minutes of rows?
-
PG (up to 9.5 anyway) doesn't support this with a window function. See also stackoverflow.com/q/31396434/3304426 for related question and discussion.Patrick– Patrick2016-03-15 20:27:45 +00:00Commented Mar 15, 2016 at 20:27
-
It doesn't strictly need to be window function, I only tagged it like this because it sounded like a possible solution but apparently is not.Red– Red2016-03-15 20:40:34 +00:00Commented Mar 15, 2016 at 20:40
Add a comment
|
1 Answer
I would approach this as a correlated subquery:
select t.*,
(select avg(t2.col)
from t t2
where t2.timestamp >= t.timestamp and
t2.timestamp < t.timestamp + interval '15 minute'
) as future_15min_avg
from t;
This is challenging to do with window functions because the size of the window can change for each row.
There is an alternative approach which is more cumbersome but more efficient on larger amounts of data. It is probably going to work best using a temporary table. The idea is:
- Insert each timestamp with its value in the table
- Insert each timestamp plus 15 minutes in the table with a value of 0
- Add an index on timestamp
- Do a cumulative sum on the values
- Use two joins between the original table and this table to get the sum
You can try this with a CTE, and the performance might be ok. However, I think the index may be important for the performance. And, the correlated subquery is probably fine even for several hundreds or thousands of rows, assuming you have an index on the time column.
6 Comments
John Chrysostom
Yep. This or a self-join.
Red
I'd love to do a self-join but the table has a few million entries, that's not fast at all. But I'm not sure if a Window Function is better.
Gordon Linoff
@a_horse_with_no_name . . . As I understand it, "range" partitioning just affects the boundaries of the range, not the measurement units. So, if you go back three records and the value is "1" and the previous 2 are also "1", then they are included as well.
|