Window Function Execution
A window function computes a value over a set of related rows without collapsing them, unlike a group by. Examples include running totals, rankings, and moving averages.
Partition and order
A window has two parts. The partition by clause splits rows into independent groups, and the order by clause orders rows within each partition. The engine usually sorts by partition keys then order keys so each partition becomes a contiguous run.
The frame
Within a partition, a frame defines which rows feed each computation, such as all rows from the start to the current row for a running total. The executor sweeps through the ordered partition, maintaining frame state as it advances.
- Partition by creates independent groups.
- Order by sequences rows inside a partition.
- The frame chooses which neighboring rows contribute.
Key idea
Window functions sort rows by partition and order, then sweep each partition maintaining a frame, producing a value per row without collapsing them.