Aggregate · Window
NULL is counted as an item, not skipped. Output array is non-NULL.
Syntax
A window function keeps each input row and adds a result for that row. PARTITION BY groups rows with matching values; without it, all input rows form one group.
Aggregate
Window
- Use a frame that includes all rows in the group or starts at the first row (
UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.
Arguments
Aggregate and window
Returns
- Type: ARRAY of STRUCT(item, count), where item has the SQL type of value and count is BIGINT
- NULL behavior: NULL is counted as an item, not skipped. Output array is non-NULL.
- Empty input: Returns an empty array on empty input.
- Shape: At most k item/count structs.
- Ordering: Descending estimated count; tied item order is unspecified.
Properties
- Approximation bound: Values occurring more than 2 × (number of input rows) / max_items_tracked times are retained among the tracked candidates. Only the top k candidates are returned.
Usage notes
- Counts are exact while all distinct values fit in the available capacity. When values are discarded to stay within that capacity, counts can be lower than the true counts. The result does not include an error bound.
- INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
- Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.
Examples
Basic example
Use the default number of results
Limitations
- Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
- DISTINCT does not remove duplicate input values in grouped calls. Removing duplicates can change the result, so remove duplicates from the input separately when needed.
Last modified on October 15, 2026
