使用rownum或HAVING >= ALL的Oracle ORDER BY

Question

我的数据库老师让我写一个查询（在Oracle服务器上）：选择2010年平均分数最高的组id。

我写了：

SELECT * FROM (
    SELECT groupid, AVG(score) average FROM points
    WHERE yr = 2010
    AND score IS NOT NULL
    GROUP BY groupid
    ORDER BY average DESC
) WHERE rownum = 1;

我的老师告诉我，这个请求“更好”:

SELECT groupid, AVG(score) average FROM points
WHERE yr = 2010
GROUP BY groupid
HAVING AVG(score) >= ALL (
    SELECT AVG(score) FROM points
    WHERE yr = 2010
    GROUP BY groupid
);

哪个更快/更好？还有什么更好的解决方案（仅适用于Oracle）吗？ 谢谢。

- Maxux

Answer 1

有两个原因导致你的教练这样告诉你。

1. 数据模型。关系型数据库管理系统处理集合而非列表。如果你正在学习SQL，最好从元组集合的角度考虑，不要按顺序列表进行思考。这样有助于你更好地理解如何查询DBMS。我认为你的解决方案是一个hack:部分情况下有效，因为-如Perun_x所指出的-如果有多个元组匹配结果，它将无法正常工作。这与SQL的数据模型和精神相反）。

2. 可移植性。这才是真正的杀手。你的代码在Oracle上可以工作，但不会在其他不支持row_number属性的DBMS上工作（每个DBMS都有自己的方法）。

--dmg

- dmg

---

谢谢。实际上，我没有“元组集”观点。但是教育方式和性能方式并行的方式对我来说确实不是非常有趣。关于可移植性问题，这是完全正确的。我更喜欢在rownum内使用LIMIT，虽然我知道这也不是可移植的。另一方面，我们也在学习Oracle特定的代码... - Maxux

Answer 2

我认为你的版本更好，假设一行就足够满足你的需求。我对老师的版本主要是可读性有问题，我发现很难解析。

你的版本基本上是说“按照平均值排序，并取最高平均值的组”。老师的版本基本上是说：“找到大于或等于任何组平均值的平均值”。这可能是主观的，但我发现前者比后者更容易理解。

至于哪个更快。你的需要聚合和排序才能获得最佳值。第二个版本需要进行两次聚合和一次连接。我认为聚合/排序方法会更快，但真正了解性能的唯一方法是检查特定系统配置和数据集的性能。

另一种表述方式，应该在性能上与你的版本大致相同：

select groupid, avgscore
from (select groupid, avg(score) as avgscore,
             row_number() over (order by avg(score) desc) as seqnum
     from points
     where yr = 2010
     group by groupid
    ) t
where seqnum = 1

这里的优点在于您可以将row_number()更改为dense_rank()，以获得最佳行中的1行或所有最佳行。

- Gordon Linoff

---

谢谢。我尝试了一些方法（也尝试了用WITH语句），是的，HAVING方法明显更慢。您提供的备选方案很有趣。 - Maxux

Answer 3

这两个查询不相同。第一个查询总是选择一行。第二个查询选择所有平均值最高的行（理论上可能有多行）。

- Perun_x

---

确实。在我们的情况下，这不应该发生。 - Maxux