比较 Group by VS Over Partition By

Question

假设一个表 CAR 包含两列 CAR_ID (int) 和 VERSION (int)。

我想检索每辆车的最大版本。

因此（至少）有两种解决方案：

select car_id, max(version) as max_version 
  from car  
 group by car_id;

或者：

select car_id, max_version 
  from  ( select car_id, version
               , max(version) over (partition by car_id) as max_version
            from car
                ) max_ver  
 where max_ver.version = max_ver.max_version

这两个查询的性能是否相似？

Answer 1

我知道这是非常古老的，但认为应该指出。

select car_id, max_version 
  from (select car_id
             , version
             , max(version) over (partition by car_id) as max_version
          from car ) max_ver  
 where max_ver.version = max_ver.max_version

不知道为什么你这样做选项二...在这种情况下，子选择理论上应该更慢，因为你从同一个表中选择 2x，然后将结果连接回自身。

只需从内联视图中删除版本，它们就是同一件事。

select car_id, max(version) over (partition by car_id) as max_version
  from car

在这种情况下，性能实际上取决于优化器，但是，是的，原始答案建议内联视图，因为它们会缩小结果。尽管这不是一个很好的例子，因为它的同一个表在给定的选择中没有过滤器。

当您选择大量列但需要适合结果集的不同聚合时，分区也很有用。否则，您将被迫按每隔一列进行分组。

Answer 2

是的，它可能会影响

第二个查询是内联视图的一个示例。
这是一种非常有用的方法，用于执行具有各种类型计数的报告或使用任何聚合函数。

Oracle 执行子查询，然后使用结果行作为 FROM 子句中的视图。

当我们考虑性能时，始终推荐内联视图而不是选择其他子查询类型。

还有一件事是，第二个查询将给出所有最大记录，而第一个查询将只给出一个最大记录。

参见此处

Answer 3

这将取决于您的索引方案和表中的数据量。优化器可能会根据表中实际的数据做出不同的决策。

我发现，至少在 SQL Server 中（我知道你问过 Oracle），优化器更有可能使用 PARTITION BY 查询而不是 GROUP BY 查询来执行完整扫描。但这仅适用于索引包含 CAR_ID 和 VERSION (DESC) 的情况。

这个故事的寓意是我会彻底测试以选择正确的。对于小桌子来说，这并不重要。对于非常非常大的数据集，两者都可能不会很快......