Window function

What is a window function?

A window function computes a value across a set of rows that relate to the current row, without folding those rows into one. Every input row stays in the result and gains an extra column: its rank, a running total, or the value from the row before it.

That behaviour is what separates a window function from GROUP BY. GROUP BY collapses each group into a single line, so ten orders for one customer become one row. A window function leaves all ten orders in place and writes the group calculation beside each one. You keep the row-level detail and the group-level number in the same query.

Think of it as a margin note beside every line of a ledger, where the note can still see the whole page. The row stays and the note adds context, and in SQL that is what a window function is for.

How the OVER clause defines the window

Every window function carries an OVER clause right after its arguments, and that clause marks which rows make up the window. Leave OVER empty and the window is the whole result set; fill it in and you shape it with two controls.

PARTITION BY splits the rows into groups that share a value, and the function restarts at each group, so PARTITION BY customer_id gives every customer its own private window. ORDER BY inside OVER sets the order the function walks the rows in, which is what makes a running total run in the right direction. This ORDER BY sits inside the parentheses and is separate from any ORDER BY that sorts the final output.

Here is a running total of order amounts, restarting for each customer:

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

Each order still appears on its own row, and next to it sits the sum of every amount for that customer up to and including that order date. Swap SUM for AVG and you get a moving average; use LAG or LEAD instead and you pull the previous or next row's value onto the current one for a month-over-month comparison.

Ranking rows: ROW_NUMBER, RANK and DENSE_RANK

Three ranking functions come up constantly, and they differ only in how they treat ties. Once you order a set of rows, the real question is what happens when two rows hold the same value.

Take three sales reps in one region with revenue of 5,000, 5,000 and 4,000, ordered from high to low:

• ROW_NUMBER() gives 1, 2, 3. It hands out unique numbers no matter what, so the two reps on 5,000 get 1 and 2. Which of them lands on 1 is arbitrary unless you add a tie-breaker to the ORDER BY.

• RANK() gives 1, 1, 3. Tied rows share a rank, and the next rank skips ahead to leave a gap. The missing 2 tells you two rows sat at or above the third.

• DENSE_RANK() gives 1, 1, 2. The tie still shares rank 1, but the next distinct value is 2, with no gap.

So the choice comes down to ties: ROW_NUMBER forces a strict order, RANK keeps ties but leaves gaps, and DENSE_RANK keeps ties without gaps. Use ROW_NUMBER when you need exactly one row per group, a common first step in deduplication before you load a data warehouse: number the rows per key and keep number one.

The frame clause: ROWS versus RANGE

Once you add ORDER BY inside OVER, a third setting quietly switches on: the frame, which decides how many rows around the current one the function actually reads. You set it with ROWS, a physical count such as the current row and the two before it, or with RANGE, a logical range based on the ordering value.

The catch is the default. When you write ORDER BY but no frame, both PostgreSQL and SQL Server default to RANGE UNBOUNDED PRECEDING, which reaches from the start of the partition through the current row's last peer. A peer is any row the ORDER BY treats as equal, so if your ordering column has ties, the default frame reaches forward to include every tied row, not just the current one.

That trips people up. A running total ordered by a date that repeats will, on a tied date, show the same total for every row sharing that date, because RANGE folds peers together. If you meant one row at a time, write ROWS UNBOUNDED PRECEDING instead, where CURRENT ROW means only the current row. For unique ordering values the two behave the same, which is why the trap stays hidden until a duplicate shows up.

What to watch out for with window functions

You cannot filter on them in WHERE. Window functions run after WHERE, GROUP BY and HAVING, so a query cannot say WHERE rank = 1 directly. Wrap the window query in a Common Table Expression or a subquery, then filter the outer query.

The ordering carries the meaning. A running total or a rank is only as trustworthy as its ORDER BY. Leave it vague, or order by a column full of ties, and the numbers shift under the default RANGE frame.

Large windows cost sorting. A window function usually needs the data sorted by its partition and order columns. Over millions of rows that sort dominates the execution plan, and an index whose leading columns match PARTITION BY then ORDER BY can remove it. Partitioning the underlying table along the same lines helps too.