calculate avg(value) for last 10 records postgresql

user_9897722 0
Viewed 414

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

window-functions postgresql average sql
4 Answers

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.

user_870150

If you want only values when at least 3 circles available

select *
 , case when lag(id, 2) over(partition by TRACK, CAR order by id) is not null then 
        avg(CIRCLE_TIME) over(partition by TRACK, CAR order by id rows between 2 preceding and current row) end a
from Racing 
order by id desc;

db<>fiddle

Output

id  track   car circle_time a
10  1   10  15  15.0000000000000000
9   1   10  14  15.0000000000000000
8   1   10  16  14.6666666666666667
7   1   10  15  null
6   1   10  13  null
5   2   10  7   5.3333333333333333
4   2   10  4   null
3   2   10  5   null
2   3   10  8   null
1   3   10  10  null
user_62199790

Use LAED() then checking one of the next 2 rows is NULL or not. THEN sum of three values for calculating average.

-- PostgreSQL
SELECT *
     , CASE WHEN next_circle_time IS NULL OR next_next_circle_time IS NULL
               THEN NULL
            ELSE ((t.circle_time + COALESCE(next_circle_time, 0) + COALESCE(next_next_circle_time, 0)) / 3 :: DECIMAL) :: DECIMAL(10, 1)
        END avg_circle_time
FROM (SELECT *
           , LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) next_circle_time
           , LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) next_next_circle_time
      FROM Racings) t

Another way Use AVG()

SELECT *
     , CASE WHEN LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
                 OR LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
               THEN NULL
            ELSE AVG(circle_time) OVER (PARTITION BY track ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
       END :: DECIMAL(10, 2) avg_circle_time       
FROM Racings

Please check from url where both query exists https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f0cd868623725a1b92bf988cfb2deba3

user_163646250

Several of the posted answers end up repeating the window definition. You can avoid this with the window clause:

select *, 
   case when row_number() over(track_window) > 2 
        then trunc(avg(CIRCLE_TIME) over(track_window rows 2 preceding), 1)
   end a
from Racing 
window track_window as (partition by track order by id)
order by id desc

Note how, in this sample, track_window is defined once, then reused for both row_number and avg. In the latter case, the window clause is embellished with a frame as well (rows 2 preceding).

user_1083260
Related