Window functions: rankings and per-group calculations

A window function is a function that operates on a "group of rows" (the window) but — unlike GROUP BY — does not reduce the number of rows: a value computed over the window is attached to each row of the final result.

It's the right tool for rankings, progressive numbering, percentages over a group total, and many other operations that are awkward or impossible with GROUP BY.

The syntax: function() OVER (…)

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Components:

• PARTITION BY — defines the "groups" (like GROUP BY, but the rows stay separate);
• ORDER BY inside OVER(…) — defines the order within the partition.

OVER () (empty parentheses) means "the whole table as a single window". Useful for computations like "percentage of total".

Ranking functions

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Differences (with values 100, 100, 90, 80):

Example: product ranking per category

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

For each category_id the RANK restarts from 1 and orders by descending price: you get the ranking of products within each category, in one shot.

Progressive numbering

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

Each customer's first order will have row_num = 1, the second 2, etc.

Try it

Review exercise