[adamtj]

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