PERCENTILE_CONT

​

Syntax

​

Aggregate

percentile_cont(percentile) WITHIN GROUP (ORDER BY value [ASC | DESC])

​

Window

percentile_cont(percentile) WITHIN GROUP (ORDER BY value [ASC | DESC]) OVER ([PARTITION BY key] [ROWS frame])

• WITHIN GROUP specifies the value order. Do not add a conflicting ORDER BY inside OVER.
• Sliding ROWS frames are supported. This function does not accept a frequency argument.
• 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.
• Use PERCENTILE when each value has a frequency or weight.
• 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 is not accepted in this function’s syntax. To calculate percentiles over unique values, remove duplicates in a subquery.
• In a window call, the WITHIN GROUP ordering determines which values are used for the percentile and supplies the window ordering.

​

Examples

​

Basic example

WITH t("value") AS (VALUES (0),(1),(2),(10)) SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY "value") AS result FROM t;

​

Request multiple percentiles as an array

WITH t(v) AS (VALUES (0), (1), (2), (10)) SELECT percentile_cont(array(0.5, 0.4, 0.1)) WITHIN GROUP (ORDER BY v) AS result FROM t;

​

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.