Shape · Time series

Rolling Window

Adds moving statistics over the last N rows: sum, average, standard deviation, min, max, count or median. For example, a 7-day moving average.

Also known as: Moving average

How it works

Group by restarts the frame per group, and Order by, which is required, sets the row order. Window is the frame size in rows (3 by default): each row is combined with the rows before it up to that count. Min periods defaults to the window; a row whose frame holds fewer non-null values than that gets NULL, so with the default the first window minus one rows of each group are NULL. Each operation picks a statistic (Sum, Count, Average, Min, Max, Median or Std dev) and a column; the result is appended as, for example, Rolling 7-Mean of sales unless you name it.

Inputs and outputs

Settings

{
  operations: [{ statistic: "sum"|"mean"|"std"|"min"|"max"|"count"|"median", column: string, outputColumn?: string }]
  window: number
  minPeriods?: number
  orderBy: [{ column, direction }]
  groupBy?: string[]
}

Example

A 7-day moving average of daily sales per store: window 7, order by day, group by store, one operation with statistic Average on sales and output sales_7d. Set Min periods to 1 if you want a value from the first day, averaged over the rows available so far.

More in Time series

• Shift / Lag — Copies a value from N rows earlier or later onto each row, so you can compare a row with the one before it. For example, last month's sales next to this month's.
• Diff / Pct Change — Compares each row with the one N periods earlier and writes the difference or percent change. For example, month-over-month growth per store.
• Resample — Rolls a time series up into regular buckets, from minutes to years, with a total or other summary per bucket. Empty buckets can be left out, carried forward or set to zero.
• Exponential Weighted Mean — Smooths a number column so recent rows count more than older ones. Gives a weighted mean, standard deviation or variance.
• Forecast — Forecasts the next periods of a measure from a dated series, one forecast per group, with a lower and upper band. It can also backtest the recent past to show how accurate the forecast would have been.
• Sessionize — Groups events into sessions: a new session starts when the gap since the last event is longer than you set, such as 30 minutes.