Shape / table · All operations

Calculate across rows

Running total, lag, lead, moving average, multi-row formula.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Running total, lag and moving average

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, order_date, amount,
       sum(amount) OVER w_run AS running_total,
       lag(amount) OVER (ORDER BY order_date, order_id) AS previous,
       round(avg(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM orders
WINDOW w_run AS (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(running_total) FILTER (WHERE order_id = 10) = 1900.74 AND max(previous) FILTER (WHERE order_id = 2) = 120 AND max(moving_avg_3) FILTER (WHERE order_id = 3) = 166.83 FROM result)) THEN 'as expected' ELSE error('the running total, the previous value or the moving average is wrong') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the running total, the previous value or the moving average is wrong”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

  • Repeat a group total on every row

    Aggregate over the whole partition or table and put the result on every row, for shares of total and comparisons with the group.

  • Compute a moving average

    Aggregate over a window of a fixed number of rows, or a span of time, that slides along with the current row.

  • Take a value from another row

    Bring a value from a row a fixed offset before or after the current one, or from the first, last or nth row of the window, often to compute a difference or percentage change.

  • Compute a running total

    Accumulate a sum, count, average, minimum or maximum from the first row of the partition down to the current row.

  • Assign session ids

    Number the sessions in ordered events, starting a new session whenever the gap between consecutive timestamps exceeds a limit.

  • Group streaming rows into time windows

    Assign rows of a stream to fixed, sliding or session windows of event time, so later steps emit one result per window.

Define the window

Set the partition, order and frame a window calculation runs over, or apply a general window step or function family.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Multi-Row Formularead 2026-09-27

    Compute a field from values in preceding or following rows: running totals, row differences

  • Running Totalread 2026-09-27

    Compute a cumulative sum on numeric fields, optionally per group

Apache DataFusion (10)
  • WINDOW clauseread 2026-09-27

    Define named window specifications for window functions to reference

  • Window Functions: Custom Windowread 2026-09-27

    Evaluate a function over an explicit frame such as ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING

  • Window Functions: Empty (OVER())read 2026-09-27

    Evaluate a function over the whole result as one window

  • Window Functions: Partitioning and orderingread 2026-09-27

    Evaluate a function over PARTITION BY groups in ORDER BY order

  • first_valueread 2026-09-27

    Window: Value at the first row of the window frame

  • lagread 2026-09-27

    Window: Value at a row offset before the current row in the partition, with a default

  • last_valueread 2026-09-27

    Window: Value at the last row of the window frame

  • leadread 2026-09-27

    Window: Value at a row offset after the current row in the partition, with a default

  • nth_valueread 2026-09-27

    Window: Value at the nth row of the window frame

  • windowread 2026-09-27

    DataFrame method: Add the results of window functions to the existing columns

Apache Hop (3)
  • Analytic Queryread 2026-09-27

    Lead, lag, first and last values across sorted rows within groups

  • Beam timestampread 2026-09-27

    Assign an event-time timestamp to each row of a streaming Beam pipeline

  • Beam windowread 2026-09-27

    Group streaming rows into fixed, sliding, session or global event-time windows on Beam

Apache NiFi (1)
  • AttributeRollingWindowread 2026-09-27

    Keep a rolling window over an expression evaluated per FlowFile; emit the count and aggregate as attributes

Azure Data Factory (2)
  • Windowread 2026-09-27

    Compute window-based aggregations over partitions of the stream

  • Window functionsread 2026-09-27

    7 functions: cumeDist, denseRank, lag, lead, nTile, …

Bruin (4)
  • lagread 2026-09-27

    Previous value of a referenced metric, offset 1 by default

  • leadread 2026-09-27

    Next value of a referenced metric, offset 1 by default

  • percent_of_totalread 2026-09-27

    Referenced metric divided by the window total

  • running_totalread 2026-09-27

    Running sum of a referenced metric over an ordered window

DuckDB (7)
Duckle (8)
  • Cumulativeread 2026-09-25

    running sum, avg, count, min or max over an ordered window

  • First Valueread 2026-09-25

    FIRST_VALUE over a window

  • Lagread 2026-09-25

    LAG value over a window

  • Last Valueread 2026-09-25

    LAST_VALUE over a window

  • Leadread 2026-09-25

    LEAD value over a window

  • Sessionizeread 2026-09-25

    assign a session id by inactivity gap within a partition

  • Tumbling Windowread 2026-09-25

    event-time tumbling windows held across runs until a watermark closes them

  • Window Aggregateread 2026-09-25

    aggregate over a window and keep every row

EasyMorph (5)
  • Distribute totalread 2026-09-27

    Break a total down across rows in proportion to a column of weights

  • Running totalread 2026-09-27

    Compute rank, cumulative count, running sum or share of total, table-wide or within groups

  • Shift column up/downread 2026-09-27

    Shift a column's values up or down by rows, leaving empty cells at the vacated end

  • Sliding aggregationread 2026-09-27

    Compute an aggregate such as average or sum over a sliding window of N values

  • Total/subtotalsread 2026-09-27

    Add a column holding the column total, or the subtotal of each row's group

Ibis (5)
  • Lagread 2026-09-27

    Value from a row a fixed offset earlier in the ordered window

  • Leadread 2026-09-27

    Value from a row a fixed offset later in the ordered window

  • NthValueread 2026-09-27

    Pick the Nth value of a column within the window

  • WindowBoundaryread 2026-09-27

    One bound of a window frame: an offset, preceding or following

  • WindowFunctionread 2026-09-27

    Evaluate an analytic or aggregate function over a rows or range frame with partition and order

KNIME Analytics Platform (3)
  • Lag Columnread 2026-09-27

    Copy values from preceding rows into the current row as one or more lagged columns

  • Moving Aggregatorread 2026-09-27

    Compute aggregations over a moving window of rows into new columns

  • Moving Averageread 2026-09-27

    Compute a moving average of a column with a chosen window type

OpenRefine (1)
Pentaho Data Integration (1)
  • Analytic queryread 2026-09-27

    Lead, lag, first and last values over a sorted dataset

Polars (48)
  • DataFrame.rollingread 2026-09-27

    Group rows into rolling windows anchored on each value of a temporal or integer column

  • DataFrame.shiftread 2026-09-27

    Shift every column by n rows, filling the gap with null or a fill value

  • Expr.cum_countread 2026-09-27

    Running count of the non-null values down the column

  • Expr.cum_maxread 2026-09-27

    Running maximum down the column

  • Expr.cum_minread 2026-09-27

    Running minimum down the column

  • Expr.cum_prodread 2026-09-27

    Running product down the column

  • Expr.cum_sumread 2026-09-27

    Running sum down the column

  • Expr.cumulative_evalread 2026-09-27

    Evaluate an expression over a window that grows by one row at each step

  • Expr.diffread 2026-09-27

    Difference between each value and the value n rows before it

  • Expr.ewm_meanread 2026-09-27

    Exponentially weighted moving average

  • Expr.ewm_mean_byread 2026-09-27

    Exponentially weighted moving average with decay set by the gaps in a time column

  • Expr.ewm_stdread 2026-09-27

    Exponentially weighted moving standard deviation

  • Expr.ewm_sumread 2026-09-27

    Exponentially weighted moving sum

  • Expr.ewm_sum_byread 2026-09-27

    Exponentially weighted moving sum with decay set by the gaps in a time column

  • Expr.ewm_varread 2026-09-27

    Exponentially weighted moving variance

  • Expr.overread 2026-09-27

    Evaluate an expression within partitions and map the result back onto every row

  • Expr.pct_changeread 2026-09-27

    Percentage change between each value and the value n rows before it

  • Expr.rollingread 2026-09-27

    Evaluate an expression over rolling windows anchored on a temporal or integer column

  • Expr.rolling_kurtosisread 2026-09-27

    Moving kurtosis over a window of a fixed number of rows

  • Expr.rolling_mapread 2026-09-27

    Apply a user function over a moving window of a fixed number of rows

  • Expr.rolling_maxread 2026-09-27

    Moving maximum over a window of a fixed number of rows

  • Expr.rolling_max_byread 2026-09-27

    Moving maximum over a window measured on another, temporal or integer, column

  • Expr.rolling_meanread 2026-09-27

    Moving mean over a window of a fixed number of rows

  • Expr.rolling_mean_byread 2026-09-27

    Moving mean over a window measured on another, temporal or integer, column

  • Expr.rolling_medianread 2026-09-27

    Moving median over a window of a fixed number of rows

  • Expr.rolling_median_byread 2026-09-27

    Moving median over a window measured on another, temporal or integer, column

  • Expr.rolling_minread 2026-09-27

    Moving minimum over a window of a fixed number of rows

  • Expr.rolling_min_byread 2026-09-27

    Moving minimum over a window measured on another, temporal or integer, column

  • Expr.rolling_quantileread 2026-09-27

    Moving quantile over a window of a fixed number of rows

  • Expr.rolling_quantile_byread 2026-09-27

    Moving quantile over a window measured on another, temporal or integer, column

  • Expr.rolling_rankread 2026-09-27

    Moving rank over a window of a fixed number of rows

  • Expr.rolling_rank_byread 2026-09-27

    Moving rank over a window measured on another, temporal or integer, column

  • Expr.rolling_skewread 2026-09-27

    Moving skewness over a window of a fixed number of rows

  • Expr.rolling_stdread 2026-09-27

    Moving standard deviation over a window of a fixed number of rows

  • Expr.rolling_std_byread 2026-09-27

    Moving standard deviation over a window measured on another, temporal or integer, column

  • Expr.rolling_sumread 2026-09-27

    Moving sum over a window of a fixed number of rows

  • Expr.rolling_sum_byread 2026-09-27

    Moving sum over a window measured on another, temporal or integer, column

  • Expr.rolling_varread 2026-09-27

    Moving variance over a window of a fixed number of rows

  • Expr.rolling_var_byread 2026-09-27

    Moving variance over a window measured on another, temporal or integer, column

  • Expr.shiftread 2026-09-27

    Shift values by n rows, filling the gap with null or a fill value

  • SQL functions: Window (group)read 2026-09-27

    8 functions: DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE, LEAD and others; SQL window functions

  • cum_countread 2026-09-27

    Running count of the non-null values of the named columns

  • cum_foldread 2026-09-27

    Running left fold across the columns of each row with a user function and a start value

  • cum_reduceread 2026-09-27

    Running left fold across the columns of each row with a user function, no start value

  • cum_sumread 2026-09-27

    Running sum of the named columns

  • cum_sum_horizontalread 2026-09-27

    Running sum across the columns of each row, returned as a struct

  • rolling_corrread 2026-09-27

    Moving correlation between two columns over a window of rows

  • rolling_covread 2026-09-27

    Moving covariance between two columns over a window of rows

RapidMiner Studio (4)
  • Generate Session IDread 2026-09-27

    Add a session id that increments when the gap between consecutive dates exceeds a maximum

  • Lagread 2026-09-27

    Shift series columns by a number of rows to create lagged copies; negative lags allowed

  • Moving Average Filterread 2026-09-27

    Apply a moving-window aggregation (weighted average and others) to series columns

  • Windowingread 2026-09-27

    Turn a series into rows of windowed lag columns plus optional horizon columns

Tableau Prep (10)
  • Difference Fromread 2026-09-27

    Difference between the current row's value and the value N rows before or after

  • Fixed LODread 2026-09-27

    Aggregate at the level of chosen fields and repeat the result on every row of the group

  • LOOKUPread 2026-09-27

    Value of an expression in the row at a relative offset from the current row

  • Moving Calculationread 2026-09-27

    Moving sum or average over a number of rows before and after the current row

  • Percent Difference Fromread 2026-09-27

    Percentage difference between the current row's value and the value N rows away

  • RUNNING_AVGread 2026-09-27

    Running average from the partition's first row to the current row

  • RUNNING_SUMread 2026-09-27

    Running sum from the partition's first row to the current row

  • Running Calculationread 2026-09-27

    Cumulative sum or average of a value and the values before it

  • { FIXED }read 2026-09-27

    Level-of-detail expression: aggregate at the named dimensions, repeated on each row

  • { PARTITION }read 2026-09-27

    Clause setting the groups of rows an analytic function is computed within

Talend (2)
  • tMemorizeRowsread 2026-09-27

    Hold the last N rows in an indexed array so later components can refer to earlier rows

  • tWindowread 2026-09-27

    Apply a Spark window to the incoming data and pass the windowed data on

Trifacta (21)
VisiData (3)
  • addcol-aggregateread 2026-09-23

    Add column holding an aggregate over the rows that share the same key values

  • addcol-deltaread 2026-09-23

    (experimental) Add column with the difference from the previous row in current column

  • addcol-windowread 2026-09-23

    Add column holding a list of each row's value with N rows before and N after

Background

In SQL, a window function, or analytic function, is a function which uses values from one or multiple rows to return a value for each row.[1] An aggregate function, by contrast, returns a single value for multiple rows.[1] Window functions express queries such as time series analysis, ranking, percentiles, moving averages and cumulative sums.[2] Window functions were incorporated into the SQL:2003 standard and had their functionality expanded in later specifications.[1]

When to use it

In DuckDB, ordering a window is optional, but without it the results of general-purpose window functions and order-sensitive aggregates are not well-defined.[3] In PostgreSQL, row_number numbers tied rows in an unspecified order.[4] Give every window that depends on order an ORDER BY with no ties, adding a key such as an id as the last sort column, so the same data gives the same answer on every run. (Meridian's view)

In PostgreSQL, window functions are forbidden in WHERE, GROUP BY and HAVING, because they logically execute after those clauses.[4] In DuckDB, the QUALIFY clause filters on the results of window functions without the need for a subquery or WITH clause.[3] In DuckDB, window functions are blocking operators that require their entire input to be buffered, making them one of the most memory-intensive operators in SQL.[3]

The method

In DuckDB, windowing breaks a relation up into independent partitions, orders those partitions, and computes a new column for each row as a function of the nearby values.[3] In PostgreSQL, the OVER clause that follows a window function's arguments determines how the query's rows are split up for processing by the function.[4] In PostgreSQL, PARTITION BY divides the rows into partitions that share the same values of its expressions, and without PARTITION BY all rows form a single partition.[4]

In PostgreSQL, a row's window frame is a set of rows within its partition, and some window functions act only on the rows of the frame rather than the whole partition.[4] In PostgreSQL, a frame can be given in ROWS mode as a number of rows, in GROUPS mode as a number of peer groups, or in RANGE mode as a maximum difference in the ordering column.[5] In DuckDB and PostgreSQL, a WINDOW clause names a window definition so that several window functions can share it through OVER and the window's name.[3][4]

In 2015, Viktor Leis, Kan Kundhikanjana, Alfons Kemper and Thomas Neumann of Technische Universität München presented an efficient and general algorithm for the SQL window operator.[2] Oracle added window functions in version 8.1.6 in 2000, PostgreSQL in version 8.4 in 2009, and SQLite in release 3.25.0 in 2018.[1]

Compared with the alternatives

Window functions have an OVER clause, and any function without one is an aggregate or single-row (scalar) function instead.[1] In PostgreSQL, window functions execute after non-window aggregate functions, so an aggregate call may be an argument of a window function but not the other way round.[4]

Sources

  1. [1]Window function (SQL) - Wikipediasecondary source
  2. [2]Efficient Processing of Window Functions in Analytical SQL Queriessecondary source
  3. [3]Window Functions – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation
  5. [5]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation

Used in

One Protocol uses this operation: