I have a table as follows
id | x | y | value
------+--------+-------+------------
1 | 1 | 1 | 25
1 | 1 | 2 | 42
1 | 2 | 3 | 98
1 | 2 | 4 | 54
1 | 3 | 5 | 67
2 | 1 | 1 | 78
2 | 1 | 2 | 45
2 | 2 | 3 | 96
I have to group this by id while maintaining the order by id, x, and y (in the respective order) and calculate the rolling average for previous n number of rows. For example if n = 3
id | x | y | value | rollingAvg
------+--------+-------+--------+-----------
1 | 1 | 1 | 25 | 25
1 | 1 | 2 | 42 | (25 / 1) = 25
1 | 2 | 3 | 98 | (25+42/2) = 33.5
1 | 2 | 4 | 54 | (25+42+98 /3) = 55
1 | 3 | 5 | 67 | (42+98+54 /3) = 64.67
2 | 1 | 1 | 78 | 78
2 | 1 | 2 | 45 | (78/1) = 78
2 | 2 | 3 | 96 | (78+45 / 2) = 61.5
Logic is
1) If the row is the 1st when grouped by id, the value should be the average
2) The average should not include the current row
Thanks in advance