問題提起
BigQuery（この記事ではstandardSQLのみ考える）でパーセンタイルを計算するにはPERCENTILE_DISC()やPERCENTILE_CONT()が使える。ただし、これは分析関数1 (analytic function) であって、SUM()やAVG()のような集計関数 (aggregate function) と同じ文脈で使えない。
解決策
集計関数のようにパーセンタイルを計算する処理は、以下のように書ける。0.5の部分を求めるパーセンタイルに置換すればよい（中央値ならこのままでOK）。SUM()やAVG()と並べて使えることに注目。
#standardSQL
SELECT
ARRAY_AGG(value ORDER BY value)[OFFSET(CAST(CEIL((COUNT(value)-1)*0.5) AS INT64))] AS percentile,
SUM(value) AS total,
AVG(value) AS average,
FROM UNNEST(GENERATE_ARRAY(0, 100)) AS value
;
/*
percentile,total,average
50,5050,50.0
*/
UDFを使って整理すると以下の通り。UDFを列に直接適用するのではなく、ARRAY_AGG()を挟む必要がある点に注意。
#standardSQL
CREATE TEMP FUNCTION PERCENTILE(arr ANY TYPE, percentile FLOAT64) AS ((
SELECT ARRAY_AGG(a ORDER BY a)[OFFSET(CAST(CEIL((COUNT(a)-1)*percentile) AS INT64))]
FROM UNNEST(arr) as a
));
SELECT
PERCENTILE(ARRAY_AGG(value), 0.5) AS percentile,
SUM(value) AS total,
AVG(value) AS average,
FROM UNNEST(GENERATE_ARRAY(0, 100)) AS value
;
もしくは、上記のUDFを公開しておいたので、以下でもOK。
最近このGCPプロジェクト消しちゃった。
SELECT
skndr666m1.fn.PERCENTILE(ARRAY_AGG(value), 0.5) AS percentile,
SUM(value) AS total,
AVG(value) AS average,
FROM UNNEST(GENERATE_ARRAY(0, 100)) AS value
;
解説
ポイントは、対象となる列に集計関数ARRAY_AGG()を適用して配列に変換すること。あとはソートして適切なインデックスを指定するだけ。線形補完などはしていないので、PERCENTILE_CONT()よりもPERCENTILE_DISC()に近い処理となる。
懸念点は、ある程度大きなテーブルでも列を配列に変換する処理が可能かどうか。またUDFの場合、ARRAY_AGG()された状態の引数を、UNNEST()後に再びARRAY_AGG()するという手順を踏むため、これもパフォーマンスに影響するかもしれない。気が向いたら検証する。
ちなみに、中央値だけでよければcommunity contributed UDFとして公開されているbqutil.fn.median()2も使える。