BigQuery の JavaScript ユーザー定義集計関数（UDAF）を使って中央値を求めてみた
2025.04.04こんにちは！エノカワです。
BigQuery の JavaScript ユーザー定義集計関数（User-Defined Aggregate Function、UDAF） が 一般提供（GA） になりました。
You can create a JavaScript user-defined aggregate function by using the CREATE AGGREGATE FUNCTION statement. This feature is generally available (GA).
前回の記事では、BigQuery の SQL ユーザー定義集計関数（UDAF）を使って、カスタムな集計処理を定義する方法をご紹介しました。
今回はその続編として、JavaScript UDAF を使って中央値（Median）を求める方法をご紹介します！
JavaScript ユーザー定義集計関数（UDAF）とは？
BigQuery では、SQL だけでなく JavaScript を使って ユーザー定義集計関数（UDAF）を定義することができます。
JavaScript UDAF では、次の 4 つの関数を定義する必要があります。
| 関数名 | 役割 |
|---|---|
initialState() |
集計の初期状態を定義する |
aggregate(state, value) |
各行の値を受け取り、状態を更新する |
merge(state, partialState) |
並列処理された状態を統合する |
finalize(state) |
最終的な集計結果を返す |
これらを組み合わせることで、SQL では書きにくいロジックも柔軟に実装できます。
やりたいこと：中央値を求める
中央値（Median）は、値を昇順に並べたときの中央の値です。
- 要素数が奇数の場合 → 中央の値
- 要素数が偶数の場合 → 中央 2 つの平均
BigQuery では、標準関数として中央値を求める関数は提供されていません。
そのため、中央値を求めたい場合は ARRAY_AGG で配列を作成し、OFFSET などを使って中央の値を取り出す必要がありますが、これを毎回クエリに書くのは面倒です。
そこで、ユーザー定義集計関数（UDAF）として「中央値」を関数化しておくことで、再利用性が高まり、クエリもすっきりします。
ただし、SQL UDAF では ARRAY_AGG や COUNT を使う必要があり、関数本体が単一の SELECT 式で完結する必要があるため、中央値のように配列を保持してソートする処理は実装が困難です。
一方、JavaScript UDAF では状態として配列を保持し、finalize 関数内でソートや中央値の計算を行うことができるため、こうした順序依存の集計処理に非常に適しています。
実装例
JsMedian：中央値を求める
ここでは、JavaScript UDAF を使って中央値を求める関数を定義します。
全体の流れとしては、各行の値を配列に追加し、最後にその配列をソートして中央の値を返す、というシンプルな構成です。
関数定義の構文
CREATE AGGREGATE FUNCTION mydataset.JsMedian(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
export function initialState() {
return [];
}
export function aggregate(state, x) {
if (x !== null && x !== undefined) {
state.push(x);
}
}
export function merge(state, partialState) {
return state.concat(partialState);
}
export function finalize(state) {
if (state.length === 0) return null;
state.sort((a, b) => a - b);
const mid = Math.floor(state.length / 2);
if (state.length % 2 === 1) {
return state[mid];
} else {
return (state[mid - 1] + state[mid]) / 2;
}
}
''';
ロジック解説
この関数は以下のように動作します。
initialStateで空の配列を初期化します。aggregateで各行の値を配列に追加します（nullは除外）。mergeで並列処理された配列を結合します。finalizeで配列をソートし、中央値を計算して返します。
BigQuery の JavaScript UDAF では、配列のような複雑な状態を保持できるため、中央値のような順序依存の集計処理に非常に向いています。
使用例
JavaScript UDAF の JsMedian を使って、いくつかのパターンで中央値を求めてみます。
ケース1：奇数個のデータ（昇順）
WITH sample AS (
SELECT x FROM UNNEST([10, 20, 30, 40, 50]) AS x
)
SELECT mydataset.JsMedian(x) AS median FROM sample;
結果
| median |
|--------|
| 30.0 |
- 入力データは
[10, 20, 30, 40, 50]（すでに昇順） - 要素数は 5（奇数）
- 中央の値は 3 番目の
30
ケース2：偶数個のデータ（ソートされていない）
WITH sample AS (
SELECT x FROM UNNEST([50, 10, 40, 20]) AS x
)
SELECT mydataset.JsMedian(x) AS median FROM sample;
結果
| median |
|--------|
| 30.0 |
- 入力データは
[50, 10, 40, 20]（ソートされていない） - ソート後の配列は
[10, 20, 40, 50] - 要素数は 4（偶数）
- 中央 2 つの値は
20と40→ 平均は(20 + 40) / 2 = 30
ケース3：null を含むデータ
WITH sample AS (
SELECT x FROM UNNEST([10, NULL, 30, NULL, 50]) AS x
)
SELECT mydataset.JsMedian(x) AS median FROM sample;
結果
| median |
|--------|
| 30.0 |
- 入力データは
[10, NULL, 30, NULL, 50] aggregate関数内でnullは除外されるため、実際の対象は[10, 30, 50]- 要素数は 3（奇数）
- 中央の値は
30
ケース4：空のデータ
WITH sample AS (
SELECT x FROM UNNEST([]) AS x
)
SELECT mydataset.JsMedian(x) AS median FROM sample;
結果
| median |
|--------|
| null |
- 入力データが空であるため、
aggregateもfinalizeも何も処理されません finalize関数内でstate.length === 0によりnullを返す- データが存在しない場合の動作確認として有効
注意点
JavaScript UDAF を使う際には、いくつかの注意点があります。
ORDER BYは JavaScript UDAF の関数呼び出しでは使用できません。そのため、ソート処理はfinalize関数内で行う必要があります。- メモリ使用量に注意：
aggregate関数で配列にすべての値を保持するため、データ量が多いとメモリ不足でクエリが失敗する可能性があります。
まとめ
今回は、BigQuery の JavaScript UDAF を使って中央値を求める関数を定義してみました。
SQL UDAF では構文上の制約により実装が難しかった処理も、JavaScript UDAF を使うことで柔軟に対応することができます。
以下に、SQL UDAF と JavaScript UDAF の違いを簡単にまとめます。
| 特徴 | SQL UDAF | JavaScript UDAF |
|---|---|---|
| 柔軟な状態保持 | × | ○ |
| ソート処理 | 制限あり | 自由に可能 |
| 配列操作 | 制限あり | 自由に可能 |
| 学習コスト | 低い | やや高い |
特に、順序に依存する集計処理や、複雑な状態管理が必要な集計では、JavaScript UDAF が非常に強力です。
使用例で紹介したように、null の除外やソート処理を関数内で自由に制御できる点は、SQL UDAF との大きな違いです。
最頻値（mode）や分位点（percentile）など、より高度な統計処理にも応用していけそうですね！
