Spark Scala Percentile and Approximate Aggregates

percentile_approx, median, and approx_count_distinct are aggregate functions for summarizing distributions in a Spark Scala DataFrame. percentile_approx estimates one or more percentiles, median returns the middle value, and approx_count_distinct estimates the number of unique values — trading a little accuracy for a lot of speed on large datasets.

Approximate percentiles

Computing exact percentiles over billions of rows is expensive because it requires a full sort. percentile_approx uses a bounded-memory algorithm to estimate percentiles cheaply, which is usually what you want for latency dashboards, SLA reporting, and outlier detection.

The Scala functions version takes three arguments — the column, the percentage, and an accuracy value:

def percentile_approx(e: Column, percentage: Column, accuracy: Column): Column

The percentile_approx function first appeared in version 3.1.0 and is defined in org.apache.spark.sql.functions.

The percentage is a value between 0.0 and 1.0 — pass lit(0.5) for the median, lit(0.95) for the 95th percentile, and so on. The accuracy is a positive integer (the default in SQL is 10000); higher values give more precise estimates at the cost of more memory.

Here we estimate the median response time per endpoint:

val df = Seq(
  ("/home",    120),
  ("/home",    140),
  ("/home",    95),
  ("/home",    600),
  ("/search",  210),
  ("/search",  230),
  ("/search",  205),
  ("/search",  2100),
).toDF("endpoint", "response_ms")

val df2 = df
  .groupBy("endpoint")
  .agg(
    percentile_approx(col("response_ms"), lit(0.5), lit(10000)).as("median_ms"),
  )
  .orderBy("endpoint")

df2.show(false)
// +--------+---------+
// |endpoint|median_ms|
// +--------+---------+
// |/home   |120      |
// |/search |210      |
// +--------+---------+

Note that percentile_approx returns an actual value from the data (here 120, one of the observed response times) rather than interpolating between the two middle values. The return type matches the input type — an Int column in, an Int estimate out.

Multiple percentiles at once

Pass an array of percentages to compute several percentiles in a single pass over the data. This is far more efficient than calling percentile_approx once per percentile, and it's the standard way to produce a p50/p90/p99 latency summary:

val df = Seq(
  ("/home",    120),
  ("/home",    140),
  ("/home",    95),
  ("/home",    600),
  ("/search",  210),
  ("/search",  230),
  ("/search",  205),
  ("/search",  2100),
).toDF("endpoint", "response_ms")

val df2 = df
  .groupBy("endpoint")
  .agg(
    percentile_approx(
      col("response_ms"),
      array(lit(0.5), lit(0.9), lit(0.99)),
      lit(10000),
    ).as("p50_p90_p99"),
  )
  .orderBy("endpoint")

df2.show(false)
// +--------+-----------------+
// |endpoint|p50_p90_p99      |
// +--------+-----------------+
// |/home   |[120, 600, 600]  |
// |/search |[210, 2100, 2100]|
// +--------+-----------------+

When you pass an array of percentages, the result is an array of estimates in the same order. With only four rows per group, the p90 and p99 both land on the largest observed value — the tail estimates get sharper as the number of rows grows.

Exact median

If you specifically want the median and don't need the approximation trade-off, Spark 3.4.0 added a dedicated median function:

def median(e: Column): Column

The median function first appeared in version 3.4.0 and is defined in org.apache.spark.sql.functions.

Unlike percentile_approx, median computes the exact middle value and interpolates when a group has an even number of rows, returning a Double:

val df = Seq(
  ("Engineering",  120000),
  ("Engineering",  95000),
  ("Engineering",  110000),
  ("Engineering",  105000),
  ("Sales",        72000),
  ("Sales",        88000),
  ("Sales",        65000),
).toDF("department", "salary")

val df2 = df
  .groupBy("department")
  .agg(
    median(col("salary")).as("median_salary"),
    percentile_approx(col("salary"), lit(0.5), lit(10000)).as("approx_median"),
  )
  .orderBy("department")

df2.show(false)
// +-----------+-------------+-------------+
// |department |median_salary|approx_median|
// +-----------+-------------+-------------+
// |Engineering|107500.0     |105000       |
// |Sales      |72000.0      |72000        |
// +-----------+-------------+-------------+

The Engineering group has four salaries. Sorted, the two middle values are 105000 and 110000, so median returns their average, 107500.0. percentile_approx instead returns 105000 — an actual data point, not an interpolated one. For the odd-sized Sales group both functions agree on 72000. This interpolation difference is the main reason to reach for median over percentile_approx(col, lit(0.5), ...) when exactness matters.

Approximate distinct counts

Counting distinct values exactly requires deduplicating every value across all partitions, which is slow at scale. approx_count_distinct uses the HyperLogLog algorithm to estimate the count in bounded memory. It comes in several forms:

def approx_count_distinct(e: Column): Column

def approx_count_distinct(e: Column, rsd: Double): Column

def approx_count_distinct(columnName: String): Column

def approx_count_distinct(columnName: String, rsd: Double): Column

The optional rsd parameter is the maximum relative standard deviation allowed (default 0.05, i.e. 5%). A smaller rsd gives a more accurate estimate but uses more memory.

val df = Seq(
  ("2026-08-01",  "user_a"),
  ("2026-08-01",  "user_b"),
  ("2026-08-01",  "user_a"),
  ("2026-08-01",  "user_c"),
  ("2026-08-02",  "user_a"),
  ("2026-08-02",  "user_d"),
  ("2026-08-02",  "user_d"),
).toDF("day", "visitor_id")

val df2 = df
  .groupBy("day")
  .agg(
    approx_count_distinct(col("visitor_id")).as("approx_unique_visitors"),
  )
  .orderBy("day")

df2.show(false)
// +----------+----------------------+
// |day       |approx_unique_visitors|
// +----------+----------------------+
// |2026-08-01|3                     |
// |2026-08-02|2                     |
// +----------+----------------------+

On this tiny dataset the estimate is exact — 2026-08-01 has three distinct visitors and 2026-08-02 has two. The approximation error only becomes visible on large, high-cardinality columns, where approx_count_distinct runs dramatically faster than an exact distinct count.

Related functions

For exact distinct counts, see count and countDistinct. For the most common value in a group rather than the middle one, see mode. For simple averages, see avg and mean.