percentile_approx
Medians and p90s that scale to billions of rows, and how the accuracy setting trades memory for error.
On this page
Show code in
Every code block on the page follows this.
You will learn
- Why exact percentiles are expensive on big data
- How percentile_approx works and what the accuracy argument does
- How to compute several percentiles at once
- When to use median and percentile instead
F.percentile_approx(col, 0.9, accuracy) returns a value from the data near the 90th percentile, using a compact sketch that merges across partitions. The relative error is about 1 / accuracy; the default accuracy is 10,000.Why not exact?
An exact percentile needs every value of a group sorted in one place. Unlike sum or count, it cannot be pre-aggregated per partition. On a group with a billion rows that is a lot of memory and a slow sort. An approximate sketch stores a small summary of the distribution instead, and two sketches can be merged, so it works like any other partial aggregation.
Step by step
For /api, the 10 latencies sorted are 12, 15, 18, 22, 25, 31, 40, 55, 90, 400.
| Statistic | Value | Comment |
|---|---|---|
| Average | 70.8 ms | Pulled up by the 400 ms outlier |
| Median (p50) | 25 ms | Half the requests are faster than this |
| p90 | 90 ms | 9 in 10 requests are faster than this |
Latency is the classic case for percentiles: the average describes no real request, while p50 and p90 describe what users experience.
Run the example
from pyspark.sql import functions as F result = latency.groupBy("endpoint").agg( F.round(F.avg("ms"), 1).alias("avg_ms"), F.percentile_approx("ms", 0.5).alias("p50"), F.percentile_approx("ms", 0.9).alias("p90"), )
SELECT endpoint, ROUND(AVG(ms), 1) AS avg_ms, percentile_approx(ms, 0.5) AS p50, percentile_approx(ms, 0.9) AS p90 FROM latency GROUP BY endpoint
Switch to PySpark to edit and run this example in your browser.
The playground computes exact nearest-rank values on small data. Real Spark returns an actual value from the data within the error bound, which on small groups is usually exact too.
The accuracy argument
- The third argument, default 10,000, controls the sketch size. The relative error of the rank is roughly
1 / accuracy: with 10,000, the returned value's rank is within about 0.01% of the requested one. - Higher accuracy means more memory per group and a slower merge. With millions of groups, lowering it (for example to 1,000) can matter.
- Pass a list of percentages to get several in one pass:
percentile_approx("ms", [0.5, 0.9, 0.99])returns an array.
Related functions
| Function | Exact? | Notes |
|---|---|---|
percentile_approx / approx_percentile | No | Scales; returns a value from the data |
median | Exact (3.4+) | Convenient; needs all values of a group |
percentile(col, p) | Exact | Interpolates between values, so may return a value not in the data |
DataFrame.approxQuantile | No | Whole-DataFrame, not per group; returns Python values to the driver |
Common mistakes
Reporting average latency
Using exact percentile on huge groups
Expecting interpolation
Key takeaways
- Exact percentiles need all of a group's values together; approximate ones merge sketches.
- Relative rank error is about 1 / accuracy (default 10,000).
- Pass a list to compute several percentiles in one pass.
- percentile_approx returns real values; percentile interpolates.
Check yourself
3 questions1. Why can percentile_approx be pre-aggregated per partition while an exact percentile cannot?
Show the answer
Sketches summarise the distribution and can be merged. Each partition builds a sketch, and sketches merge, so only small summaries are shuffled.
2. What does raising the accuracy argument do?
Show the answer
Reduces error but uses more memory. A bigger sketch gives a smaller rank error at the cost of memory and merge time.
3. Which may return a value that does not appear in the data?
Show the answer
percentile. percentile interpolates between neighbouring values.
Practice it
Interview problems that use percentile_approx: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: functions.percentile_approx · functions.median