Início / dba / Perguntas / 60537
Accepted
Slamice
Asked: 2014-03-10 17:29:07 +0800 CST

Como posso obter as últimas cinco pontuações por usuário por assunto?

  • 772

Existe uma tabela do Postgres em nosso sistema chamada "pontuações" que registra as pontuações dos testes. Ele contém os examinandos (usuários do nosso sistema), pontuações de 0 a 10, departamentos e datas de cada teste feito, assim:

id  |  user_id  |  score  |  dept      | cdatetime
---------------------------------------------------------
1   |  123      |  5.5    | math       | 2014-01-01 12:00 
2   |  123      |  8.9    | chemistry  | 2014-02-01 03:20 
3   |  123      |  0.2    | physics    | 2014-01-01 09:00 
4   |  123      |  1.4    | math       | 2014-03-01 12:00 
5   |  456      |  9      | biology    | 2014-01-18 05:20 
...

Um usuário pode ter mais de uma pontuação por departamento.

O que eu quero é: Se uma pessoa fez mais de 4 testes em um depto, a média das últimas 5 notas por usuário por depto.

Sinto que estou quase lá com esta consulta:

SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
FROM  (
   SELECT *
         ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
  FROM mg.scores
  WHERE score IS NOT NULL) as score
   ) AS x
WHERE  x.rn <= 5
GROUP  BY user_id,dept;

No entanto, os depts com menos de 5 pontuações aparecem :(. Deve haver algo errado na consulta a ver com as funções da janela, mas não consigo identificar ...

Existe uma maneira melhor de escrever esta consulta?

postgresql window-functions

1 respostas

  1. Best Answer

    Você quer aqueles com menos de 5 pontuações no mix. Se você quiser apenas 5 pontuações, pode adicionar um tendo à sua consulta:

        SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;
    • 2

relate perguntas