Track · 2:15 · Liner note
Window Function Waltz
A window function computes a value for each row from a related set of rows, such as a running total, rank or moving average, without collapsing the rows the way GROUP BY does.
Window functions move in threes: a partition, an order and a frame. Choose them well and each row steps through its neighbors, picking up a running total, a rank or the value from the row before, while staying in the result. That is the Window Function Waltz.
The frame is where beginners stumble. In standard SQL, a running aggregate with an ORDER BY defaults to a range up to the current row, so ties can behave unexpectedly. Learn the frame clause and the rest follows: rank, lag, lead and moving average are variations on one step.