Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Streaming pivot tables

A pivot table groups rows by one set of columns, splits them across another, and aggregates the cells. A streaming pivot table keeps that result correct as the underlying rows are inserted, updated and removed — without recomputing the whole pivot.

In Perspective a pivot is a View with group_by and split_by:

const view = await table.view({
    group_by: ["Region", "State"],
    split_by: ["Category"],
    columns: ["Sales", "Profit"],
    aggregates: { Sales: "sum", Profit: "avg" },
    sort: [["Sales", "desc"]],
});

When table.update() is called, the engine applies the delta to only the affected groups and notifies subscribers:

view.on_update(async (updated) => {
    const rows = await view.to_json();
}, { mode: "row" });

Loaded into <perspective-viewer>, the same configuration is an interactive pivot grid: users drag columns between Group By, Split By, Order By and Where, expand and collapse row groups, and switch to a chart of the same pivot.

await viewer.load(table);
await viewer.restore({
    plugin: "Datagrid",
    group_by: ["Region", "State"],
    split_by: ["Category"],
    columns: ["Sales", "Profit"],
});

What can be pivoted

  • Row pivots — any number of group_by levels, rendered as an expandable tree with subtotals at each level.
  • Column pivots — any number of split_by levels, rendered as grouped column headers.
  • Aggregates — sum, count, distinct count, average, weighted mean, median, min/max, first/last, standard deviation, variance and more, chosen per column.
  • Computed columns — expressions can be grouped, split, aggregated and filtered like any other column, so bucketing a datetime by month or binning a number is one expression.
  • Window columns — running totals, ranks, lags and rates.
  • Joins — pivot over a reactive join of two streaming tables.

Where the pivot runs

The same pivot API runs in the browser (WebAssembly), in Node.js, in Python and in Rust. It can also be delegated to a database: with a virtual server, a group_by/split_by configuration is translated to SQL and executed by DuckDB, ClickHouse or PostgreSQL.

Licensing

Row and column pivoting, aggregation, charting of pivots and server-side virtualization are all part of Perspective’s Apache-2.0 open source distribution. There is no commercial tier.

Examples