mysql - 如何在MySQL中为每个事件找到最佳分数？

Question

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center。
6年前关闭。
我有一个MySQL表，其中包含运动员进行的一系列测试的数据我想为每一项赛事都取得最好的成绩。
以下是运动员所有测试的数据表：

+---------+-----------+-------+
| eventId | athleteId | score |
+---------+-----------+-------+
| 1       | 129907    | 900   |
| 2       | 129907    | 940   |
| 3       | 129907    | 927   |
| 4       | 129907    | 856   |
| 1       | 328992    | 780   |
| 2       | 328992    | 890   |
| 3       | 328992    | 936   |
| 4       | 328992    | 864   |
| 1       | 492561    | 899   |
| 2       | 492561    | 960   |
| 3       | 492561    | 840   |
| 4       | 492561    | 920   |
| 5       | 487422    | 900   |
| 6       | 487422    | 940   |
| 7       | 487422    | 927   |
| 5       | 629876    | 780   |
| 6       | 629876    | 890   |
| 7       | 629876    | 940   |
| 5       | 138688    | 899   |
| 6       | 138688    | 950   |
| 7       | 138688    | 840   |
+---------+-----------+-------+

我需要选择最好的标准阵容，参加最好的测试。我要找的结果应该是：

+---------+-----------+-------+
| eventId | athleteId | score |
+---------+-----------+-------+
| 1       | 129907    | 900   |
| 2       | 492561    | 960   |
| 3       | 328992    | 936   |
| 4       | 492561    | 920   |
| 5       | 487422    | 900   |
| 6       | 138688    | 950   |
| 7       | 629876    | 940   |
+---------+-----------+-------+

最佳答案

如果你想可靠地获得赢家（和联合赢家）。下面的SQL语句应该可以做到这一点。。。

SELECT athleteId, a.eventId, a.score
FROM tests AS a
JOIN (
  -- This select finds the top score for each event
  SELECT eventId, MAX(score) AS score
  FROM tests 
  GROUP BY eventId
) AS b
-- Join on the top scores
ON a.eventId = b.eventId
AND a.score = b.score

我正在执行子选择以获取每个事件的最高分数，然后执行内部联接以获取在该事件中获得最高分数的各个记录。
附加信息
我从评论中的对话中收集了以下信息。
为什么基本的分组解决方案不可靠？

SELECT athleteId, eventId, score
FROM (
  SELECT athleteId, eventId, score
  FROM tests
  ORDER BY eventId, score DESC
) AS a
GROUP BY eventId

我们正在根据已订购的事件和分数记录集创建一个组。然后，我们使用分组从列中选择值，以便为每个事件选择一条记录。
首先要注意的是
如果您使用的是 GROUP BY子句，那么您将不再讨论单个记录，而是一组无序的记录！
您可以使用聚合函数在MySQL http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html中执行一些非常强大和有用的跨记录计算，但是为了将组与单个记录关联起来，您可能需要执行 JOIN。
在第二个例子中，我们返回的组就好像它们是单独的记录一样。
为什么第二个例子看起来有用？
而不是在SQL语言中，非聚合列是非法的，在MySQL中它们是被允许的，尽管我不能解释原因，这可能是由于非规范化列中的性能原因，或者由于某种原因您确定组中列的值不会更改。
MySQL为组中的非聚合列选择最容易返回的值。它碰巧选择了它在分组之前由于记录集的顺序而遇到的第一个值，但是，它不一定总是这样做！
MySQL文档指出，包含 GROUP BY的select中的非聚合列的值是不确定的。这意味着，不应假定非聚合列的结果值是分组之前的事件的结果（即记录集中的任何顺序），尽管在当前的实现中实际上似乎是这样。
在将来的版本中，可能不是这样，甚至可能不是这样，如果您运行两次，结果甚至可能不相同。事实上，它是明确记录的理由，足以让我避免它！
为什么非聚合列不确定？
我可以推断，他们打算让用于分组的algos的实现为将来的优化打开，这可能会忽略或破坏分组之前记录的原始顺序。
从概念上讲，如果您将一组记录想象为一个单独的单元，而不是单个记录的集合，那么这是有意义的。对于非聚合列，有许多可能的值可以返回，并且在选择时没有隐含的条件来选择其中一个值，您必须记住分组之前记录的方式。
风险
我所有使用这种方法的查询可能在某个时候开始起作用。它们可能会返回未获得事件最高分数的记录的值。
而且，这个bug不会立即显现，因此跟踪MySQL最近升级的原因需要一段时间。我也可以保证我已经忘记了这个潜在的陷阱，当它发生的时候，所有的地方都是个问题，所以我很可能最终会被一个较老的不安全的MySQL版本困住，直到我有机会正确调试它。。。等。。。痛苦的。。。
为什么连接解决方案不同？
JOIN语句中的子select不使用非聚合列，聚合是确定的，因为它们与整个组而不是单个记录相关。不管这些记录在分组之前的顺序如何，答案总是一样的。
我使用了一个 JOIN语句将组与我们感兴趣的个人记录联系起来。在某些情况下，这可能意味着我对每个组都有一个以上的个人记录。举个例子，当两个运动员得分相同的平局时，我要么返回两个记录，要么任意选择一个。我很有信心，我们会希望所有的得分最高的人，所以我没有提供任何规则来选择两个运动员之间可能抽签。
选择一个记录作为赢家
为了选出一张明显胜出的唱片，我们需要一种能够区分胜出者和亚军的方法。我们可能会选出最终的赢家作为第一个获得最高得分的运动员，而另一个运动员要想跃入领先地位，他们必须比之前的得分集更好。
要做到这一点，我们必须有一种确定测试顺序的方法，所以我们引入一个 testId列，它将随着我们得到的每个新结果而递增。当我们有了这个，我们可以执行以下查询。。。

