Shape / rows · All operations

Number runs of equal values

A variant of Number or rank rows

Add a number that restarts, or an id that changes, whenever the value of chosen columns changes from one row to the next.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Number each run of equal values, restarting when the value changes

SQL
CREATE TEMP TABLE readings AS SELECT * FROM (VALUES
  (1, 'a', 'up'), (2, 'a', 'up'), (3, 'b', 'up'), (4, 'b', 'down'), (5, 'b', 'down'),
  (6, 'a', 'down'), (7, NULL, 'down'), (8, NULL, 'down'), (9, 'a', 'down')
) t(seq, station, status);
-- a new run starts at the first row and wherever station or status differs from the row before; NULLs equal NULLs
CREATE TEMP TABLE changes AS
SELECT *, (row_number() OVER w = 1 OR (station, status) IS DISTINCT FROM (lag(station) OVER w, lag(status) OVER w))::INT AS starts_run
FROM readings WINDOW w AS (ORDER BY seq);
CREATE TEMP TABLE result AS
SELECT seq, station, status, run_id, row_number() OVER (PARTITION BY run_id ORDER BY seq) AS position_in_run
FROM (SELECT *, sum(starts_run) OVER (ORDER BY seq) AS run_id FROM changes);
SELECT * FROM result ORDER BY seq;
SELECT CASE WHEN ((SELECT list(run_id ORDER BY seq) FROM result) = [1, 1, 2, 3, 3, 4, 5, 5, 6] AND (SELECT list(position_in_run ORDER BY seq) FROM result) = [1, 2, 1, 1, 2, 1, 1, 2, 1]) THEN 'as expected' ELSE error('the nine readings were not six runs numbered 1, 1, 2, 3, 3, 4, 5, 5, 6, restarting the position in each run') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the nine readings were not six runs numbered 1, 1, 2, 3, 3, 4, 5, 5, 6, restarting the position in each run”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
Pentaho Data Integration (1)
Polars (1)
  • Expr.rle_idread 2026-09-27

    Give every run of identical consecutive values its own integer id

VisiData (1)
  • addcol-incr-keyread 2026-09-23

    Add running number that restarts whenever the key column values change

Background

In Polars, rle_id gives a distinct integer id to each run of identical values, starting at 0 and increasing by one each time the value changes.[1] Apache Hop's Add Fields Changing Sequence transform adds a sequence value that resets each time a value changes in a list of specified fields.[2] In run-length encoding (RLE), a run is a sequence of consecutive occurrences of the same data value.[3]

When to use it

In Apache Hop, the sequence is reset to its start value if a value in one or more of the specified fields changes compared to the previous row.[2] Reach for run numbers when a value can come back later and each return should count as a new spell, such as a status that goes up, down and up again. (Meridian's view)

In DuckDB, ORDER BY may not use a stable sorting algorithm, so it does not guarantee that the row order is preserved.[4] Runs exist only in an order, so sort by a column that fixes it, such as a timestamp with a tie-breaker, before numbering them. (Meridian's view)

In PostgreSQL, ordinary comparison operators yield null when either input is null, while IS DISTINCT FROM treats null as a normal value and returns false when both inputs are null.[5] Compare each row with the one before it using IS DISTINCT FROM, so that two missing values in a row stay in one run. (Meridian's view)

The method

Run-length encoding is a form of lossless data compression in which a run is stored as a single occurrence of its value and a count of its consecutive occurrences.[3] In PostgreSQL, lag returns a value from the row a given offset before the current row within the partition, or a default, NULL if omitted, when there is no such row.[6] In PostgreSQL, an aggregate used as a window function with ORDER BY and the default window frame produces a running sum type of behaviour.[6] In SQL, mark each row whose values differ from the row before it, take a running sum of the marks to give each run its number, and number the rows within each run to give each row's position. (Meridian's view)

In PostgreSQL, when ORDER BY is supplied, the default window frame runs from the start of the partition through the current row plus any following rows equal to it according to the ORDER BY.[7] Order the running sum by a unique column, or give it a ROWS frame, so that rows tied on the ordering do not share one total. (Meridian's view)

Compared with the alternatives

Polars' rle_id is especially useful for defining a new group every time a column's value changes, rather than for every distinct value of that column.[1] Use a dense rank or a group key when every return of a value should share one number, and a run id when each return should get a new one. (Meridian's view)

Sources

  1. [1]polars.Expr.rle_id — Polars documentationdocumentation
  2. [2]Add value fields changing sequence | Apache Hopdocumentation
  3. [3]Run-length encoding - Wikipediasecondary source
  4. [4]Order Preservation – DuckDBdocumentation
  5. [5]PostgreSQL: Documentation: 18: 9.2. Comparison Functions and Operatorsdocumentation
  6. [6]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  7. [7]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation