Window Functions

Window functions calculate values across nearby rows — for example a rolling total, or the value from the previous row — without collapsing your data the way group by does.

When to use them

Rolling average or sum over time
Compare each row to the previous or next row (lag, lead)
Running totals within a line or product

Important: You cannot use group by and window in the same query. If you need both, put the grouping in a with helper and use from on the outer query (see Windowing over CTE results).

Syntax

AGGREGATE
    <function>(<expression>) OVER <window_name> AS <column name>
WHERE <filters>
WINDOW <window_name> AS (
    [PARTITION BY <column>, ...]
    [ORDER BY <column> [ASC|DESC]]
    [<frame_type> BETWEEN <start> AND <end>]
)

Window definition clauses

Clause	What it does
`PARTITION BY`	Split rows into separate groups (like grouping, but rows stay separate)
`ORDER BY`	Sort rows inside each group
`ROWS BETWEEN ... AND ...`	Which nearby rows to include
`RANGE BETWEEN ... AND ...`	Same idea, but based on values instead of row count

Frame bounds

Bound	Meaning
`UNBOUNDED PRECEDING`	From the first row in the group
`N PRECEDING`	N rows before the current row
`CURRENT ROW`	The current row
`N FOLLOWING`	N rows after the current row
`UNBOUNDED FOLLOWING`	Through the last row in the group

Supported functions

Add OVER window_name to any aggregate function:

sum(...), avg(...), min(...), max(...), count(...), p75(...), etc.

Window-only helpers:

Function	What it returns
`lag(<expression>)`	Value from the previous row
`lead(<expression>)`	Value from the next row

Example 1: Rolling sum

Two-row rolling sum of linespeed, per line:

aggregate
    sum(metric_group('linespeed_uuid')) over w as rolling_total
where time from now()-1D to now()
window w as (
    partition by line.id
    order by timestamp
    rows between 1 preceding and current row
)

Example 2: Cumulative and reverse cumulative sums

aggregate
    sum(metric_group('linespeed_uuid')) over w_prefix as cumulative_sum,
    sum(metric_group('linespeed_uuid')) over w_suffix as reverse_sum
where time from now()-1D to now()
window w_prefix as (
    partition by line.id
    order by timestamp
    rows between unbounded preceding and current row
),
w_suffix as (
    partition by line.id
    order by timestamp
    rows between current row and unbounded following
)

Example 3: Lag and lead

aggregate
    lag(metric_group('linespeed_uuid')) over w as prev_value,
    lead(metric_group('linespeed_uuid')) over w as next_value
where time from now()-1D to now()
window w as (
    partition by line.id
    order by timestamp
    rows between unbounded preceding and unbounded following
)

lag returns 0 when there is no previous row. lead returns 0 when there is no next row.

Example 4: Partition only

aggregate
    sum(metric_group('linespeed_uuid')) over w as line_total
where time from now()-1D to now()
window w as (partition by line.id)

Windowing over helper query results

with — compute per-line totals with group by
from — apply window functions on those totals

with grouped(linename, total) as (
    aggregate
        sum(metric_group('linespeed_uuid')) as total
    where time from now()-7D to now()
    group by line.name as linename
)
aggregate
    grouped.total as total,
    lag(grouped.total) over w as prev_total
from grouped
window w as (order by grouped.total)

Multiple partition columns

aggregate
    sum(metric_group('linespeed_uuid')) over w as rolling_total
where time from now()-1D to now()
window w as (
    partition by line.id, factory.id
    order by timestamp
    rows between 1 preceding and current row
)