Window Functions and OVER
Window functions calculate values across related rows without collapsing them into one row per group. OVER defines the window; PARTITION BY divides rows into groups, and ORDER BY defines ordering within a window. Functions such as ROW_NUMBER, RANK, LAG, and LEAD support ranking and comparisons.
Unlike GROUP BY, window functions preserve the individual result rows. Understand window frames for running aggregates: the default frame can have peer-related behavior when ORDER BY is present, so specify a frame explicitly when the intended calculation requires row-by-row accumulation.
Rank orders per customer
SELECT customer_id, id, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY total DESC, id
) AS order_rank
FROM orders;💡 ROW_NUMBER assigns a unique sequence within each customer's partition. The id tie-breaker makes the ordering deterministic when totals are equal.
Key Takeaways
- Window functions preserve individual rows.
- PARTITION BY defines independent groups for window calculations.
- Use deterministic ordering for stable ranking.
Knowledge Check
Which window function assigns a unique sequential number to each row in a partition?