Derived `Table`s

A Table can be constructed from a View, producing a read-only Table whose rows are that View’s output. The engine maintains it in place: an update to the source Table is reflected in the derived Table, and in any View of it, within the same update cycle.

This is how you query the result of a query — filter on an aggregate, group an aggregate by a coarser key, or feed a pivot into a window function. It is also the mechanism behind a Client/Server Replicated design, where it handles View serialization and on_update forwarding for you.

const view = await table.view({
    group_by: ["Region"],
    columns: ["Sales"],
    aggregates: { Sales: "sum" },
    group_rollup_mode: "flat",
});

const totals = await client.table(view);

view = table.view(
    group_by=["Region"],
    columns=["Sales"],
    aggregates={"Sales": "sum"},
    group_rollup_mode="flat",
)

totals = client.table(view)

#![allow(unused)]
fn main() {
let view = table.view(Some(ViewConfigUpdate {
    group_by: Some(vec!["Region".into()]),
    columns: Some(vec![Some("Sales".into())]),
    group_rollup_mode: Some(GroupRollupMode::Flat),
    ..ViewConfigUpdate::default()
})).await?;

let totals = client.table(TableData::View(view), TableInitOptions::default()).await?;
}

Columns

An unpivoted View yields a Table with the View’s own columns, including expressions and window columns, and one row for every row passing its filter. The source’s index is inherited when the index column is among the View’s columns, and its limit is inherited as well.

A pivoted View yields the table you see on screen. Each group_by column becomes a typed key column of the same name, and each data column takes the name View::column_paths gives it. A column that is both a group_by key and one of the View’s columns would collide with itself, so that aggregate is qualified by its aggregate, as in "Region (count)" or "Sales (weighted mean by Weight)"; the key is always the bare column name:

Region,Central|Sales,East|Sales,South|Sales,West|Sales
Furniture,163797.16,208291.20,117298.68,252612.74
Office Supplies,167026.41,205516.05,125651.31,220853.25

Key columns are null below the depth of their row, which is how subtotal and grand-total rows are represented.

Rollup modes are mirrored

A derived Table has exactly the rows its source View displays. Under the default group_rollup_mode of "rollup" that includes every subtotal row and the grand total; under "flat" it is the leaves only; under "total" it is the single total row. split_rollup_mode selects the data columns the same way.

Set group_rollup_mode: "flat" on the source View before grouping or filtering a derived Table, or its subtotal rows will be counted alongside the rows they already summarize.

sort, expand/collapse and group_by_depth describe how a View is presented rather than what it contains, and do not affect the derived Table.

Read-only

The engine owns a derived Table’s rows, so update(), remove(), clear() and replace() all raise. Change the source Table instead. The index and limit options are rejected for the same reason — identity comes from the source.

Deleting a View that a derived Table still reads raises as well; delete the derived Table first.

The schema is fixed

A derived Table’s columns are decided when it is created, which matters for split_by, whose columns depend on the data. A split value that first appears later has no column to land in and its cells are dropped; one that disappears leaves its column in place, filled with null.

Declare the columns up front to accept values that have not arrived yet:

const derived = await client.table(view, {
    schema: {
        Region: "string",
        "Mon|Sales": "float",
        "Tue|Sales": "float",
        "Wed|Sales": "float",
    },
});

derived = client.table(
    view,
    schema={
        "Region": "string",
        "Mon|Sales": float,
        "Tue|Sales": float,
        "Wed|Sales": float,
    },
)

#![allow(unused)]
fn main() {
let derived = client.table(TableData::View(view), TableInitOptions {
    schema: Some(IndexMap::from([
        ("Region".into(), ColumnType::String),
        ("Mon|Sales".into(), ColumnType::Float),
    ])),
    ..TableInitOptions::default()
}).await?;
}

A declared column that the View does not have is null until its values arrive; a column the View has but the schema omits is dropped. A declared type which disagrees with the View’s is an error.

Filtering on an aggregate

SQL’s HAVING — show only the groups whose total passes a predicate — is a filter on a derived Table:

const totals = await table.view({
    group_by: ["Category", "Sub-Category"],
    columns: ["Sales"],
    aggregates: { Sales: "sum" },
    group_rollup_mode: "flat",
});

const derived = await client.table(totals);
const large = await derived.view({ filter: [["Sales", ">", 100000]] });

totals = table.view(
    group_by=["Category", "Sub-Category"],
    columns=["Sales"],
    aggregates={"Sales": "sum"},
    group_rollup_mode="flat",
)

derived = client.table(totals)
large = derived.view(filter=[["Sales", ">", 100000]])

#![allow(unused)]
fn main() {
let derived = client.table(TableData::View(totals), TableInitOptions::default()).await?;
let large = derived.view(Some(ViewConfigUpdate {
    filter: Some(vec![Filter::new("Sales", ">", 100000)]),
    ..ViewConfigUpdate::default()
})).await?;
}

Change between pivot columns

To compare an aggregate against the previous column of a pivot — day-over-day change of an accruing balance, for example — group by the pivot key rather than splitting on it, then apply a diff window to the derived Table and split there:

const daily = await table.view({
    group_by: ["Portfolio", "Date"],
    columns: ["PnL"],
    aggregates: { PnL: "last" },
    group_rollup_mode: "flat",
});

const derived = await client.table(daily);
const change = await derived.view({
    group_by: ["Portfolio"],
    split_by: ["Date"],
    columns: ["change"],
    aggregates: { change: "sum" },
    group_rollup_mode: "flat",
    windows: {
        change: {
            column: "PnL",
            aggregate: "diff",
            partition_by: ["Portfolio"],
            order_by: ["Date", "asc"],
        },
    },
});

daily = table.view(
    group_by=["Portfolio", "Date"],
    columns=["PnL"],
    aggregates={"PnL": "last"},
    group_rollup_mode="flat",
)

derived = client.table(daily)
change = derived.view(
    group_by=["Portfolio"],
    split_by=["Date"],
    columns=["change"],
    aggregates={"change": "sum"},
    group_rollup_mode="flat",
    windows={
        "change": {
            "column": "PnL",
            "aggregate": "diff",
            "partition_by": ["Portfolio"],
            "order_by": ["Date", "asc"],
        }
    },
)

A window runs down rows, so the axis it walks has to be a group_by of the source. When each row of the source Table is already one observation per key — one row per portfolio per day — no derived Table is needed, and the same window applied directly to that Table gives the same result.

Limitations

A derived Table re-aggregates its own rows, so an expression over aggregate columns is only correct at the granularity those rows already have. A ratio of sums, for instance, cannot be re-derived correctly for a coarser group_by of the derived Table, because the ratios are averaged rather than recomputed.

Constructing a Table from a View hosted by a different Client copies the View’s data across that boundary instead, which appends rather than updates in place for a pivoted View.

On a Virtual Server a derived Table is a snapshot of the View taken when it is created, since a virtual View is itself a snapshot.

In the viewer

<perspective-viewer> exposes derivation from the settings sidebar’s query tab and from the panel’s right-click context menu. Derive table (at the bottom of the settings panel) and the context menu’s Derive Table each create a derived Table under a generated name — "superstore GROUP BY Region"-style — from whatever the panel currently displays, then rebind the panel to it. Both require a pivoted view.

A derived Table created this way is an ordinary named table in the workspace: it appears in the table pickers and the “New” menu, other panels may bind to it, and it outlives the panel that created it. The “New” menu marks derived rows, shows how many panels and tables read each one, and offers a delete button once nothing does.

The panel’s derivation chain appears above the control, one row per table from the base up. The row for the panel’s own table carries a close button:

Click rebinds the panel to the parent table, restoring the config the derived table was created with. The derived table itself is untouched.
Shift+click rebinds and deletes the derived table, which is offered only when that panel is the only thing reading it.

A “full” reset — shift-clicking the status bar’s Reset button, or reset(true) / resetPanel with reset_all from the API — tears the whole derived state down: every panel bound to a derived Table is released to its root table, and every derivation (pending ones included) is deleted.

A backend which cannot promote a View to a Table reports so through the view_derivations capability, and the control is hidden for panels on that client. Where a backend can derive but does not stream — DuckDB and PostgreSQL — each level is badged as a snapshot, because its data is fixed at the moment it was derived.

Derived tables persist in saveWorkspace() output under the top-level derived_tables key, each entry naming the table its source View reads and that view’s config. Workspace panels refer to tables by name; the single-panel save() format instead inlines the definition recursively in its table field, so the token is self-contained, and restore() rebinds to a structurally identical derived table or creates one under a generated name. See Save & restore.

Changes to `client.table(view)`

Previous releases copied the View’s data into an ordinary Table and forwarded updates to it. Derived Tables are maintained by the engine, which changes several behaviors for code written against the old implementation:

A derived Table is read-only, where the copy accepted writes that the next update would overwrite.
Deleting the source View raises, where the copy became a frozen snapshot.
Rows follow the source’s insertion order; the source View’s sort no longer orders the copy.
A row updated out of the source View’s filter is removed, where the copy retained it with stale values.
Updates arrive in the same cycle as the source rather than eventually.
A View which omits its source’s index column is keyed by row identity, so updates apply in place rather than appending.
A pivoted View yields named key columns and only the rows the View displays, where the copy appended its changed rows on every update.

Keyboard shortcuts

Perspective