MEDIAN

​

Syntax

​

Aggregate

median([DISTINCT] value)

​

Window

median(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

​

Arguments

​

Aggregate and window

​

Returns

• Type: DOUBLE for integer inputs; the input’s effective FLOAT, DOUBLE, or DECIMAL type for floating-point and decimal inputs
• NULL behavior: NULL values are ignored; all-NULL input yields NULL.
• Empty input: Returns NULL on empty input.

​

Properties

​

Usage notes

• MEDIAN calculates the continuous percentile at 0.5.
• MEDIAN calculates percentile 0.5 and returns one value. It does not accept an array of percentile fractions.
• 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.
• In grouped aggregation, DISTINCT removes duplicate input values before calculating the median.

​

Examples

​

Basic example

WITH t("value") AS (VALUES (1),(2),(3),(4)) SELECT median("value") AS result FROM t;

​

Groups with different numbers of values

WITH sample(grp, v) AS (VALUES (1, 1), (1, 5), (2, 8), (2, CAST(NULL AS INT)))
SELECT grp, round(median(CAST(v AS DOUBLE)), 6) AS result
FROM sample GROUP BY grp ORDER BY grp;

​

Limitations

• Sliding ROWS frames are supported for the window syntax shown above. RANGE frames with numeric or time offsets are not supported.
• Floating-point and decimal results can keep their effective input type, even when query tools report DOUBLE.
• 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.