clickhouse.guru
Tutorials· intermediate· Tested on ClickHouse 26.4

Window functions: frames, ranking and lag/lead

ROWS vs RANGE frames, running and moving averages, row_number/rank/dense_rank, lagInFrame/leadInFrame and top-N per group.

Part 1 introduced OVER, PARTITION BY and ORDER BY. This part is about the piece that decides exactly which rows a function sees: the frame. It then uses frames for moving averages, ranking, deltas between neighbouring rows and top-N per group.

The examples use the metrics table from part 1 (eight hourly readings from two sensors).

Window, partition, frame

The structure is nested:

window  ->  partitions (PARTITION BY)  ->  frame inside the partition (ORDER BY + ROWS/RANGE)  ->  rows

For every row, the function is evaluated over that row’s frame. The frame is a slice of the row’s partition, anchored on the current row:

OVER (PARTITION BY sensor_id ORDER BY ts
      ROWS BETWEEN <start> AND <end>)

Start and end can be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.

ROWS versus RANGE

  • ROWS counts physical rows: “the previous two rows”.
  • RANGE compares the value of the ORDER BY column: “all rows whose ts is within the last hour”. Rows with an equal sort value (“peers”) are always in or out together.

The default frame

If you give no frame:

  • Without ORDER BY, the frame is the whole partition.
  • With ORDER BY, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: everything from the start of the partition up to the current row, including its peers.

The peers detail bites when the sort column has ties. Sensor 2 has two readings of 18:

