4 ms·

Using distinct and windowing functions to avoid writing group-by clauses may work fine with Postgres but is a very ugly hack.

• wallyhs 12y ago
  This "hack" is also supported by Oracle and SQL Server.
• adamtj 12y ago
  This ugly hack is not useful in general and only looks interesting here because the original example query is in bad taste. Simplifying it removes the tedium motivating the use of window functions. For example, this is equivalent to the original query: SELECT sku, sum(price) FROM invoice_items WHERE date_part('month',created_at) = 9 GROUP BY sku See how the tedium disappears? In the original, the HAVING clause doesn't use any aggregate functions, which means it can instead be a WHERE. Removing the HAVING means we can further simplify the GROUP BY. Of course, that only works because this example needs only a single month group. If you want multiple months and per-month price totals, then my rewritten query is no longer equivalent. It would need the more complicated GROUP BY. However, the window function version would also get more complicated in just the same way. The complication is inherent to the problem, not due to standards conformance. Actually, what's really tedious is the verbose expression required to get the month and the fact that the author is repeating it. The standard provides "WITH" to avoid that duplication, and Postgres implements it. Here's a reasonable version for multiple months: WITH items AS ( SELECT *, date_part('month',created_at) AS month FROM invoice_items ) SELECT sku, month, sum(price) FROM items WHERE month in (7,8,9) GROUP BY sku, month But suppose we make the example even more complicated. Suppose you want sums over different time periods, like per-month and per-quarter. Then you can't use a GROUP BY and must use window functions and DISTINCT. WITH items AS ( SELECT * ,date_part('year',created_at) AS year ,date_part('month',created_at) AS month ,date_part('quarter',created_at) AS quarter FROM invoice_items ) SELECT DISTINCT sku ,year ,month ,quarter ,sum(price) OVER (PARTITION BY year) AS yearly_total ,sum(price) OVER (PARTITION BY year, month) AS monthly_total ,sum(price) OVER (PARTITION BY year, quarter) AS quarterly_total ,sum(price) OVER (PARTITION BY month) AS month_number_total ,sum(price) OVER (PARTITION BY quarter) AS quarter_number_total FROM items ORDER BY 1,2,3,4