9.4 Aggregation and analytics

Blocking stages that summarize rows (group, bucket, pivot) or compute across rows (window, fill). All run within the query’s memory budget and spill to disk past it.

group

Definition. Aggregates rows by keys: one output row per distinct key.

Compound form

group
| by <key> [as <alias>] [, <expression> as <alias>]*
| <measure> …
| end

Every measure accepts [where <predicate>] and [as <alias>]:

| sum amount where category in ["Sales", "Services"] as revenue
| count where amount < 0 as credits

Behavior

• by is required, exactly once, and may list several keys. A key may be an expression with as (by date_format(date, "%Y-%m") as month).
• Measures appear zero or more times, in any order. With none, the group emits count.
• A measure’s value may be an expression with as (sum price * qty as revenue).
• where on a measure counts the value as null on the rows it rejects: every measure ignores them, and count where p counts the kept rows. first/last with where need by <order>.
• median, percentile and stddev keep the group’s numbers (a list) and reduce them at the end.
• A collection is read in storage order, which a replaced row may change: use first … by <order> / last … by <order> for a defined order.
• Output rows come in key order.

Compact form

group <key> [as <alias>]                          -- count per key
group <key> [as <alias>], <item> [as <alias>]…    -- numbers summed, the rest keys

• One item: count per key.
• Several items: the first is a key. Each later item is summed when every value it takes is a number, and is a key otherwise. With no summed item, the group counts.
• What an item holds is known before the scan, from the storage’s per-field kinds, through the where, limit, skip, sort and derive stages before the group. An arithmetic or numeric function expression is a number; a comparison, a text or date function, a text or boolean field is a key.
• When that cannot be known (another stage before, a nested path, at/get), every item but the last is a key and the last is summed.
• A numeric code (an integer client number) is summed too: group by it with the compound form, or to_string(code) as code.

Examples

on orders | group | by region | sum qty as units | avg price | count as orders | end

{"avg_price":39.833333333333336,"orders":3,"region":"EU","units":8}
{"avg_price":119.83333333333333,"orders":3,"region":"US","units":8}

on orders | group status                                    -- {"count":4,"status":"paid"} …
on orders | group region, qty                               -- {"qty":8,"region":"EU"} …
on orders | group region as zone, month, qty as units
on orders | group region, qty, price * qty as revenue, status
on ledger
| group
    | by entity, account
    | last balance by month as closing
    | sum amount where category in ["Sales", "Services"] as revenue
    | count where amount < 0 as credits
    | sum amount * rate as converted
| end
on orders | group | by user | list product as products | end
on orders | group | by region | median price | percentile price 0.9 as p90 | stddev price | end

Patterns

• HAVING: filter after the group: group | by user | sum qty as units | end | where units > 3.
• Grand total: a constant key, derive all = "total" | group | by all | sum amount | end.
• Top N groups: group … | sort revenue desc | limit 10 (a bounded group then Top-N).

Errors and limits

• by missing or repeated.
• min/max on values of different kinds.
• group | limit N keeps only the N smallest live keys.

See also pivot, bucket, count, aggregate functions.

bucket

Definition. Counts rows per value range.

Syntax

bucket <expression> by [<b0>, <b1>, …] [default <value>] [as <alias>]

Behavior. Each row falls in the range [b(i), b(i+1)) named by its lower bound; rows outside every range go to default (or null). Bounds must be strictly ascending. The alias defaults to bucket. It is shorthand for derive <alias> = bucket(<expression>, [...], <default>) | group <alias>.

Example

on users | bucket age by [0, 18, 40] default "40+" as band

{"band":0,"count":1}
{"band":18,"count":2}
{"band":"40+","count":1}

See also the bucket function, to bucket without grouping.

pivot

Definition. Cross-tabulates rows: one output row per row key, one field per column value.

Syntax

pivot
| rows    <field> | <expression> as <alias> [, …]
| columns <field> | <expression> as <alias> [, …]
| values  <field> [as <alias>] | <expression> as <alias> [, …]
| aggregate sum | count | avg | average | min | minimum | max | maximum | first | last
| end

Behavior

• Each directive appears exactly once.
• values takes one or more measures, written like select items. With one measure, each cell is named after its column value; with several, <column>.<name> (names must be unique).
• A row is named after its alias; a column’s alias is required but unused (its values name the cells).
• Expressions in rows, columns or values are computed by a hidden derive before the pivot.
• Cells aggregate as group does: sums stay integers when exact (a cell with no number is 0), non-numbers are ignored by sum/avg, missing values skipped by every aggregate but count.
• A cell with no row is absent: read it with ? after the pivot.
• Stages may follow end.

Examples

on orders
| pivot | rows region | columns month | values qty | aggregate sum | end

{"2026-01":7,"2026-02":1,"region":"EU"}
{"2026-01":7,"2026-02":1,"region":"US"}

on sales
| pivot | rows substr(period, 0, 4) as year | columns kind | values amount | aggregate sum | end
| derive margin = coalesce(sales?, 0) - coalesce(costs?, 0)

See also group.

window

Definition. Adds to every row values computed over its ordered partition: previous and next values, running sums, ranks.

Syntax

window
| partition by <field> [, <field>]*
| order by <field> [asc | desc] [, …]
| lag <field> [<rows>] [as <alias>]
| lead <field> [<rows>] [as <alias>]
| cumsum <field> [as <alias>]
| first <field> [as <alias>]
| last <field> [as <alias>]
| row_number [as <alias>]
| end

Behavior

• order by is required, once; partition by is optional (the whole input is one partition without it); at least one function.
• Partition keys compare as sort compares them (1 and 1.0 share a partition).
• Functions run in order: one may read a field an earlier one added.
• Rows come out sorted by partition, then order, keeping all their fields.
• It is a stable sort (it spills) then a pass holding one partition at a time.

Example

on ledger
| window
    | partition by entity, account, fiscal_year
    | order by month
    | lag balance as previous
    | cumsum movement as ytd
    | row_number as n
| end
| derive variation = balance - coalesce(previous, 0)

Errors and limits

• a window partition exceeds the governed query working set: partition more finely.
• Partition and order keys are field paths: derive computed keys first.
• Top level or a union branch only.

fill

Definition. Adds, in each partition, a row for every value of a grid that no row holds: complete missing months, missing steps.

Syntax

fill <field> from <start> to <end> step <step> [by <field> [, <field>]*] [with <field> = <value> [, …]]

Behavior

• The grid is start, start + step, … up to end, inclusive.
• step is a positive number, or a date unit for date bounds (step "month", units as in date_add). A date grid is written as start is: "2026-01" gives "2026-02", "2026-03"…; a full date gives full dates; epoch milliseconds give milliseconds.
• A new row holds the partition’s by fields, the grid value and the with values; other fields are missing.
• Rows already there pass unchanged, values off the grid included. Output is sorted by the by fields then <field>.
• An empty input stays empty: a partition exists only through its rows.
• Bounds may be parameters (from $start to $end); at most 100,000 grid values.

Example

on ledger
| fill month from $opening to $closing step "month" by entity, account with balance = 0, movement = 0
| window | partition by entity, account | order by month | lag balance 12 as last_year | end

Limits. <field> and by are field paths; top level or a union branch.