Calculating the Median in BigQuery

One of these days, I had to handle missing value imputation and stumbled upon the need to calculate a median in BigQuery SQL. Since there is no built-in function for this, I looked for what other people used as workarounds—see sources in comments.

First, we have PERCENTILE_CONT and PERCENTILE_DISC (from continuous and discrete, respectively). The difference between them lies in whether interpolation is used:
- PERCENTILE_CONT uses linear interpolation. In the case of an even number of values, it returns their average.
- PERCENTILE_DISC selects the closest value without any interpolation.
Both of these are window functions, so if you want to simulate grouping, you need to ensure that a single value is kept per group. Also, note the option of IGNORE | RESPECT NULLS (ignore is the default).

Additionally, we can use the approximate aggregation function APPROX_QUANTILES, which allows grouping. This function splits the values into quantiles, from which we can select the 50th percentile to retrieve the median. Check out the comments for a quick into intro approximate aggregate functions.

SELECT
  1 AS id,
  PERCENTILE_CONT(x, 0.5 IGNORE NULLS) OVER() AS median_cont,
  PERCENTILE_DISC(x, 0.5 IGNORE NULLS) OVER() AS median_disc
FROM
  UNNEST([1,2,2,3,4,NULL]) as x
LIMIT 1;

SELECT
  1 AS id,
  APPROX_QUANTILES(value, 100)[OFFSET(50)] AS median
FROM UNNEST([1,2,2,3,4,NULL]) AS value
GROUP BY id