【BigQuery】集計関数でパーセンタイルを算出する

TL;DR

パーセンタイルを出すには

APPROX_QUANTILES(x, 100)[OFFSET(percentile)]

を使う

---

BigQueryに用意されている PERCENTILE_CONT, PERCENTILE_DISC 関数はウィンドウ関数のため、

SELECT
  PERCENTILE_DISC(x, 0.5) OVER() median
FROM
  UNNEST([1,2,3,4,5,6,7,8,9]) x

5
5
5
...

となり、 LIMIT 1, DISTINCT 等をつける必要があるため扱いづらい。

以下のように書くと集計関数であるため結果はスカラ値になる。

SELECT
  APPROX_QUANTILES(x, 100)[OFFSET(50)] median
FROM
  UNNEST([1,2,3,4,5,6,7,8,9]) x

5