title: PostgreSQL calculate rolling average with group and order
body: <p>I have a table as follows </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Logic is</p>
  
  <p>1) If the row is the 1st when grouped by id, the value should be the average</p>
  
  <p>2) The average should not include the current row</p>
  
  <p>Thanks in advance</p>