Window Function

A window function computes a value for each row using a set of rows related to it, without collapsing those rows into one. An aggregate returns a single number per group; a window function returns a number per row while still seeing the group. That difference is what makes running totals, rankings, per-customer sequence numbers, moving averages and comparisons with the previous row expressible in a single SQL statement instead of a self-join or application code. The syntax has three parts worth understanding, because most confusion comes from mixing them up. PARTITION BY defines which rows belong together — the equivalent of GROUP BY but without collapsing. ORDER BY defines the order within a partition, and it is what makes running totals and lead/lag meaningful. The frame clause defines how much of the ordered partition the function sees for the current row, which is how a moving average over a trailing window is expressed. Omitting the frame leaves a default that is not always what people expect, and is a common source of results that look almost right. In analytics work they are the standard tool for the questions that dominate product reporting: each user's first and most recent event, the gap between consecutive events, a rank of accounts by revenue within each region, a rolling seven-day active count. They are also expensive on large tables because they require ordering within each partition, so on a warehouse it is worth checking whether the partitioning column is one the storage layout already supports.