主页 / dba / 问题 / 60537
Accepted
Slamice
Asked: 2014-03-10 17:29:07 +0800 CST

如何获得每个用户每个主题的最新五个分数？

  • 772

在我们的系统中有一个名为“scores”的 Postgres 表，它记录了测试分数。它有应试者（我们系统的用户）、0-10 分的分数、每次考试的部门和日期，如下所示：

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 
...

每个部门的用户可以有多个分数。

我想要的是：如果一个人在一个部门参加了 4 次以上的测试，则每个部门每个用户最后 5 个分数的平均值。

我觉得我几乎已经完成了这个查询：

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;

然而，分数低于 5 的部门出现了 :(。查询中一定有与窗口函数有关的错误，但我无法发现它......

有没有更好的方法来编写这个查询？

postgresql window-functions

1 个回答

  1. Best Answer

    你想要分数低于5的混合吗？如果你只想要 5 分之一，你可以在你的查询中添加一个 having：

        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

相关问题