Window Functions: Aggregation Without Collapsing

The previous post‘s GROUP BY collapses each group into one row. But you’ve surely met this need: “I want a whole-group computation, and I want to keep every row.” For example — beside each order, mark what share it is of that customer’s total; or beside each month’s revenue, mark the difference from last month. Collapsing can’t do it, because once collapsed you don’t even have “each row” any more. That’s the window function: aggregation without collapsing. It’s the dividing line between SQL that “works” and SQL that’s “good”, and the foundation for a pile of analytical moves later in this series.

Window function = aggregation without collapsing

Same data, same SUM; the difference between GROUP BY and OVER is exactly one thing: whether the rows get collapsed:

In one sentence: GROUP BY is “many rows become one”; a window function is “beside each row, one more value computed over the whole group”. The rows are still there, so you can compare “one row vs the whole group”.

The three knobs of OVER

A window function’s power is all inside the OVER (...) parentheses, which break down into three knobs:

SUM(amount) OVER (
  PARTITION BY customer    -- ① group: but don't collapse (omit it and the whole table is one group)
  ORDER BY order_date      -- ② order within the group: defines "so far"
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- ③ frame: which range each row looks at
)

The first two are easy; the third, the frame, is the one most people never get straight and most often get bitten by. The frame decides “when computing this row, which rows of the group are included”. Take a running total: each row’s frame is “from the start to the current row”, so the sum grows row by row:

One trap you must remember here: the frame’s default. When you write ORDER BY but no frame, the default is “start → current row” (a running total); with no ORDER BY, the default is “the whole partition” (a group total). So SUM() OVER (PARTITION BY c) is a group total, but SUM() OVER (PARTITION BY c ORDER BY d) becomes a running total — one ORDER BY changes the meaning, and many people silently compute the wrong thing here.

The three most-used kinds of window function

• Ranking: ROW_NUMBER() (1,2,3… always unique), RANK() (ties share a rank and skip: 1,1,3), DENSE_RANK() (ties share a rank, no skipping: 1,1,2).
• Offset: LAG() / LEAD() fetch the previous / next row’s value. Period-over-period in one line: sales - LAG(sales) OVER (ORDER BY month).
• Aggregates as windows: SUM / AVG / COUNT plus OVER, with a frame, for running totals, moving averages, shares.

The classic move: top N per group

“Top 3 by sales in each category” is the most iconic use of window functions, and it lands right on the first post‘s processing order — a window function is computed in the SELECT (⑤) stage, so it can’t go directly in WHERE (②); compute the rank in a subquery first, then filter in the outer layer:

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn <= 3;   -- top 3 per category

That old question, “why must it be wrapped in a subquery”, is again answered by the processing order: by the time ROW_NUMBER is computed, WHERE has long since run.

Reflections

The words “no collapsing” open up a whole new territory

I still remember the first time I used a window function — SQL suddenly “levelled up”. A lot of things that used to mean joining back to yourself or hand-building piles of correlated subqueries (each row’s share, comparison with the previous row, ranking within a group) were solved by one OVER, fast and readable. The key insight extends the previous post: GROUP BY collapses the rows away, and you lose “the single row”; a window function keeps every row, which is what makes “this row vs its group” comparisons possible. The essence of many analytical requirements is “the relationship between a row and the group it belongs to”, and that’s exactly what window functions were born to do.

The frame default is the trap I’ve seen the most people fall into

Is SUM() OVER (PARTITION BY c ORDER BY d) a group total or a running total? The only difference is whether you wrote ORDER BY, and the results are worlds apart. It’s exactly the spirit of the NULL post: if you don’t understand the default behaviour, you’ll write queries that “run, look right, and are wrong”. When I write running or group totals now, I always spell the frame out (ROWS BETWEEN ...) rather than relying on the default — one extra line, in exchange for eliminating a whole class of hard-to-catch bugs. A bargain.

Window functions are the dividing line of analytical SQL

Honestly, whether someone knows window functions is close to the line I use to judge how deep their SQL goes. It’s not just a convenient function; it’s a way of looking at data — putting each row back into the group and sequence it belongs to. The next several moves in this series — dedupe to the latest, contiguous ranges (gaps and islands), slowly changing dimensions (SCD) — are all, underneath, applications of window functions. So get fluent with the three knobs of OVER, and your SQL has truly entered the door of analytics.