1

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?

Community
  • 1
  • 1
Pedro Carvalho
  • 565
  • 1
  • 6
  • 26
  • PG (up to 9.5 anyway) doesn't support this with a window function. See also http://stackoverflow.com/q/31396434/3304426 for related question and discussion. – Patrick Mar 15 '16 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. – Pedro Carvalho Mar 15 '16 at 20:40

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yep. This or a self-join. – John Chrysostom Mar 15 '16 at 19:29
  • 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. – Pedro Carvalho Mar 15 '16 at 19:32
  • "*This is challenging to do with window functions because the size of the window*" - theoretically not, but unfortunately Postgres does not allow to specify a `range` based on an interval as e.g. Oracle does. –  Mar 15 '16 at 19:42
  • @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. – Gordon Linoff Mar 15 '16 at 21:22
  • Well in Oracle you can e.g. do `sum(x) over (partition by ... order by date_column range between interval '15' minute and current row)` - not sure if this is part of the SQL standard though –  Mar 15 '16 at 21:26
  • @GordonLinoff: Could you elaborate on the temporary table solution, with code? The other solution is definitely way too costly, even if I limit my results to a single month it's been running for over an hour. – Pedro Carvalho Mar 15 '16 at 21:56