PARTITION BY & OVER Clause
The OVER clause turns an aggregate or ranking function into a window function, computing the result over a defined window of rows without collapsing them. PARTITION BY subdivides the window; ORDER BY defines row ordering within each partition.
OVER() is a window in the wall — you look through it at a specific section of data (partition) in a specific order, without destroying the wall (collapsing rows). Each window function is its own window.
Without PARTITION BY: all rows are one partition. With PARTITION BY department: each department is an independent partition. Window functions run after WHERE, GROUP BY, and HAVING — they operate on the result set, not the base table. This means you can combine GROUP BY with window functions: first aggregate with GROUP BY, then rank groups with a window function in a CTE. Multiple window functions with the same OVER spec are optimized by the planner to a single pass.
WINDOW clause (SQL standard): define named windows to avoid repeating OVER specs. SELECT salary, AVG(salary) OVER w, RANK() OVER w FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary). The planner evaluates all window functions sharing the same partition/order with a single sort and scan. Window functions cannot be used in WHERE or HAVING directly — wrap in a subquery or CTE. In PostgreSQL, window function execution happens after all other clauses, using a sequential scan of the (materialized or sorted) intermediate result.
OVER() is what makes a function a window function instead of an aggregate. PARTITION BY is like GROUP BY but without collapsing rows. ORDER BY within OVER controls row ordering for ranking and frame-based calculations. I use the WINDOW clause when multiple functions share the same OVER spec to keep the query DRY and help the optimizer plan a single pass.
Trying to filter on a window function result in WHERE. Window functions are evaluated after WHERE — you can't filter them there. Wrap the query in a CTE or subquery, then apply WHERE on the outer query.