SELECT ts, value,
       sum(value) OVER (ORDER BY value) AS default_frame,
       sum(value) OVER (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM metrics WHERE sensor_id = 2 ORDER BY value, ts;
┌──────────────────ts─┬─value─┬─default_frame─┬─rows_frame─┐
│ 2026-01-01 03:00:00 │    17 │            17 │         17 │
│ 2026-01-01 00:00:00 │    18 │            53 │         35 │
│ 2026-01-01 01:00:00 │    18 │            53 │         53 │
│ 2026-01-01 02:00:00 │  22.5 │          75.5 │       75.5 │
└─────────────────────┴───────┴───────────────┴────────────┘

With the default RANGE frame both 18 rows see each other, so both show 53. With ROWS the running total advances one physical row at a time. If you want a “running total” that never has plateaus, order by a unique column or spell out ROWS.

Moving averages

A frame of “this row and the one before”:

SELECT sensor_id, ts, value,
       round(avg(value) OVER (PARTITION BY sensor_id ORDER BY ts
                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS avg_2
FROM metrics ORDER BY sensor_id, ts;
┌─sensor_id─┬──────────────────ts─┬─value─┬─avg_2─┐
│         1 │ 2026-01-01 00:00:00 │  20.5 │  20.5 │
│         1 │ 2026-01-01 01:00:00 │    21 │ 20.75 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │ 20.25 │
│         1 │ 2026-01-01 03:00:00 │    23 │ 21.25 │
│         2 │ 2026-01-01 00:00:00 │    18 │    18 │
│         2 │ 2026-01-01 01:00:00 │    18 │    18 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │ 20.25 │
│         2 │ 2026-01-01 03:00:00 │    17 │ 19.75 │
└───────────┴─────────────────────┴───────┴───────┘

Use RANGE when you care about time distance and rows may be missing. With a DateTime order column the offset is in seconds, so a trailing one-hour sum is:

SELECT ts, value,
       sum(value) OVER (ORDER BY ts RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW) AS last_hour
FROM metrics WHERE sensor_id = 1 ORDER BY ts;
┌──────────────────ts─┬─value─┬─last_hour─┐
│ 2026-01-01 00:00:00 │  20.5 │      20.5 │
│ 2026-01-01 01:00:00 │    21 │      41.5 │
│ 2026-01-01 02:00:00 │  19.5 │      40.5 │
│ 2026-01-01 03:00:00 │    23 │      42.5 │
└─────────────────────┴───────┴───────────┘

Ranking: row_number, rank, dense_rank

All three need an ORDER BY. They differ in how they treat ties:

  • row_number() gives each row a unique number; ties are broken arbitrarily.
  • rank() gives tied rows the same number and leaves a gap afterwards.
  • dense_rank() gives tied rows the same number with no gap.
SELECT sensor_id, ts, value,
       row_number() OVER w AS rn, rank() OVER w AS rk, dense_rank() OVER w AS drk
FROM metrics
WINDOW w AS (PARTITION BY sensor_id ORDER BY value DESC)
ORDER BY sensor_id, rn;
┌─sensor_id─┬──────────────────ts─┬─value─┬─rn─┬─rk─┬─drk─┐
│         1 │ 2026-01-01 03:00:00 │    23 │  1 │  1 │   1 │
│         1 │ 2026-01-01 01:00:00 │    21 │  2 │  2 │   2 │
│         1 │ 2026-01-01 00:00:00 │  20.5 │  3 │  3 │   3 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │  4 │  4 │   4 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │  1 │  1 │   1 │
│         2 │ 2026-01-01 00:00:00 │    18 │  2 │  2 │   2 │
│         2 │ 2026-01-01 01:00:00 │    18 │  3 │  2 │   2 │
│         2 │ 2026-01-01 03:00:00 │    17 │  4 │  4 │   3 │
└───────────┴─────────────────────┴───────┴────┴────┴─────┘

Look at sensor 2: the two 18 readings share rank 2, the next rank is 4 but the next dense_rank is 3.

Top N per group

Window functions cannot be used in WHERE, so compute the rank in a subquery and filter outside:

SELECT sensor_id, ts, value FROM (
    SELECT *, row_number() OVER (PARTITION BY sensor_id ORDER BY value DESC) AS rn
    FROM metrics)
WHERE rn <= 2
ORDER BY sensor_id, rn;

ClickHouse has a dedicated, usually cheaper, alternative for plain “top N per key”, LIMIT n BY:

SELECT sensor_id, ts, value FROM metrics
ORDER BY sensor_id, value DESC
LIMIT 2 BY sensor_id;

Both return the same four rows here. Reach for row_number() when you also need the rank itself, or rank/dense_rank tie semantics.

Neighbouring rows: lag, lead and the InFrame variants

lag(x, offset, default) returns x from offset rows before the current one, lead looks forward. They ignore the frame and work on the whole ordered partition, so a plain window is enough:

SELECT sensor_id, ts, value,
       lag(value)  OVER w AS prev,
       value - lag(value) OVER w AS delta,
       lead(value) OVER w AS next
FROM metrics
WINDOW w AS (PARTITION BY sensor_id ORDER BY ts)
ORDER BY sensor_id, ts;

ClickHouse also has lagInFrame and leadInFrame, which are limited to the frame. With the default frame (which ends at the current row) leadInFrame never sees a following row and returns the default value everywhere, so it needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. The old neighbor() function is deprecated; use one of these instead. Prefer lag/lead unless you really want the frame to limit the lookup.

┌─sensor_id─┬──────────────────ts─┬─value─┬─prev─┬─delta─┬─next─┐
│         1 │ 2026-01-01 00:00:00 │  20.5 │    0 │  20.5 │   21 │
│         1 │ 2026-01-01 01:00:00 │    21 │ 20.5 │   0.5 │ 19.5 │
│         1 │ 2026-01-01 02:00:00 │  19.5 │   21 │  -1.5 │   23 │
│         1 │ 2026-01-01 03:00:00 │    23 │ 19.5 │   3.5 │    0 │
│         2 │ 2026-01-01 00:00:00 │    18 │    0 │    18 │   18 │
│         2 │ 2026-01-01 01:00:00 │    18 │   18 │     0 │ 22.5 │
│         2 │ 2026-01-01 02:00:00 │  22.5 │   18 │   4.5 │   17 │
│         2 │ 2026-01-01 03:00:00 │    17 │ 22.5 │  -5.5 │    0 │
└───────────┴─────────────────────┴───────┴──────┴───────┴──────┘

The first prev and the last next are 0, not NULL: with no row to read, ClickHouse returns the column’s default value. That makes the first delta a bogus 20.5. Pass an explicit third argument (lag(value, 1, value) repeats the current value), or make the column nullable, if a missing neighbour must be distinguishable from a real zero.

The same trap applies to last_value: with the default frame it returns the current row’s value, because the frame ends there.

SELECT sensor_id, ts, value,
       first_value(value) OVER (PARTITION BY sensor_id ORDER BY ts) AS first_v,
       last_value(value)  OVER (PARTITION BY sensor_id ORDER BY ts) AS last_default
FROM metrics ORDER BY sensor_id, ts;

In the output last_default equals value on every row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the real last value of the partition.

Cheat sheet

You want Use
Running total sum(x) OVER (PARTITION BY k ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Share of group total x / sum(x) OVER (PARTITION BY k)
Moving average of N rows avg(x) OVER (... ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW)
Time-based trailing window RANGE BETWEEN <seconds> PRECEDING AND CURRENT ROW on a DateTime order column
Difference to previous row x - lag(x) OVER (PARTITION BY k ORDER BY t)
Rank with / without gaps rank() / dense_rank()
Top N per group LIMIT n BY k, or row_number() in a subquery

Reference: window functions in the docs.

sqlwindow-functions

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.