不理解从每个组中检索前n条记录的查询是如何工作的

Question

我遇到了一个问题，我试图从数据库中的每个组(天)或记录中获取前'n'条记录。经过一番挖掘，我找到了一些很好的答案，它们确实解决了我的问题。

• 如何选择每组的前N行
• 获取每组分组结果的前n条记录

然而，我的无知使我无法准确理解这些"计数"解决方案的工作原理。如果有一个SQL知识更好的人能够解释，那就太好了。

编辑：这里有更多的细节

假设我有一个下面描述的表格，里面有这个样本数据。(为了更简单，我有一个专栏记录下一个即将到来的午夜的时间，以便更好地对"每天"进行分组)。

id | vote_time | time_of_midnight |    name    | votes_yay | votes_nay
------------------------------------------------------------------------
1 |     a     |        b         |  Person p  |    24     |     36
1 |     a     |        b         |  Person q  |    20     |     10
1 |     a     |        b         |  Person r  |    42     |     22
1 |     c     |        d         |  Person p  |     8     |     10
1 |     c     |        d         |  Person s  |   120     |     63

• 每天可能有几十或几百个"人"(b，d，…)
• id是我分组所需的其他栏(如果有帮助的话，你可以把它看作选举id)

我试图按降序计算出每天得票最多的前5个名字我能够使用引用的文章创建一个查询，该查询将给我以下结果(在Oracle上)：

SELECT name, time_of_midnight, votes_yay, votes_nay, (votes_yay+votes_nay) AS total_votes
FROM results a
WHERE id=1 AND (
SELECT COUNT(*) 
FROM results b
WHERE b.id=a.id AND b.time_of_midnight=a.time_of_midnight AND (a.votes_yay+a.votes_nay) >= (b.votes_yay+b.votes_nay)) <= 5
ORDER BY time_of_midnight DESC, total_votes DESC;

name    | time_of_midnight | votes_yay | votes_nay | total_votes
------------------------------------------------------------------------
Person s  |         d        |     120   |     63    |     183
Person p  |         d        |       8   |     10    |      18
Person r  |         b        |      42   |     22    |      64
Person p  |         b        |      24   |     36    |      60
Person q  |         b        |      20   |     10    |      30

所以我真的不确定

• 为什么这种计数方法有效
• [愚蠢]：为什么我不需要在内部查询中包含name，以确保它不会错误地连接数据

Answer 1

让我们从以下事实开始：您的查询实际上是在计算得票率最低的前5个名字。要获得数字最高的前5名，您需要更改此条件：

(a.votes_yay+a.votes_nay) >= (b.votes_yay+b.votes_nay)

进入这个：

(a.votes_yay+a.votes_nay) <= (b.votes_yay+b.votes_nay)

或者，也许，这个(也是一样的)：

(b.votes_yay+b.votes_nay) >= (a.votes_yay+a.votes_nay)

(在我看来，后一种形式更可取，但这只是因为它与其他两种比较是一致的，即左侧有b列，右侧有a列。这与逻辑的正确性完全无关。)

从逻辑上讲，正在发生的事情就是这样。对于results中的每一行，服务器将在同一表中查找与给定行的id和time_of_midnight匹配的行，并且这些行的总票数与给定行中的总票数相同或更高。然后，它将对找到的行进行计数，并检查结果是否不大于5，即同一(id, time_of_midnight)组中是否不超过5行具有与给定行相同或更高的票数。

例如，如果给定的行恰好是其组中投票最多的行，则子查询将只找到同一行(假设没有平局)，因此计数将为1。这少于5——因此，给定的行将有资格输出。

如果给定的行将是组中投票次数第二多的项目，则子查询将找到同一行和投票次数最多的项目(同样，假设没有平局)，这将给出2的计数。同样，这与count <= 5条件相匹配，因此该行将在输出中返回。

通常，如果一行根据总票数在其组中排名为#N，则意味着该组中有N行的票数与给定行中的票数相同或更高(我们仍然假设没有平局)。因此，当你以这种方式计算选票时，你实际上是在计算给定行的排名。

现在，如果存在平局，使用这种方法，每个组的结果可能会更少。事实上，如果一个组有6行或更多行被绑定到最大行数，那么在输出中该组将得到无行，因为子查询永远不会返回小于6的计数值。

这是因为实际上，所有得票最多的项目都将被列为6(或无论其数量如何)，而不是1。要将它们列为1，您可以尝试对同一查询进行以下修改：

SELECT name, time_of_midnight, votes_yay, votes_nay, (votes_yay+votes_nay) AS total_votes
FROM results a
WHERE id=1 AND (
SELECTCOUNT(*) + 1
FROM results b
WHERE b.id=a.id AND b.time_of_midnight=a.time_of_midnight
AND(b.votes_yay+b.votes_nay) >(a.votes_yay+a.votes_nay)) <= 5
ORDER BY time_of_midnight DESC, total_votes DESC;

现在，子查询将只查找投票数高于给定行的行。结果计数将增加1，这将是给定行的排名(以及与5进行比较的值)。

因此，如果计数是例如10、10、8、7等，则排名将被计算为1、1、3、4等，而不是像原始版本那样计算为2、2、3、四等。

当然，这意味着输出现在每个组可能有多于5行的。例如，如果选票被分配为10、9、8、8、八、六等，你会得到10、9和所有的8(因为排名是1、2、3、3、三、七…)。要让每个组准确地返回5个名字(假设至少有5个)，你可能需要考虑一种不同的方法。