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.
Part by part
- PERCENTILE_CONT(0.5)
- The continuous percentile: it interpolates between the two middle values when the count is even. 0.5 is the median by definition.
- WITHIN GROUP (ORDER BY value)
- Ordered-set aggregate syntax. The ordering is part of the function rather than the query, because a percentile is meaningless without knowing what it is ordered by.
- PERCENTILE_CONT(0.9)
- The 90th percentile — nine tenths of values fall below it. For latency work this is far more informative than an average.
Common mistakes
AVG(value) AS median_valueThe mean is not the median and diverges sharply on skewed data. One enormous outlier moves the average and barely touches the median, which is exactly why the median is worth computing.
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY value)Not wrong, and different: the discrete form returns an actual value from the data rather than interpolating. Use it when the result must be a real observation, such as a category or an identifier.
Averaging percentiles across groupsThe mean of several p90s is not the p90 of the combined data, and can be far from it. Percentiles do not compose, so recompute from the raw values rather than aggregating the aggregates.
Questions
Why is the median better than the average here?
Because it is resistant to outliers. A single faulty sensor reading of a million moves the mean and leaves the median almost unchanged, which is the property you want when summarising messy measurements.
What is the difference between CONT and DISC?
Continuous interpolates between neighbouring values and can return a number that is not in the data; discrete returns the nearest actual value. For latency either is fine; for identifiers only DISC makes sense.
Does every database support this?
Postgres, Oracle, SQL Server and DuckDB do. MySQL has no percentile function before 8.0 and no ordered-set aggregates even now, so it needs a window-function workaround with ROW_NUMBER.
Why do people quote p95 and p99 for latency?
Because the tail is where users notice. An average hides the slow requests entirely, whereas p99 describes what the unluckiest one in a hundred experiences — which is a real person on every busy page.