SELECT a.eventId, athleteId, a.score
FROM tests AS a
JOIN (
  -- This select finds the first testId for each score + event combination
  SELECT MIN(testId) AS testId, c.eventId, c.score
  FROM tests AS c
  JOIN (
    -- This select finds the top score for each event
    SELECT eventId, MAX(score) AS score
    FROM tests
    GROUP BY eventId
  ) AS d
  ON c.eventId = d.eventId
  AND c.score = d.score
  GROUP BY eventId, score
) AS b
ON a.testId = b.testId

这里发生的事情是，我们为每个事件创建代表最高分数的组，然后我们将其与代表每个分数和事件组合的最低测试的组进行内部连接，最后将其与测试表中的记录进行内部连接，以获取各个记录。
也可以按如下方式编写（执行计划略有不同）。

SELECT a.eventId, athleteId, a.score
FROM tests AS a
JOIN (
  -- This select finds the top score for each event
  SELECT eventId, MAX(score) AS score
  FROM tests
  GROUP BY eventId
) AS b
ON a.eventId = b.eventId
AND a.score = b.score
JOIN (
  -- This select finds the first testId for each score + event combination
  SELECT MIN(testId) AS testId, eventId, score
  FROM tests
  GROUP BY eventId, score
) AS c
ON a.testId = c.testId

基本的group by解决方案在更少的SQL中实现了相同的结果，但相比之下，它的优化效果非常差。如果我们将索引添加到表中，则基本的group by解决方案不使用索引，并且需要对tests表中的所有记录执行两个文件队列（在表中额外运行以将其排序）。但是，上面的原始嵌套子选择查询优化得非常好。

关于mysql - 如何在MySQL中为每个事件找到最佳分数？ ，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14068917/

上一篇：php - 试图让 PHP 和数据库协同工作

下一篇：php - 准备语句只准备一次？

相关文章：

MySqlException : Access denied for user 'altjenb' @'localhost' (using password: YES)

MySQL 端口 3306 与 3307 差异

php - 显示mysql数据库字段

sql - 如何将字段内的值总和分配给变量？

mysql - 聚合 SQL 查询

php - 将数据导出为 .sql 格式。如何逃脱？

mysql - Mysql数据库表中的日期时间和时间戳

sql - 截断的 LISTAGG 字符串

mysql - 升级后mysql错误如何解决

scala - 如何定义自定义聚合函数来对向量列求和？

Answer 1

如果你想可靠地获得赢家（和联合赢家）。下面的SQL语句应该可以做到这一点。。。

SELECT athleteId, a.eventId, a.score
FROM tests AS a
JOIN (
  -- This select finds the top score for each event
  SELECT eventId, MAX(score) AS score
  FROM tests 
  GROUP BY eventId
) AS b
-- Join on the top scores
ON a.eventId = b.eventId
AND a.score = b.score

我正在执行子选择以获取每个事件的最高分数，然后执行内部联接以获取在该事件中获得最高分数的各个记录。
附加信息
我从评论中的对话中收集了以下信息。
为什么基本的分组解决方案不可靠？

SELECT athleteId, eventId, score
FROM (
  SELECT athleteId, eventId, score
  FROM tests
  ORDER BY eventId, score DESC
) AS a
GROUP BY eventId

