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:

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:

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

Use avg as a window function with an explicit frame clause. SQL Fiddle

select *, avg(circle_time) 
over (partition by track order by id desc rows between CURRENT ROW and 2 following) as avg3_circle_time
from the_table;

CodePudding user response：

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