6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

【BigQuery】standardSQLでパーセンタイル(や中央値)を集計関数のように計算する

Last updated at Posted at 2021-03-05

問題提起

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も使える。

  1. この記事ではBigQueryのドキュメントの用語をそのまま使う。分析関数よりもウィンドウ関数 (window function)という呼び方に馴染みがある人も多いと思う。

  2. コードを確認したところ、こちらは対象が偶数の場合に線形補完する処理が記載されていた。そのためPERCENTILE_CONT(foo, 0.5)のような処理になる雰囲気。

6
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?