使SQL查询更高效

Question

我有一个类似的查询问题

select b.* from
(select key, max(val) as val from (somequery) group by key) as a
inner join
(somequery) as b
on a.key = b.key and a.val = b.val
order by key

我在想是否有明显的方法（我可能错过了）来简化它（考虑到某些查询可能相当长）。

欢迎提出任何想法。

- Sam

---

如果你希望得到真正的帮助，你需要详细说明一下“somequery”。 - OMG Ponies

@rexem：不是的，原帖并没有这个要求。很明显他只想获取最大值所在的行。 - Eric

Answer 1

确实有这个功能，但并不显而易见：

select
    *
from
    (
    select
        key,
        val,
        col,
        max(val) over (partition by key) as MaxVal
    from
        tableA
    )
where
    val = MaxVal

使用over子句是实现此目的的好方法，不需要任何额外的子查询。它只是针对每个键值获取最大的val，然后将结果集包装在子查询中，在这里我们可以检查val是否与MaxVal匹配，以确保我们提取正确的行。

比使用多达三个子查询更加简洁和快速！

- Eric

---

我以前没有见过MAX..OVER。对于复合键，这是如何工作的？ 我正在尝试在自己的表上使用agg/join或ROWNUMBER = 1，但不能使用此技术。 - gbn

max(val) over (partition by key1, key2, key3) 运行得非常好。 - Eric

Answer 2

你需要使用ROW_NUMBER()或RANK()。

(请确保前一个查询以分号结尾)

with ranked as
(
select *, row_number() over (partition by key order by val desc) as bestrow
from sometableorquery 
)
select *
from ranked
where bestrow = 1
order by key;

如果你想要并列（让一个有两个最佳值的键返回两者），那么请使用rank()而不是row_number()。

Rob

- Rob Farley

---

另外，如果您想获取每个关键字的前三项，则尝试使用“WHERE bestrow <= 3”。 - Rob Farley

ROW_NUMBER只会返回每个键的一行。而OP的代码在一个键有多行且val=max(val)的情况下会出现问题。将ROW_NUMBER()替换为RANK()可以保留原本的意图。 - Shannon Severance

谢谢Shannon。我在那里提到了“如果你想要领带”的事情。 - Rob Farley

Answer 3

我建议在从子查询中选择之前将其存储到临时表中。我认为这样做可以显著提高性能。

- Jon

---

不是我点的踩，但是...如果你从一个临时表中选择，数据库管理系统必须存储该数据及其元数据，然后才能使用它。更有效率和有效的方法是将整个查询提供给优化器 - 这样它就可以尽可能避免材料化子查询。现在，如果有几个查询将使用相同的子查询，则可以从显式的临时表中获益 - 尽管最好进行测量并确保。 - Jonathan Leffler

@Jonathan：我同意你的原则，但是我的SQL服务器经验表明，对于更大的表格（比如超过2000行），这种方法更加高效。 - Jon