Calculating window functions
SQL window functions are an additional tool in your toolkit. Unlike aggregate functions, which return a single result per group of rows, window functions return a single result for each row, based on the context of that row within a window of related rows.
OVER, ORDER BY, PARTITION, and SET
Window functions have the following basic syntax:
<function> (<expression>) OVER ( [PARTITION BY <expression_list>] [ORDER BY <expression_list>] [ROWS|RANGE <frame specification>])
There are a few key concepts to understand here, so let’s break them down:
- The
OVERkeyword is what differentiates a window function from a regular function; once you see it, you know you’re in window function land. TheOVERclause defines the window or subset of rows within a query result set that the window function operates on. In short, it provides a way to partition the result set into logical groups and allows the window...