Window Functions: Compute Without Collapsing

Aggregate functions are powerful, but they come with a cost: when you ask for a summary, you lose the detail. Window functions remove that trade-off. They let you compute a group-level value — a total, an average, a rank — and place it alongside every individual row, without collapsing anything. This article builds the intuition you need before working with any specific window function.

What Is a Window Function?

A window function performs a calculation across a set of table rows that are somehow related to the current row — and crucially, it does so without merging those rows into a single output row. As the PostgreSQL documentation puts it: "unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities." Each row keeps its own identity in the result, but gains access to information computed across its neighbours.

The clearest way to feel the difference is to contrast it with GROUP BY. A grouped aggregate is like asking each relay team for a single number: "What was your team's average finish time?" You get one row per team, and the individual runners disappear into that average. A window function is like standing on the track during the race: you can see your own finishing position and glance sideways to see how every other runner did. Nobody is collapsed into a summary — the detail and the context coexist.

Technically, what marks a function as a window function is the presence of the OVER clause. The same SUM() or AVG() you already use with GROUP BY becomes a window function the moment you attach OVER (...) to it. Without OVER, it is an ordinary aggregate; with OVER, it computes a value for each row while leaving the row count untouched.

The Sample Data

Throughout this article we use a small e-commerce dataset of customers and their orders.

customers

orders

GROUP BY vs. Window Function: The Key Difference

Suppose we want to know how much each customer has spent in total. The familiar approach uses GROUP BY:

This collapses the five order rows down to one row per customer. The summary is correct — but the individual orders are gone. Now compare the window-function version, which keeps every order row and simply adds the customer's total spend as an extra column:

Look closely at the row counts. The GROUP BY query returns four rows — one per customer. The window query returns five rows — exactly as many as the orders table — and each order now carries its customer's total spend. That is the entire idea in a nutshell: same data, enriched versus fewer rows, summarized.

The Anatomy of OVER()

Every window function call is built around the OVER clause. As Microsoft's SQL Server documentation states: "the OVER clause determines the partitioning and ordering of a rowset before the associated window function is applied." It has two core pieces you will use constantly:

PARTITION BY

PARTITION BY divides the rows into independent groups — the "windows" — that share the same value of the partition expression. The function is then computed separately within each group, and the computation restarts for each partition. It is similar in spirit to GROUP BY, but with one essential difference: it does not collapse the rows. If you omit PARTITION BY, the entire result set is treated as a single partition.

ORDER BY (inside OVER)

ORDER BY inside the OVER clause establishes the ordering of rows within each partition. This is essential for operations such as ranking and running calculations. It is important to distinguish this from the ORDER BY at the end of the query: the latter controls the order in which the final result is displayed, while the ORDER BY inside OVER is part of the window definition.

A Gentle Worked Example

Let's revisit the partition aggregate from earlier and follow it step by step. We want each order to display the total spend of the customer who placed it. First, conceptually, PARTITION BY customer_id slices the orders into per-customer windows; then SUM(total) is computed within each window and written onto every row.

Notice that customer 1's two orders both show 131.50 — the sum of 89.50 and 42.00 — repeated on each of their rows. Because there is no ORDER BY here, this is a flat partition total, not a running total. The value is the same for every row in the partition. Adding ORDER BY changes the window definition and, for an aggregate such as SUM(), can produce a cumulative calculation depending on the resulting frame.

A First Look at "The Frame"

There is one more concept worth meeting briefly. A window definition can include a window frame: a specific subset of rows within the partition that is visible to frame-sensitive window calculations for the current row. This is what allows calculations such as running totals and moving averages to work over a changing set of rows.

The important nuance for beginners is that the default frame depends on whether ORDER BY is present. In PostgreSQL, when ORDER BY is supplied, the default frame extends from the start of the partition through the current row and its peers — rows that tie according to the window ordering. When ORDER BY is omitted, the default frame covers the entire partition. That is why our SUM() example above returns the same partition total on every row, whereas adding an ORDER BY can produce running cumulative behaviour.

Frames have their own syntax, including ROWS and RANGE, and they will be covered in depth separately. For now, simply knowing that a frame exists — and that it controls which rows are visible to a frame-sensitive calculation — is enough.

The next diagram makes this concrete. To keep the focus on how ORDER BY reshapes the frame, it drops PARTITION BY entirely and treats the whole table as a single window — so the numbers you see are sums across all five orders, not per-customer totals like the worked example above.

Why Window Functions Matter

Window functions unlock a whole category of analytical queries that are awkward or slow to express otherwise. A few of the things they make easy — each of which deserves its own treatment:

• Ranking — numbering or ranking rows within a group, such as the top order per customer.
• Running totals — cumulative sums that grow as you move through ordered rows.
• Comparing each row to a group average — e.g., flagging orders above their customer's mean.
• Moving averages — smoothing time series over a sliding window of rows.
• Top-N per group — selecting the best few rows within each partition.

All of these share the same DNA as our example: a calculation over related rows, returned without throwing the detail away.

Cross-Database Support

Window functions are part of the SQL standard, introduced in SQL:2003 (ISO/IEC 9075, the fifth revision of SQL, published on 1 March 2004). Oracle had shipped an early implementation even before that, in Oracle8i in 1998. Today the core syntax is essentially the same across the major engines:

This portability means the intuition you build here transfers directly between systems, with only minor dialect differences around advanced framing options.

Where We Go From Here

You now have the one idea that everything else builds on: a window function computes across related rows while letting each row keep its place in the output. OVER is the switch that turns an aggregate into a window function; PARTITION BY chooses the groups; ORDER BY sets the ordering within the window; and the frame fine-tunes exactly which rows are in view for frame-sensitive calculations. With that foundation in place, the next step is to put individual functions to work — assigning ranks, building running totals, and reaching across rows to compare values — and to see just how much expressive power this single clause delivers.