Shape / table · All operations

Compute a moving average

A variant of Calculate across rows

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

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Average over a sliding window of three rows and over the last seven days

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,
       round(avg(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 4) AS avg_3_rows,
       round(avg(amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW), 4) AS avg_7_days
FROM orders
WHERE amount IS NOT NULL;
SELECT * FROM result ORDER BY order_date, order_id;
SELECT CASE WHEN ((SELECT list(avg_3_rows ORDER BY order_date, order_id) FROM result) = [120.0, 100.25, 166.8333, 141.9167, 285.0833, 218.4133, 373.33, 208.33, 248.3333] AND (SELECT list(avg_7_days ORDER BY order_date, order_id) FROM result) = [120.0, 166.8333, 166.8333, 277.625, 277.625, 99.99, 510.0, 262.5, 220.0]) THEN 'as expected' ELSE error('the three-row and seven-day moving averages were not the expected values') 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 three-row and seven-day moving averages were not the expected values”.
  • 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 NiFi (1)
  • AttributeRollingWindowread 2026-09-27

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

EasyMorph (1)
  • Sliding aggregationread 2026-09-27

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

KNIME Analytics Platform (2)
  • 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

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

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

  • 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.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

  • 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 (1)
  • Moving Average Filterread 2026-09-27

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

Tableau Prep (1)
  • Moving Calculationread 2026-09-27

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

Trifacta (16)
VisiData (1)
  • addcol-windowread 2026-09-23

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

Background

A moving average analyses data points by creating a series of averages of different selections of the full data set, shifting the selection forward each time.[1] A moving average is commonly used with time series data to smooth out short-term fluctuations and highlight longer-term trends or cycles.[1] A moving average is also called a rolling average, running average, moving mean or rolling mean.[1]

When to use it

The period chosen for a simple moving average depends on the type of movement of interest, such as short, intermediate or long-term.[1] If the data used are not centred around the mean, a simple moving average lags behind the latest datum by half the sample width.[1]

In PostgreSQL, a ROWS frame offset is a number of rows before or after the current row, while a RANGE offset is the maximum difference in the value of the ordering column.[2] In DuckDB, a seven-day moving average can use a RANGE frame of INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING, which handles any missing days.[3]

The method

In financial applications, a simple moving average is the unweighted mean of the previous k data points.[1] The next simple moving average can reuse the previous one, as a new value comes into the sum and the oldest value drops out.[1] Mathematically, a moving average is a type of convolution, and in signal processing it is viewed as a low-pass finite impulse response filter.[1]

An exponential moving average (EMA), also known as an exponentially weighted moving average (EWMA), applies weights that decrease exponentially for each older datum, never reaching zero.[1] The technique of moving averages was invented by the Bank of England in 1833 to conceal the state of its bullion reserves.[1]

Compared with the alternatives

In Apache Beam, sliding time windows, such as 60-second windows starting every 30 seconds, are useful for a running average of the past 60 seconds updated every 30 seconds.[4] Choose a per-row moving average when each reading needs its own smoothed value, and a stream's sliding window when one figure per period is enough. (Meridian's view)

Sources

  1. [1]Moving average - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation
  3. [3]Window Functions – DuckDBdocumentation
  4. [4]Beam Programming Guidedocumentation