BigQueryでfhoffa.x.medianを使って中央値を計算する

KPIの分析をするさいに、平均値だけでなく中央値もみたいという場合がある。
実際、新しく中央値も見たいと言われてBigQueryで中央値を出す方法を調べていた。

BigQueryは集計するのが早くて楽だけど、中央値を出すmedian関数がないっぽい。

調べてみると PERCENTILE_CONT を使うことで中央値を出すことができる、という記事が多く出てきた。
例えば

PERCENTILE_CONT(hoge, 0.5) OVER(partition by fuga)

で中央値を出すことができるらしい
なので、最初に PERCENTILE_CONT を使って出そうとしたのだが、いくつか問題が出てきた。

後からの要望で、欲しい情報が1つのテーブルにまとまっている事はそんなにない。
テーブルが分かれている場合は、JOINで外部結合して欲しい情報をまとめるのだが、外部結合したテーブルで PERCENTILE_CONTをすると以下のようなエラーが起きる。

SELECT list expression references column {hoge} which is neither grouped nor aggregated

サブクエリ内で外部結合して結果から検索するとかも考えれるが手間であるし、その先でも別の問題がおきるかもしれない。
なので、別の方法を探すことにした。

社内の方に聞いているうちに fhoffa.x.median が使えるという話を聞いたので試したみたが、これはうまくいった。

fhoffa.x.median(ARRAY_AGG(hoge IGNORE NULLS))

このようにするだけで中央値を求めることができた。
また、集計対象のカラムにNULLがある場合でも IGNORE NULLS をつけることでNULLを無視して中央値を出すことができる。

fhoffa.x.median が楽でしたので、BigQueryでの集計で今後も中央値がほしいと言われたら、この方法を使おうと思います。

KYな雑記帳