DISTINCT with PARTITION BY vs.

Question

我在我正在检查的应用程序中发现了一些SQL查询，如下所示：

SELECT DISTINCT
Company, Warehouse, Item,
SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock

我非常确定这会给出相同的结果：

SELECT
Company, Warehouse, Item,
SUM(quantity) AS stock
GROUP BY Company, Warehouse, Item

是否有任何好处(性能、可读性、编写查询时的额外灵活性、可维护性等)使用前一种方法而不是后一种方法？

Answer 1

性能：

获奖者： GROUP BY

在一个包含未索引列的大型表上进行的一些非常基本的测试表明，至少在我的例子中，这两个查询生成了一个完全不同的查询计划。PARTITION BY的速度要慢得多。

GROUP BY查询计划只包括一个表扫描和聚合操作，而PARTITION BY计划有两个嵌套循环自联接。在第二次运行时，PARTITION BY花了大约2800毫秒，而GROUP BY只花了500毫秒。

可读性/可维护性：

获奖者： GROUP BY

根据评论者的意见，PARTITION BY对于大多数开发人员来说可读性较差，因此在未来可能也更难维护。

灵活性

获奖者： PARTITION BY

PARTITION BY使您在选择分组列时具有更大的灵活性。使用GROUP BY，对于所有聚合列，只能有一组分组列。使用DISTINCT + PARTITION BY，您可以在每个分区中拥有不同的列。另外，在一些DBMS上，您可以在OVER子句中选择更多的聚合/分析函数。

Answer 2

在over partition by中使用sum()作为分析函数是不必要的。我不认为它们在任何意义上有很大的区别。在oracle中，解析函数比聚合函数多得多。我认为ms-sql也是如此。例如，lag()、lead()、rank()、dense rank()等只用group by就很难实现。当然，这个论点并不是为了捍卫第一个版本……

可能以前在结果集中有更多的计算字段，这些字段不能用group by实现。

Answer 3

尽管当您查看列时，这两个查询似乎计算相同的内容，但它们实际上生成了完全不同的行集。

使用解析函数的第一个函数将为每一个输入行恰好输出一行。也就是说，对于每个库存信息，它将返回一行，其中包含相关公司/仓库/项目的总数量。(顺便说一句，计算平均值对我来说更有意义，但谁知道呢……)

第二个只会为每个公司/仓库/商品组合返回一行。

所以，是的，在这个例子中，第一个查询看起来有点无用…除非您想按公司/仓库/项目计算一些库存水平统计数据，如当前库存与总数量的比率(只是一个例子，不知道它是否有任何业务意义！)

分析函数是SQL中非常强大机制，在某种意义上比group-by更强大。但要小心使用它。一个简单的经验法则可能是:如果你可以使用group-by计算它，那么，就不要使用解析函数;)