Tengo una tarea complicada, supongamos que tenemos la tabla "Carreras", y allí tenemos las columnas PISTA, COCHE, CIRCLE_TIME aquí hay un ejemplo de cómo se verían los datos:
| identificación | pista | coche | círculo de tiempo |
|---|---|---|---|
| 10 | 1 | 10 | 15 |
| 9 | 1 | 10 | 14 |
| 8 | 1 | 10 | dieciséis |
| 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 |
lo que necesito, agregar una columna más como avg3_circle_time que me mostrará un tiempo promedio de los últimos 3 circle_time de cada pista, ejemplo:
| identificación | pista | coche | círculo de tiempo | avg3_circle_time |
|---|---|---|---|---|
| 10 | 1 | 10 | 15 | 15 |
| 9 | 1 | 10 | 14 | 15 |
| 8 | 1 | 10 | dieciséis | 14.6 |
| 7 | 1 | 10 | 15 | nulo |
| 6 | 1 | 10 | 13 | nulo |
| 5 | 2 | 10 | 7 | 5.3 |
| 4 | 2 | 10 | 4 | nulo |
| 3 | 2 | 10 | 5 | nulo |
| 2 | 3 | 10 | 8 | nulo |
| 1 | 3 | 10 | 10 | nulo |
Sé cómo podría funcionar en Oracle, podría usar algo como rowid, pero en el caso de postgresql no sé, tengo un borrador como .....avg(circle_time) OVER(PARTITION BY track,car.. ...) como avg3_circle_time... ayúdame a resolver esa tarea por favor
Puede usar funciones de ventana para calcular promedios móviles:
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 Dependiendo de su definición de las tres anteriores, la ventana podría ser ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING .
Si solo desea valores cuando haya al menos 3 círculos disponibles
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;Producción
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 nullUse LAED() y luego verifique que una de las siguientes 2 filas sea NULL o no. ENTONCES suma de tres valores para calcular el promedio.
-- 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) tOtra forma Usar 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 RacingsVerifique desde la URL donde existen ambas consultas https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f0cd868623725a1b92bf988cfb2deba3