Skip to content
Advertisement

dynamic rolling average in sql

Hope I can explain this properly

I’m trying to calculate a rolling average, but with a variable number of preceding rows, depending on a rolling sum of another column. Or in other words, I want to calculate the recent average of column b, over previous rows where column a sums to at least X

example data:

Desired result, with X = 3:

I’m not counting the a value from the current row, so row_to is always the previous row.

The third row just needs row 2

The fourth row needs rows 2 and 3, because row 2 isn’t >=3 by itself. I don’t mind going over the X value.

I can do the average over a varaible number of rows, but I don’t know how to calculate the row_from required. I imagine it’s easy in python, but is there a set based way to do this in TSQL?

I’m using PostgreSQL 10.5, but also could use SQLServer 2019

Advertisement

Answer

I cannot think of how to do this without recursion.

Except for the arithmetic error for the 16:00 entry ((19 + 25)/2 = 22), this produces your desired output:

Depending on the size of your table, this may not be practicable, performance-wise.

Working db<>fiddle.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement