BigQueryでの中央値の算出

はじめに

中央値を使って、データをクリーニングすることがたまにある。例えば、複数人がそれぞれで同じデータを手入力した場合などである。
しかもBigueryでは、他のSQLにあるMEDIANが用意されていない。

データ概要

複数人がそれぞれで同じデータを手入力したサンプルをtable1とする。
同じ事象に対して人がそれぞれ入力したとしても、本来はみな同じデータになるはずだが、手入力していると、どうしても間違いが起こる。この例では、2019-12-2のCの入力が、165,000-と入力すべきところを間違って1,650,000-と入力したように思える。

このデータをどうにかして、キレイにしたい。

table1

date	person	sales
2019-12-1	A	150,000
2019-12-1	B	150,000
2019-12-1	C	150,000
2019-12-2	A	165,000
2019-12-2	B	165,000
2019-12-2	C	1,650,000
2019-12-3	A	140,000
2019-12-3	B	140,000
2019-12-3	C	140,000

ここでは、誰が入力したかは重要ではなく、dateごとにsalesのデータをまとめたい。つまり、table1をtable2のように集約したい。

table2

date	sales
2019-12-1	150,000
2019-12-2	165,000
2019-12-3	140,000

単に平均値で集約すると、table2のようにはならないので、中央値を使って、クリンジングするのである。
中央値を使うとtable2のようになり、間違って入力された1,650,000-は無視される。
もちろん、中央値を使えば完璧だというわけではなく、平均値よりはマシという感じではあるが、使用する場面は意外に多いように思う。

SQL作成（Persistent UDF）

大体のSQLでは、MEDIANが使えるので、以下のように書けば、table2が作成できる。

SELECT
  date,
  MEDIAN(sales) AS sales
FROM
  table1
GROUP BY
  date

しかしながら、BigQueryではMEDIANが用意されていない。
BigQueryにも、PERCENTILE_CONT()という関数が用意されており、第2引数に0.5を入れると中央値が算出できるものの、window関数なので、table2を作成しようとすると一手間増える。

SELECT
  date,
  MAX(median) AS sales
FROM
  (SELECT
    date,
    PERCENTILE_CONT(sales, 0.5) OVER(partition by date) as sales
  FROM
    table1)
GROUP BY
  date

上記のSQLでもtable2は作成できるのだが、ちょっとダサい。

色々調べていると、Felipe Hoffa氏がPersistent UDFを作成していて、特に宣言しなくて誰でもMEDIANの代わりになる関数を使うことが出来る。
Felipe Hoffa氏が作成したPersistent UDFを使うと、table2が作成できる。

SELECT
  date,
  fhoffa.x.median(ARRAY_AGG(sales)) as sales
FROM
  table1
GROUP BY
  date

これでだいぶスッキリtable2が作成できた。

最後に

データクレンジグだけでなく、基本統計量としても中央値は使うので、使用場面は意外にありそうである。
また、UDFはよく使う方も多いと思うが、Persistent UDFも意外に使えそうである。

参考