Median and percentiles
The middle value of a column, or any percentile, via PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) — no manual row-counting needed.
Last updated
SELECT sensor_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value) AS p90_value FROM measurements GROUP BY sensor_id;
Why it’s written this way
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) sorts the values in each group and interpolates the point exactly halfway through them — the definition of a median — and 0.9 in place of 0.5 gives the 90th percentile with the identical syntax. WITHIN GROUP is what tells the engine which column to sort and measure, since PERCENTILE_CONT itself takes no column argument.
It behaves as an ordered-set aggregate, so it follows the same GROUP BY rules as any other aggregate: list sensor_id alongside it and group by that column, and it computes one median per sensor instead of a single value for the whole table.
Edge cases to know
- →MySQL has no PERCENTILE_CONT; the common workaround there is a self-join with manual row-counting, or a window-function trick, since the built-in ordered-set aggregate simply isn't available.
- →PERCENTILE_CONT interpolates between the two middle values on an even-sized group, which can produce a number that never actually appears in the data — PERCENTILE_DISC picks an actual row's value instead when that distinction matters.
- →NULL values in value are dropped before the percentile is computed, the same way AVG or SUM ignore them — they don't count toward the size of the group.