Search code examples
sqlpostgresqlaveragewindow-functions

calculate avg(value) for last 10 records postgresql

i have a tricky task, lets assume we have table "Racings", and there we have columns TRACK, CAR, CIRCLE_TIME here is an example how data could be look like:

id track car circle_time
10 1 10 15
9 1 10 14
8 1 10 16
7 1 10 15
6 1 10 13
5 2 10 7
4 2 10 4
3 2 10 5
2 3 10 8
1 3 10 10

what i need, i to add one more coumn like avg3_circle_time which will show me an average time from last 3 circle_time from each track, example:

id track car circle_time avg3_circle_time
10 1 10 15 15
9 1 10 14 15
8 1 10 16 14.6
7 1 10 15 null
6 1 10 13 null
5 2 10 7 5.3
4 2 10 4 null
3 2 10 5 null
2 3 10 8 null
1 3 10 10 null

I know how it could works in oracle, you could use something like rowid, but in case of postgresql i don't know, i have a draft like .....avg(circle_time) OVER(PARTITION BY track,car.....) as avg3_circle_time..... help me to solve that task please

Solution

  • You can use window functions to calculate moving averages:

    SELECT track, id, car, circle_time, AVG(circle_time) OVER (
  PARTITION BY track
  ORDER BY id
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM t
ORDER BY track, id

    Depending on your definition of previous three, the window could be ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING.