make_reference [groupby: col storable]*, [groupOrAggregateFunction: expression]+

Builds a compact reference Table by aggregating a temporal input across the full history available in the dataset, materialized so it can be maintained efficiently as new data arrives.

The input must define a Valid From column (temporal Event, Interval, or Resource datasets) and must use insert-only acceleration; other acceleration modes are rejected with an unsupported-input error. You supply name:expression bindings where each expression uses only aggregate functions that support incremental maintenance at the top level—aggregates such as array_agg that are not incremental are rejected, and nesting one aggregate inside another (for example max of a min over the same input) is not allowed. Every non-grouping column from the input must appear only inside an aggregate (or grouping) expression. You may combine several aggregates and grouping keys in one row using ordinary arithmetic on the aggregate results (for example adding a constant to a sum), as long as the underlying aggregates stay incremental. Window functions are not accepted in these expressions. The output is a non-temporal Table whose schema is your grouping columns plus the named aggregate columns; the verb requires at least one grouping column or aggregate overall. Use this when you need a stable lookup table over all time; for aggregates scoped to a query window or timeline, statsby or other time-aware rollups are usually a better fit.

Categories

• Aggregate
• Metadata

Accelerable

make_reference is always accelerable if the input is accelerable. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.

Examples

make_reference rows: count(1), group_by(station_id)

Shows a single incremental aggregate with an explicit group_by list so each distinct grouping key becomes one row in the reference table.

make_reference sum_amt:sum(temp_c), mean_amt:sum(temp_c) / count(1), group_by(station_id)

Uses two incremental aggregates in the same binding list, then combines their results with ordinary division to form an average-style column alongside a plain sum.