DISTINCT with PARTITION BY vs. GROUPBY
distinct, group-by, query-performance, sql, sql-server
Solution
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.
Flexibility
Winner: `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.
Problem
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 stock ``` I'm quite sure this gives the same result as: ``` SELECT Company, Warehouse, Item, SUM(quantity) AS stock GROUP BY Company, Warehouse, Item ``` Is there any benefit (performance, readability, additional flexibility in writing the query, maintainability, etc.) of using the first approach over the later?