Window Columns

The windows property declares ordered, partitioned rolling computations over the rows of a Table — moving averages, cumulative sums, period-over-period differences — analogous to SQL window functions.

Window Columns are declared per-View, keyed by output alias, exactly as expressions are:

const view = await table.view({
    columns: ["10-tick avg Sales"],
    windows: {
        "10-tick avg Sales": {
            column: "Sales",
            aggregate: "avg",
            rows: 10,
        },
    },
});

view = table.view(
    columns=["10-tick avg Sales"],
    windows={
        "10-tick avg Sales": {
            "column": "Sales",
            "aggregate": "avg",
            "rows": 10,
        }
    },
)

Each window produces a new column which may be used anywhere a Table column can — in columns, filter, sort, group_by, and so on. An alias must not collide with a Table column, an expression alias, or another window’s key.

Window Columns update incrementally as the Table updates, including rows outside an update batch whose window frames were affected by it.

Spec fields

Field	Type	Description
`column`	`string`	The input column — a `Table` column or an expression alias from the same config
`aggregate`	`string`	The window function to apply (see below)
`partition_by`	`string[]`	Columns whose distinct value tuples partition the rows; omitted partitions the whole `Table` as one group
`order_by`	`[string, "asc" \| "desc"]`	The column which orders each partition, and its direction
`rows`	`integer`	Frame of the N rows preceding each row, plus the row itself
`range`	`number`	Frame of rows whose `order_by` value lies within `range` of each row’s
`cumulative`	`true`	Frame of all rows from the partition start through each row
`offset`	`integer`	Row offset for `lag`/`lead` (default `1`)
`alpha`	`number`	Smoothing factor in `(0, 1]` for `ema`

rows, range and cumulative are mutually exclusive — supplying more than one is an error. range requires a numeric or temporal order_by.

order_by orders rows within the window frame only. It does not reorder the View — that is what the view-level sort property does.

Aggregates

Aggregate	Description	Result type
`sum`, `avg`	Rolling sum and mean over the frame	`float`
`stddev`, `var`	Rolling standard deviation and variance	`float`
`count`	Number of non-null values in the frame	`integer`
`min`, `max`	Smallest and largest value in the frame	input type
`lag`, `lead`	Value `offset` rows behind or ahead	input type
`diff`	This row’s value minus the value `offset` rows behind	`float`
`rate`	Rate of change across the frame	`float`
`ema`	Exponential moving average, smoothed by `alpha`	`float`

sum, avg, stddev, var, diff, rate and ema require a numeric input column.

Frame compatibility

sum, avg, count, min, max, stddev and var accept any frame.
lag, lead, diff and ema are frame-independent — they are computed from row offsets rather than a frame.
rate requires a range frame, and is invalid with rows or cumulative.

The first and last window aggregates are declared in the type definitions but are not yet implemented by the engine; a View which uses them will be rejected.

Examples

Moving average over a fixed row count

A 10-tick moving average, over the whole table in its natural order:

{
    "columns": ["10-tick avg Sales"],
    "windows": {
        "10-tick avg Sales": {
            "column": "Sales",
            "aggregate": "avg",
            "rows": 10
        }
    }
}

Moving average over a time range

A 5-second moving average, framing rows by their Order Date rather than by count:

{
    "columns": ["5s avg Sales"],
    "windows": {
        "5s avg Sales": {
            "column": "Sales",
            "aggregate": "avg",
            "order_by": ["Order Date", "asc"],
            "range": 5000
        }
    }
}

Cumulative sum

A running total from the start of each partition:

{
    "columns": ["Cumulative Sales"],
    "windows": {
        "Cumulative Sales": {
            "column": "Sales",
            "aggregate": "sum",
            "order_by": ["Order Date", "asc"],
            "cumulative": true
        }
    }
}

Period-over-period change, per group

partition_by restarts the window at each new Region, so each region’s first row has no predecessor to difference against:

{
    "columns": ["Region", "Sales", "Sales Δ"],
    "windows": {
        "Sales Δ": {
            "column": "Sales",
            "aggregate": "diff",
            "partition_by": ["Region"],
            "order_by": ["Order Date", "asc"]
        }
    }
}

Support

Window Columns are implemented by Perspective’s built-in engine, by the DuckDB, ClickHouse and Polars Virtual Servers, and by the <perspective-viewer> UI. Virtual Servers advertise support through their features declaration, so the UI control is hidden for backends which do not implement it.

Keyboard shortcuts

Perspective