SELECT 公司，仓库，项目， SUM（数量）AS库存 GROUP BY公司，仓库，物料
解决方案使用第一种方法是否有任何好处（性能，可读性，编写查询的额外灵活性，可维护性等） div> 表现：
优胜者： GROUP BY
在一个包含无索引列的大表上进行一些非常基本的测试表明，至少在我的情况下，这两个查询生成了完全不同的查询计划。用于 PARTITION BY 的那个显着较慢。 GROUP BY 查询计划仅包含表扫描和聚合操作，而 PARTITION BY plan有两个嵌套循环自连接。第二次运行时， PARTITION BY 大约耗时2800毫秒， GROUP BY 只花费了500毫秒。
可读性/可维护性：优胜者： GROUP BY 灵活性
优胜者： PARTITION BY
PARTITION BY 选择分组列。使用 GROUP BY ，所有聚合列只能有一组分组列。使用 DISTINCT + PARTITION BY ，您可以在每个分区中有不同的列。同样在一些DBMS中，您可以从 OVER 子句中的更多聚合/分析函数中进行选择。
I have found some SQL queries in an application I am examining like this:
SELECT DISTINCT Company, Warehouse, Item, SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stockI'm quite sure this gives the same result as:
SELECT Company, Warehouse, Item, SUM(quantity) AS stock GROUP BY Company, Warehouse, ItemIs there any benefit (performance, readability, additional flexibility in writing the query, maintainability, etc.) of using the first approach over the later?
解决方案Performance:
Winner: GROUP BY
Some very rudimentary testing on a large table with unindexed columns showed that at least in my case the two queries generated a completely different query plan. The one for PARTITION BY was significantly slower.
The GROUP BY query plan included only a table scan and aggregation operation while the PARTITION BY plan had two nested loop self-joins. The PARTITION BY took about 2800ms on the second run, the GROUP BY took only 500ms.
Readability / Maintainability:Winner: GROUP BY
Based on the opinions of the commenters here the PARTITION BY is less readable for most developers so it will be probably also harder to maintain in the future.
FlexibilityWinner: PARTITION BY
PARTITION BY gives you more flexibility in choosing the grouping columns. With GROUP BY you can have only one set of grouping columns for all aggregated columns. With DISTINCT + PARTITION BY you can have different column in each partition. Also on some DBMSs you can chose from more aggregation/analytic functions in the OVER clause.
更多推荐
DISTINCT与PARTITION BY与GROUPBY
发布评论