Introduction to window functions

OVER() versus GROUP BY, and keeping detail while aggregating.

What you'll be able to do

The difference from GROUP BY

GROUP BY collapses the rows. A window function computes across them and keeps every row.

SELECT order_id, region, total,
       ROUND(100.0 * total / SUM(total) OVER (PARTITION BY region), 1) AS pct_of_region
FROM orders;

One query gives the detail and its share of the group.

SUM(total) OVER ()    -- grand total on every row

With no PARTITION BY, the window is the entire result set.

Window functions are evaluated with SELECT, after WHERE. So this fails:

-- invalid
WHERE ROW_NUMBER() OVER (ORDER BY total DESC) <= 3

Wrap it and filter one level up:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
  FROM orders
) t WHERE rn <= 3;

Key points

Check yourself