Aggregate · Window
NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.
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
- Sliding ROWS frames work for unweighted calls without DISTINCT. Weighted windows must start at the first row in the group.
- RANGE frames with a numeric or time offset are not supported.
Arguments
Aggregate and window
Returns
- Type: A scalar fraction returns DOUBLE for integer inputs, or the input’s effective FLOAT, DOUBLE, or DECIMAL type for floating-point and decimal inputs. An array of fractions returns an ARRAY of the corresponding result type.
- NULL behavior: NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.
- Empty input: Returns NULL on empty input.
- Shape: Scalar fraction gives scalar; array fractions give matching-length array.
- Ordering: Array results follow requested fraction order. DESC reverses percentile orientation.
Properties
Usage notes
- Exact computation retains values and memory grows with input size.
- Integer values are converted to DOUBLE. Large integers can lose precision.
- Only PERCENTILE has frequency weighting.
- NULL value/frequency pairs are skipped. Frequency zero contributes no rows; negative frequencies raise an error.
- 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.
- DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.
- Window calls accept the value, fraction, and optional frequency arguments shown in Syntax.
Examples
For the values 0, 1, 2 and 10, percentile 0.5 lies halfway between 1 and 2, giving 1.5.
Calculate several percentiles with separate calls
To calculate several percentiles, use a separate call for each fraction:
Each fraction selects a position in the sorted values. The function interpolates between neighboring values when needed.
Request multiple percentiles as an array
The result follows the order of the requested fractions. For a single percentile, pass a scalar fraction instead of a one-element array.
Limitations
- Floating-point and decimal results keep their effective input type, even when query tools report DOUBLE or ARRAY<DOUBLE>. The reported type does not convert the result.
- Fractions must be constant expressions; they cannot vary by row.
- An array containing exactly one fraction currently raises a result-type error. Pass a scalar fraction for one percentile, or use separate scalar calls when selecting several percentiles.
- DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required.
Last modified on October 15, 2026
