Window functions: OVER, PARTITION BY and ORDER BY

A window function computes a value for every row of the result, using other rows as input. It keeps the row count of the query intact, which is what separates it from GROUP BY. This first part covers the building blocks: OVER, PARTITION BY, ORDER BY and named windows. Part 2 covers frames, ranking and lag/lead.

Three kinds of function

Kind	Input	Output	Example
Scalar	one row’s values	one value per row	`upper(name)`, `sqrt(aa + bb)`
Aggregate	many rows	one value per group	`sum(value)`, `count()`
Window	many rows (the window)	one value per row	`sum(value) OVER (...)`

A window function is “the best of both”: it sees many rows like an aggregate, and returns a value for each input row like a scalar function. You do not need a grouping key, and you do not lose the other columns.

DISTINCT is none of these. It is not a function at all, only a modifier that drops duplicate rows from the result.

The demo data

The examples use a small table of hourly sensor readings.

CREATE TABLE metrics (ts DateTime, sensor_id UInt32, metric LowCardinality(String), value Float64)
ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (metric, sensor_id, ts);

INSERT INTO metrics VALUES
('2026-01-01 00:00:00',1,'temp',20.5),('2026-01-01 01:00:00',1,'temp',21.0),
('2026-01-01 02:00:00',1,'temp',19.5),('2026-01-01 03:00:00',1,'temp',23.0),
('2026-01-01 00:00:00',2,'temp',18.0),('2026-01-01 01:00:00',2,'temp',18.0),
('2026-01-01 02:00:00',2,'temp',22.5),('2026-01-01 03:00:00',2,'temp',17.0);

With a plain aggregate you get one row per sensor:

SELECT sensor_id, count(), sum(value) FROM metrics GROUP BY sensor_id ORDER BY sensor_id;

┌─sensor_id─┬─count()─┬─sum(value)─┐
│         1 │       4 │         84 │
│         2 │       4 │       75.5 │
└───────────┴─────────┴────────────┘

Add a non-aggregated column and GROUP BY complains, because it cannot tell which row’s ts you mean. A window function has no such problem.

OVER makes it a window function

The OVER keyword is what turns an aggregate (or a window-only function) into a window function. It can be used in three ways.

Empty OVER

OVER () means one window containing the whole result set:

SELECT sensor_id, ts, value, sum(value) OVER () AS total
FROM metrics ORDER BY sensor_id, ts;

Every row gets the same total (159.5 here) next to its own value.

PARTITION BY

PARTITION BY splits the rows into independent groups, and the function is computed inside each one:

SELECT sensor_id, ts, value, sum(value) OVER (PARTITION BY sensor_id) AS sensor_total
FROM metrics ORDER BY sensor_id, ts;

┌─sensor_id─┬──────────────────ts─┬─value─┬─sensor_total─┐
│         1 │ 2026-01-01 00:00:00 │  20.5 │           84 │
│         1 │ 2026-01-01 01:00:00 │    21 │           84 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │           84 │
│         1 │ 2026-01-01 03:00:00 │    23 │           84 │
│         2 │ 2026-01-01 00:00:00 │    18 │         75.5 │
│         2 │ 2026-01-01 01:00:00 │    18 │         75.5 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │         75.5 │
│         2 │ 2026-01-01 03:00:00 │    17 │         75.5 │
└───────────┴─────────────────────┴───────┴──────────────┘

The totals are the same ones GROUP BY sensor_id gave, but now every original row is still there. That is the typical use: “each row, plus something about its group”, such as the share of the group total.

Named windows

When several functions share a window, define it once with WINDOW and refer to it by name:

SELECT sensor_id, ts, value,
       sum(value) OVER w AS sensor_total,
       avg(value) OVER w AS sensor_avg
FROM metrics
WINDOW w AS (PARTITION BY sensor_id)
ORDER BY sensor_id, ts;

This is not just shorter. It also removes the risk of two copies of the window definition drifting apart.

ORDER BY inside OVER is not the query ORDER BY

The final ORDER BY of a query only reorders the result rows. The ORDER BY inside OVER defines the order of rows within the window, and it changes the values the function computes.

Adding it to an aggregate makes the window grow row by row, which gives a running total:

SELECT sensor_id, ts, value,
       sum(value) OVER (PARTITION BY sensor_id ORDER BY ts) AS running
FROM metrics ORDER BY sensor_id, ts;

┌─sensor_id─┬──────────────────ts─┬─value─┬─running─┐
│         1 │ 2026-01-01 00:00:00 │  20.5 │    20.5 │
│         1 │ 2026-01-01 01:00:00 │    21 │    41.5 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │      61 │
│         1 │ 2026-01-01 03:00:00 │    23 │      84 │
│         2 │ 2026-01-01 00:00:00 │    18 │      18 │
│         2 │ 2026-01-01 01:00:00 │    18 │      36 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │    58.5 │
│         2 │ 2026-01-01 03:00:00 │    17 │    75.5 │
└───────────┴─────────────────────┴───────┴─────────┘

Two groups of functions react differently to ordering:

Order-insensitive aggregates (sum, min, max, avg, any) give the same answer for a given set of rows no matter how they are sorted. Ordering matters for them only because it changes which rows are in the frame (next part).
Order-sensitive functions (row_number, rank, dense_rank, first_value, last_value, lagInFrame, leadInFrame) have no meaning without an order.

Without ORDER BY, ranking functions have nothing to rank on. rank() OVER () returns 1 for every row, because all rows are peers. row_number() OVER (ORDER BY ts) numbers the rows in ts order across the whole result, ignoring sensors:

SELECT sensor_id, ts, value, rank() OVER () AS r_nowin, row_number() OVER (ORDER BY ts) AS rn
FROM metrics ORDER BY sensor_id, ts;

┌─sensor_id─┬──────────────────ts─┬─value─┬─r_nowin─┬─rn─┐
│         1 │ 2026-01-01 00:00:00 │  20.5 │       1 │  1 │
│         1 │ 2026-01-01 01:00:00 │    21 │       1 │  3 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │       1 │  5 │
│         1 │ 2026-01-01 03:00:00 │    23 │       1 │  7 │
│         2 │ 2026-01-01 00:00:00 │    18 │       1 │  2 │
│         2 │ 2026-01-01 01:00:00 │    18 │       1 │  4 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │       1 │  6 │
│         2 │ 2026-01-01 03:00:00 │    17 │       1 │  8 │
└───────────┴─────────────────────┴───────┴─────────┴────┘

The rn column also shows that rows with the same ts are numbered arbitrarily: ties in ORDER BY give no guarantee about which tied row comes first. Add a tiebreaker column if you need a stable result.

Where window functions can be used

Window functions are evaluated after WHERE, GROUP BY and HAVING, just before the final ORDER BY and LIMIT. They are allowed in SELECT and the final ORDER BY only:

SELECT sum(value) OVER () FROM metrics WHERE sum(value) OVER () > 1;
-- Code: 184. Window function sum(value) OVER () is found in WHERE in query. (ILLEGAL_AGGREGATION)

To filter on a window result, wrap the query in a subquery or CTE and filter outside. Part 2 shows this for top-N per group.

Recap

OVER turns a function into a window function; the result keeps all rows.
PARTITION BY is like GROUP BY without collapsing rows.
ORDER BY inside OVER orders the window, not the output.
Use WINDOW name AS (...) to share one definition between several functions.

Next: frames, ranking and lag/lead. Reference: window functions in the docs.