OVER
The clause that makes a function a window function and defines which rows it can see and in what order.
In more detail
An empty OVER () sees the whole result. OVER (PARTITION BY customer_id) sees only the rows of the current customer. OVER (ORDER BY id) by default sees rows from the first up to the current one in id order, which gives a running total.
Example
SUM(id) OVER (ORDER BY id) AS running_id_sumRelated terms
- window function — A calculation across a set of rows related to the current row that, unlike GROUP BY, keeps every input row in the answer.
- PARTITION BY — The part of OVER that splits rows into groups so a window calculation restarts for each group.
- window frame — The subset of a partition that a window calculation sees for each row, such as the current row and the one before it.
Taught in
- Window Functions: Replacing Application Loops — PostgreSQL, Indexing & Relational Data Modeling
- Advanced Window Functions: Ranking And Framing — PostgreSQL, Indexing & Relational Data Modeling