OVER Clause Anatomy

## What OVER is A window function is an ordinary function call followed by the keyword `OVER` and a window definition in parentheses. The `OVER` clause is what makes it a *window* function: it tells the engine which other rows this row is allowed to see when the value is computed. Without `OVER` the same name means something else entirely — `SUM(amount)` is a grouped aggregate that collapses rows, `SUM(amount) OVER (...)` is a window aggregate that leaves every row in place and attaches a computed value to it. The window definition has three optional parts, always in this order: ```sql function_name(args) OVER ( PARTITION BY partition_expr [, ...] -- 1. split ORDER BY sort_expr [ASC|DESC] [, ...] -- 2. sequence frame_clause -- 3. narrow ) ``` ## PARTITION BY: splitting into independent groups `PARTITION BY` takes one or more expressions and divides the rows into groups that share the same value of those expressions. The function is then evaluated independently inside each group, and its state resets at every partition boundary. In `SUM(amount) OVER (PARTITION BY customer_id)`, each row gets the total for *its own* customer. Two things it is not: - It is **not a filter**. It never removes rows the way `WHERE` does; it only decides which rows are peers of which. - It does **not collapse** rows. Ten input rows produce ten output rows, each carrying its partition's value. The partition expressions are ordinary expressions, not just bare columns — `PARTITION BY EXTRACT(YEAR FROM order_date)` is legal. Multiple keys work like a compound grouping key: `PARTITION BY region, product_id` makes one partition per (region, product) pair. ## ORDER BY: sequencing rows inside a partition `ORDER BY` inside `OVER` gives the rows of each partition an order. That order is what lets the engine answer "which row is before this one". It is essential for anything positional — numbering rows, looking at the previous row's value, accumulating a total as you walk forward. It is also what makes a frame meaningful, because a frame is defined relative to the current row's position in that sequence: with no sequence there is no "preceding" and no "following". Crucially, this `ORDER BY` is scoped to the window. It orders rows *for the computation*; it says nothing about the order in which the query hands rows back to the client. That is the job of the query's own `ORDER BY` clause, which is a separate thing that happens to share a keyword. ## The frame slot The third slot narrows the window further, from "the whole partition" to a moving span around the current row, written with `ROWS`, `RANGE` or `GROUPS` (for example `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`). It only becomes meaningful once `ORDER BY` has sequenced the rows, and most queries leave it out and take the default. ## Both parts are optional — four shapes ```sql OVER () -- one unordered partition: the whole result set OVER (PARTITION BY dept_id) -- per-department, unordered OVER (ORDER BY hire_date) -- one partition, sequenced by date OVER (PARTITION BY dept_id ORDER BY hire_date) -- per-department, sequenced by date ``` All four are valid SQL. Which one you want depends on the question: "restart per group?" chooses `PARTITION BY`, "does position matter?" chooses `ORDER BY`. ## Reading one aloud A useful interview habit is to narrate the clause left to right. `AVG(score) OVER (PARTITION BY class_id ORDER BY exam_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)` reads as: *for each class separately, walking exams oldest to newest, average the score over this exam and the two before it.* Split → sequence → narrow. If you can say that sentence, you can read any window clause. ## Where the window sits in the query The rows a window sees are the rows that survived `FROM`, `WHERE`, `GROUP BY` and `HAVING` — a window never sees rows the filters removed. That is why `AVG(salary) OVER ()` in a query with `WHERE department_id = 3` is the average of department 3, not of the whole table. ## Common mistakes Treating `PARTITION BY` as a synonym for `GROUP BY` (it splits but does not collapse); assuming both slots are required (`OVER ()` is legal); and assuming the window's `ORDER BY` sorts the output. Each of those is a distinct misunderstanding of one slot.