为每个用户选择前三个分数的总和

I am having trouble writing a query for the following problem. I have tried some existing queries but cannot get the results I need.

我在编写查询以解决以下问题时遇到问题。我尝试了一些现有的查询,但无法获得我需要的结果。

I have a results table like this:

我有一个这样的结果表:

userid  score timestamp
1       50    5000
1       100   5000 
1       400   5000 
1       500   5000 
2       100   5000 
3       1000  4000

The expected output of the query is like this:

查询的预期输出如下:

userid  score
3       1000
1       1000
2       100

I want to select a top list where I have n best scores summed for each user and if there is a draw the user with the lowest timestamp is highest. I really tried to look at all old posts but could not find one that helped me.

我想选择一个*列表,其中我为每个用户总结了n个最佳分数,如果有抽奖,则具有最低时间戳的用户最高。我真的试着查看所有旧帖子,但找不到帮助我的帖子。

Here is what I have tried:

这是我尝试过的:

SELECT sum(score) FROM (
   SELECT score 
   FROM results 
   WHERE userid=1 ORDER BY score DESC LIMIT 3
) as subquery

This gives me the results for one user, but I would like to have one query that fetches all in order.

这给了我一个用户的结果,但我希望有一个查询按顺序获取所有用户。

5 个解决方案

#1

This is a pretty typical greatest-n-per-group problem. When I see those, I usually use a correlated subquery like this:

这是一个非常典型的最大n组问题。当我看到这些时,我通常会使用这样的相关子查询:

SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mT
  WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3;

This is not the whole solution, as it only gives you the top three scores for each user in its own row. To get the total, you can use SUM() wrapped around that subquery like this:

这不是整个解决方案,因为它只为您自己行中的每个用户提供前三个分数。要获得总数,可以使用包围该子查询的SUM(),如下所示:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId;

Here is an SQL Fiddle example.

这是一个SQL小提琴示例。

EDIT

Regarding the ordering (which I forgot the first time through), you can just order by totalScore in descending order, and then by MIN(timestamp) in ascending order so that users with the lowest timestamp appears first in the list. Here is the updated query:

关于排序(我第一次忘记了),您可以按降序排列totalScore,然后按升序排列MIN(时间戳),以便具有最低时间戳的用户首先出现在列表中。这是更新的查询:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

and here is an updated Fiddle link.

这是一个更新的Fiddle链接。

EDIT 2

As JPW pointed out in the comments, this query will not work if the user has the same score for multiple questions. To settle this, you can add an additional condition inside the subquery to order the users three rows by timestamp as well, like this:

正如JPW在评论中指出的那样,如果用户对多个问题具有相同的分数,则此查询将不起作用。要解决这个问题,您可以在子查询中添加一个附加条件,以便按用时间戳为用户排序三行,如下所示:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score 
      AND mT.timeCol <= m.timeCol) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

I am still working on a solution to find out how to handle the scenario where the userid, score, and timestamp are all the same. In that case, you will have to find another tiebreaker. Perhaps you have a primary key column, and you can choose to take a higher/lower primary key?

我仍在研究解决方案,以了解如何处理userid,score和timestamp都相同的场景。在这种情况下,你将不得不找到另一个决胜局。也许你有一个主键列,你可以选择更高/更低的主键吗?

#2

Query for selecting top three scores from table.

查询从表中选择前三个分数。

SELECT score FROM result GROUP BY id ORDER BY score DESC LIMIT 3;

SELECT得分FROM结果GROUP BY id ORDER BY得分DESC LIMIT 3;

#3

Can you please try this?

你能试试吗?

SELECT score FROM result GROUP BY id ORDER BY score DESC, timestamp ASC LIMIT 3;

if 2 users have same score then it will set order depends on time.

如果2个用户具有相同的分数,那么它将根据时间设置顺序。

#4

You can use a subquery

您可以使用子查询

SELECT r.userid,
(  SELECT sum(r2.score)
   FROM results r2
   WHERE r2.userid = r.userid 
   ORDER BY score DESC 
   LIMIT 3
) as sub
FROM result r
GROUP BY r.userid
ORDER BY sub desc

#5

You should do it like this

你应该这样做

SELECT SUM(score) as total, min(timestamp) as first, userid FROM scores
GROUP BY userid
ORDER BY total DESC, first ASC

This is way more efficient than sub queries. If you want to extract more fields than userid, then you need to add them to the group by.

这比子查询更有效。如果要提取比userid更多的字段,则需要将它们添加到组中。

This will of cause not limit the number of scores pr user, which indeed seems to require a subquery to solve.

这将导致不限制用户的分数,这确实似乎需要子查询来解决。

#1