An Aggregate turns a group into one row. A window keeps every row and adds a value worked out from the row's group: the group's total, the row's rank, the amount on the row before it. Pick a function, then tap a row to see exactly which rows its value came from.
Two settings shape the window:
group_by splits the rows into partitions. Each row only sees the rows in its own partition. The field is required; write group_by: [] to put all rows in one partition.sort_by puts each partition in order. Order matters for positions (row_number, lag, lead), ranks, first and last, and the running total.- type: transform
name: ranked
input: sales
config:
analytic_window:
group_by:
sort_by:
- field:
order:
cxl: |
emit rep = rep
emit amount = amount
emit result =
$window.sum(amount) gives every row in North the same North total (530.0). For a running total, use $window.cumulative_sum(amount).sum, avg, min, max, count, collect, distinct, any and every all look at the whole partition, whatever the row's position. Only cumulative_sum stops at the current row.
There is no frame option to change this.
These return a whole row, so name the field you want: $window.lag(1).amount. Written on its own, $window.lag(1) gives null on every row, with no error.
first_value(amount) and last_value(amount) take the field as an argument instead.
$window.count() is the number of rows in the partition, the same on every row. For a position use $window.row_number(); for a position where ties share a number, rank() or dense_rank().
| function | looks at | notes |
|---|---|---|
sum avg min max | whole partition | Empty values are skipped. avg returns a decimal-point number, and so does sum of whole numbers (530 comes back as 530.0). |
cumulative_sum | first row through the current row | The running total. Like sum, whole numbers come back as a decimal-point number. |
count() | whole partition | Number of rows in the partition. |
row_number() | current position | 1 for the first row in sort_by order. |
rank() dense_rank() | current position | Rows with equal sort_by values share a rank. rank then skips numbers (1, 2, 2, 4); dense_rank doesn't (1, 2, 2, 3). |
first_value(f) last_value(f) | first or last row of the partition | In sort_by order. |
first().f last().f | first or last row of the partition | Name the field after the call. |
lag(n).f lead(n).f | the row n before or after | Null when there is no such row. |
any(p) every(p) exists(p) not_exists(p) | whole partition | A condition checked on every row of the partition. |
collect(f) distinct(f) | whole partition | An array of the values, empty values left out. |
Windows also work with correlation keys: if rows are taken back out of an upstream group, the window recomputes the partitions they were in.