我们正在根据已订购的事件和分数记录集创建一个组。然后，我们使用分组从列中选择值，以便为每个事件选择一条记录。
首先要注意的是
如果您使用的是 GROUP BY子句，那么您将不再讨论单个记录，而是一组无序的记录！
您可以使用聚合函数在MySQL http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html中执行一些非常强大和有用的跨记录计算，但是为了将组与单个记录关联起来，您可能需要执行 JOIN。
在第二个例子中，我们返回的组就好像它们是单独的记录一样。
为什么第二个例子看起来有用？
而不是在SQL语言中，非聚合列是非法的，在MySQL中它们是被允许的，尽管我不能解释原因，这可能是由于非规范化列中的性能原因，或者由于某种原因您确定组中列的值不会更改。
MySQL为组中的非聚合列选择最容易返回的值。它碰巧选择了它在分组之前由于记录集的顺序而遇到的第一个值，但是，它不一定总是这样做！
MySQL文档指出，包含 GROUP BY的select中的非聚合列的值是不确定的。这意味着，不应假定非聚合列的结果值是分组之前的事件的结果（即记录集中的任何顺序），尽管在当前的实现中实际上似乎是这样。
在将来的版本中，可能不是这样，甚至可能不是这样，如果您运行两次，结果甚至可能不相同。事实上，它是明确记录的理由，足以让我避免它！
为什么非聚合列不确定？
我可以推断，他们打算让用于分组的algos的实现为将来的优化打开，这可能会忽略或破坏分组之前记录的原始顺序。
从概念上讲，如果您将一组记录想象为一个单独的单元，而不是单个记录的集合，那么这是有意义的。对于非聚合列，有许多可能的值可以返回，并且在选择时没有隐含的条件来选择其中一个值，您必须记住分组之前记录的方式。
风险
我所有使用这种方法的查询可能在某个时候开始起作用。它们可能会返回未获得事件最高分数的记录的值。
而且，这个bug不会立即显现，因此跟踪MySQL最近升级的原因需要一段时间。我也可以保证我已经忘记了这个潜在的陷阱，当它发生的时候，所有的地方都是个问题，所以我很可能最终会被一个较老的不安全的MySQL版本困住，直到我有机会正确调试它。。。等。。。痛苦的。。。
为什么连接解决方案不同？
JOIN语句中的子select不使用非聚合列，聚合是确定的，因为它们与整个组而不是单个记录相关。不管这些记录在分组之前的顺序如何，答案总是一样的。
我使用了一个 JOIN语句将组与我们感兴趣的个人记录联系起来。在某些情况下，这可能意味着我对每个组都有一个以上的个人记录。举个例子，当两个运动员得分相同的平局时，我要么返回两个记录，要么任意选择一个。我很有信心，我们会希望所有的得分最高的人，所以我没有提供任何规则来选择两个运动员之间可能抽签。
选择一个记录作为赢家
为了选出一张明显胜出的唱片，我们需要一种能够区分胜出者和亚军的方法。我们可能会选出最终的赢家作为第一个获得最高得分的运动员，而另一个运动员要想跃入领先地位，他们必须比之前的得分集更好。
要做到这一点，我们必须有一种确定测试顺序的方法，所以我们引入一个 testId列，它将随着我们得到的每个新结果而递增。当我们有了这个，我们可以执行以下查询。。。

SELECT a.eventId, athleteId, a.score
FROM tests AS a
JOIN (
  -- This select finds the first testId for each score + event combination
  SELECT MIN(testId) AS testId, c.eventId, c.score
  FROM tests AS c
  JOIN (
    -- This select finds the top score for each event
    SELECT eventId, MAX(score) AS score
    FROM tests
    GROUP BY eventId
  ) AS d
  ON c.eventId = d.eventId
  AND c.score = d.score
  GROUP BY eventId, score
) AS b
ON a.testId = b.testId

这里发生的事情是，我们为每个事件创建代表最高分数的组，然后我们将其与代表每个分数和事件组合的最低测试的组进行内部连接，最后将其与测试表中的记录进行内部连接，以获取各个记录。
也可以按如下方式编写（执行计划略有不同）。

SELECT a.eventId, athleteId, a.score
FROM tests AS a
JOIN (
  -- This select finds the top score for each event
  SELECT eventId, MAX(score) AS score
  FROM tests
  GROUP BY eventId
) AS b
ON a.eventId = b.eventId
AND a.score = b.score
JOIN (
  -- This select finds the first testId for each score + event combination
  SELECT MIN(testId) AS testId, eventId, score
  FROM tests
  GROUP BY eventId, score
) AS c
ON a.testId = c.testId

基本的group by解决方案在更少的SQL中实现了相同的结果，但相比之下，它的优化效果非常差。如果我们将索引添加到表中，则基本的group by解决方案不使用索引，并且需要对tests表中的所有记录执行两个文件队列（在表中额外运行以将其排序）。但是，上面的原始嵌套子选择查询优化得非常好。