Shape / table · All operations

Median and percentiles per group

A variant of Summarise by group

Compute the median, a quantile or several percentiles of each group, exactly or approximately.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Median and percentiles of each group, exact and approximate

SQL
-- amount is cast to DOUBLE so a percentile between two values is not rounded to the column's two decimals
CREATE TEMP TABLE result AS
SELECT product,
       median(amount::DOUBLE) AS median,
       round(quantile_cont(amount::DOUBLE, 0.25), 4) AS q1,
       round(percentile_cont(0.75) WITHIN GROUP (ORDER BY amount::DOUBLE), 4) AS q3,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc,
       quantile_cont(amount::DOUBLE, [0.25, 0.5, 0.75]) AS quartiles,
       approx_quantile(amount::DOUBLE, 0.5) AS median_approx
FROM orders
GROUP BY product;
SELECT product, median, q1, q3, median_disc, median_approx FROM result ORDER BY product;
SELECT CASE WHEN ((SELECT list((product, median, q1, q3, median_disc) ORDER BY product) FROM result) = [('gadget', 45.25, 30.125, 62.875, 45.25), ('gizmo', 159.995, 129.9925, 189.9975, 99.99), ('widget', 405.0, 255.0, 510.0, 300.0)] AND (SELECT bool_and(len(quartiles) = 3 AND quartiles[2] = median) FROM result) AND (SELECT list((product, median_approx) ORDER BY product) FROM result) = [('gadget', 45.25), ('gizmo', 159.995), ('widget', 405.0)] AND (SELECT bool_and(median_approx BETWEEN lo AND hi) FROM result JOIN (SELECT product, min(amount) AS lo, max(amount) AS hi FROM orders GROUP BY product) USING (product))) THEN 'as expected' ELSE error('the exact median, quartiles and discrete median per product were wrong, or the approximate median was not the pinned value inside its group range') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the exact median, quartiles and discrete median per product were wrong, or the approximate median was not the pinned value inside its group range”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (6)
Duckle (1)
  • Approx Quantileread 2026-09-25

    approximate quantile per group with approx_quantile

EasyMorph (1)
Ibis (6)
  • ApproxMedianread 2026-09-27

    Approximate median of a column

  • ApproxMultiQuantileread 2026-09-27

    Approximations of several quantiles of a column, as an array

  • ApproxQuantileread 2026-09-27

    Approximation of a chosen quantile of a column

  • Medianread 2026-09-27

    Median of a column

  • MultiQuantileread 2026-09-27

    Several quantiles of a column, returned as an array

  • Quantileread 2026-09-27

    A chosen quantile of a column

Polars (2)
Trifacta (6)

Background

A percentile is a score greater than a given percentage of the scores in a sample: 97% of the data points are less than the 97th percentile.[1] The 25th percentile is also known as the first quartile, the 50th as the median and the 75th as the third quartile.[1] In PostgreSQL, percentile_cont gives the value at a fraction within the ordered set of aggregated values, interpolating between adjacent values if needed.[2]

When to use it

The median is not skewed by a small proportion of extreme values, unlike the mean, and so it can give a better picture of the centre of the data.[3] Report a group's median beside its mean when a few very large values could drag the mean away from a typical row. (Meridian's view)

There is no standard definition of percentile, though all definitions give similar results when the number of observations is very large and the distribution is continuous.[1] Hyndman and Fan identified nine methods of calculating a percentile, and most statistical and spreadsheet software uses one of the methods they describe.[1] In DuckDB, median averages the two middle values for an even count of quantitative values, and returns the lower value for ordinal values.[4] When two tools disagree on a group's percentile, check which method each uses before suspecting the data. (Meridian's view)

In DuckDB, approx_quantile calculates an approximate quantile using T-Digest, and reservoir_quantile uses reservoir sampling with a default sample size of 8192.[4] Prefer the exact functions until the groups are too large to sort, and label an approximate median as an estimate wherever it is reported. (Meridian's view)

The method

In PostgreSQL, ordered-set aggregates are a subclass of aggregate functions that require an ORDER BY, usually because their computation only makes sense for an ordering of the input rows.[5] In PostgreSQL, an ordered-set aggregate's ORDER BY is written inside WITHIN GROUP (...), and percentile calculations are typical examples.[5] In PostgreSQL, the percentile fraction is a direct argument, evaluated once per aggregate call rather than once per input row.[5] In PostgreSQL, percentile_disc returns the first value in the ordered set whose position in the ordering equals or exceeds the specified fraction.[2] In PostgreSQL, giving percentile_cont an array of fractions computes several percentiles at once, returning an array of the same dimensions.[2] In DuckDB, an ordered-set function written with WITHIN GROUP (ORDER BY ...) is converted to an equivalent aggregate that takes the ordering expression as its first argument.[4]

Hyndman and Fan compiled a taxonomy of nine algorithms for estimating a quantile from a sample, used by various software packages.[6] The t-digest maintains a data structure of bounded size, using an approach motivated by k-means clustering to group similar values.[6]

Compared with the alternatives

A percentile rank works the other way round from a percentile: a score is given and a percentage is computed, rather than a percentage given and a score determined.[1] In PostgreSQL, the hypothetical-set aggregate percent_rank computes the relative rank a hypothetical row would have if it were added to the sorted group.[2]

Sources

  1. [1]Percentile - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 9.21. Aggregate Functionsdocumentation
  3. [3]Median - Wikipediasecondary source
  4. [4]Aggregate Functions – DuckDBdocumentation
  5. [5]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation
  6. [6]Quantile - Wikipediasecondary source