Tengo una tabla t con 3 campos de interés: d (fecha), pid (int) y puntaje (numérico)
Estoy tratando de calcular un cuarto campo que es un promedio de los puntajes N (3 o 5) principales de cada jugador para los días anteriores a la fila actual.
Probé la siguiente unión en una subconsulta pero no produce los resultados que busco:
SELECT td, t.pid, t.score, sq.highscores FROM t, (SELECT *, avg(score) as highscores FROM (SELECT *, row_number() OVER w AS rnum FROM t AS t2 WINDOW w AS (PARTITION BY pid ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) isq WHERE rnum <= 3) sq WHERE td = sq.d AND t.pid = sq.pid¡Cualquier sugerencia sería muy apreciada! Soy un programador aficionado y esta es una consulta más compleja de lo que estoy acostumbrado.
No puede seleccionar * y avg(score) en la misma consulta (interna). Es decir, ¿qué valores no agregados deben seleccionarse para cada promedio? PostgreSQL no decidirá esto en tu lugar.
Debido a que PARTICIONA PARTITION BY pid en la consulta más interna, debe usar GROUP BY pid en la subconsulta de agregación. De esa manera, puede SELECT pid, avg(score) as highscores :
SELECT pid, avg(score) as highscores FROM (SELECT *, row_number() OVER w AS rnum FROM t AS t2 WINDOW w AS (PARTITION BY pid ORDER BY score DESC)) isq WHERE rnum <= 3 GROUP BY pid Nota : ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING no hace ninguna diferencia para row_number() .
Pero si la parte superior N es fija (y N también serán pocos en su caso de uso del mundo real), puede resolver esto sin tanta subconsulta (con la función de ventana nth_value() ):
SELECT d, pid, score, (coalesce(nth_value(score, 1) OVER w, 0) + coalesce(nth_value(score, 2) OVER w, 0) + coalesce(nth_value(score, 3) OVER w, 0)) / ((nth_value(score, 1) OVER w IS NOT NULL)::int + (nth_value(score, 2) OVER w IS NOT NULL)::int + (nth_value(score, 3) OVER w IS NOT NULL)::int) highscores FROM t WINDOW w AS (PARTITION BY pid ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)