BrevFeed
Sign in

29. Window functions: OVER and PARTITION BY

📖 Reading · 7 min
💡 Every code box below is live — edit it and hit Run.

The problem GROUP BY can't solve: keep the rows, add a computation

GROUP BY (Module 5) collapses many rows into one per group — you lose the individual rows entirely, left only with the aggregate. But a real, common question is: "for each film, what's its rank within its rating, while still showing every individual film row." GROUP BY structurally cannot do this — it only ever has as many output rows as groups.

Window functions solve exactly this: they compute an aggregate-like value per row, using a window of related rows, without collapsing anything. Every input row still appears in the output, exactly once, now with an extra computed column.

SELECT
    title, rating, rental_rate,
    avg(rental_rate) OVER (PARTITION BY rating) AS avg_rate_for_rating
FROM film
ORDER BY rating, title
LIMIT 10;

Compare row count: this returns 1000 rows (every film), each annotated with its rating's average — not 5 rows the way GROUP BY rating would. That OVER (...) clause is what turns an ordinary aggregate function into a window function — the same avg() you already know, applied differently.

PARTITION BY: the "GROUP BY" of window functions

PARTITION BY column divides rows into groups for the purpose of the window computation — conceptually identical to GROUP BY's grouping logic, but without collapsing rows. Each row's window function sees only the rows sharing its partition's value:

SELECT
    title, rating, rental_rate,
    count(*) OVER (PARTITION BY rating) AS films_with_same_rating,
    max(rental_rate) OVER (PARTITION BY rating) AS max_rate_for_rating
FROM film
ORDER BY rating, title
LIMIT 10;

PARTITION BY is optional — omitting it treats the entire result set as one partition, exactly like an aggregate with no GROUP BY at all:

SELECT title, rental_rate,
       avg(rental_rate) OVER () AS avg_rate_overall
FROM film
LIMIT 5;

ORDER BY inside OVER(): order matters for some functions, not others

Some window functions (avg, sum, count, max, min used this way) don't inherently need an order — "the average for this partition" is the same regardless of row order. Others are fundamentally about position — ROW_NUMBER(), RANK(), LAG()/LEAD() (next two lessons) — and require ORDER BY inside the OVER() clause to mean anything at all:

SELECT
    title, rental_rate,
    row_number() OVER (ORDER BY rental_rate DESC) AS overall_rank
FROM film
ORDER BY overall_rank
LIMIT 5;

This ORDER BY is completely independent of the query's outer ORDER BY — one controls the order the window function processes rows in, the other controls the order rows are returned in. They're often the same column for readability (as above), but they don't have to be, and conflating them is a common early confusion.

Combining PARTITION BY and ORDER BY

The genuinely powerful combination — "rank within each group":

SELECT
    title, rating, rental_rate,
    row_number() OVER (PARTITION BY rating ORDER BY rental_rate DESC) AS rank_within_rating
FROM film
ORDER BY rating, rank_within_rating
LIMIT 15;

Read the OVER clause as a sentence: "partition rows by rating, and within each partition, order by rental_rate descending, then number them." This single line answers "what's the most expensive film in each rating category" — a question that's awkward to answer with GROUP BY alone (it would require a self-join or correlated subquery, both more verbose and less clear than this one OVER clause).

Where window functions can appear

Window functions are only allowed in SELECT and ORDER BY — never in WHERE, GROUP BY, or HAVING. This mirrors the aggregate-in-WHERE restriction from Module 5's HAVING lesson, for the same underlying reason: WHERE runs before window functions are computed (window functions conceptually run after WHERE/GROUP BY/HAVING, alongside SELECT). If you need to filter on a window function's result, wrap the whole query in a derived table or CTE and filter in the outer query — covered directly in the practical-patterns lesson at the end of this module.

Check yourself

  1. What's the key structural difference between what GROUP BY returns and what a window function with PARTITION BY returns?
  2. Why does ROW_NUMBER() require an ORDER BY inside OVER() to be meaningful, while AVG(...) OVER (PARTITION BY x) doesn't?
  3. Why can't a window function be used directly in a WHERE clause?
← Previous Next →