BigQuery のユーザー定義集計関数（UDAF）を使ってみた

こんにちは！エノカワです。

BigQuery のユーザー定義集計関数（User-Defined Aggregate Function、UDAF） が 一般提供（GA） になりました。

• BigQuery release notes > March 03, 2025

You can create a SQL user-defined aggregate function by using the CREATE AGGREGATE FUNCTION statement. This feature is generally available (GA).

今回は、公式ドキュメント に掲載されている例を実際に動かしてみて、その動作を確認しました。

ユーザー定義集計関数（UDAF）とは？

BigQuery のユーザー定義集計関数（User-Defined Aggregate Function、UDAF）は、SQL クエリの中でユーザーが独自に定義できる集計関数です。
標準の SUM、COUNT、AVG などの関数では対応できないような、複雑な集計ロジックを関数化できます。

標準の集計関数との違い

標準の集計関数は、BigQuery が提供するあらかじめ定義された処理を使って、複数行のデータを単一の値に変換します。
一方、UDAF はユーザーが処理内容を定義できるため、以下のような用途に適しています。

• 条件に基づいて集計に含める値と除外する値を制御する
• 複数の列から得られる集約パラメータを組み合わせて集計する
• 複数の集約パラメータを処理し、より複雑な集計ロジックを実装する

これにより、標準関数では難しい複雑な集計が可能になります。

ユーザー定義関数（UDF）との違い

ユーザー定義関数（User-Defined Function、UDF）は、主に単一の行に対する処理を行う関数です。
例えば、文字列の操作や条件分岐など、各行の値を変換・加工する目的で使用されます。
対照的に、UDAF は複数の行にわたる値を集約し、一つの結果を返します。

各関数の違いを表に整理すると下記となります。

実装例

ScaledAverage：比率の平均を求める

やりたいこと

2 つの列（dividend / divisor）の比率を行ごとに計算し、その平均を求める。

関数定義の構文

CREATE OR REPLACE AGGREGATE FUNCTION mydataset.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64
)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

ロジック解説

• dividend と divisor は 集約パラメータ（行ごとに渡ってくる）
• AVG(dividend / divisor) によって、各行の比率を計算し、それらの平均を返す
• 1 ステートメントで完結するため、UDAF の構文要件を満たす

使用例

WITH sample AS (
  SELECT 100 AS dividend, 10 AS divisor UNION ALL
  SELECT 80, 20 UNION ALL
  SELECT 60, 30
)
SELECT mydataset.ScaledAverage(dividend, divisor) AS result FROM sample;

結果

| result |
|--------|
| 5.3333 |

この結果は、各行において dividend / divisor を計算し、それらの平均を求めたものです。
具体的には、以下のような計算が行われています：

• 1行目：100 / 10 = 10.0
• 2行目：80 / 20 = 4.0
• 3行目：60 / 30 = 2.0

これらの比率を平均すると

(10.0 + 4.0 + 2.0) / 3 = 16.0 / 3 = 5.3333...

このように、各行の比を個別に計算した後に平均を取るという処理を、UDAF によって関数化しています。
標準の AVG() 関数ではこのような比率の計算を関数として抽象化することはできませんが、UDAF を使えばこのようなロジックを一度定義しておけば、何度でも再利用できます。

ScaledSum：定数を使った集計

やりたいこと

dividend を合計して、外部から渡された定数（divisor）で割る。

関数定義の構文

CREATE OR REPLACE AGGREGATE FUNCTION ScaledSum(
  dividend FLOAT64,
  divisor FLOAT64 NOT AGGREGATE
)
RETURNS FLOAT64
AS (
  SUM(dividend) / divisor
);

ロジック解説

• dividend は 集約パラメータ（複数行を対象）
• divisor は 非集約パラメータ（グループ全体で1つの定数）
• NOT AGGREGATE を付けることで、divisor を定数として扱える

使用例

WITH sample AS (
  SELECT 100 AS dividend UNION ALL
  SELECT 200 UNION ALL
  SELECT 300
)
SELECT ScaledSum(dividend, 3) AS result FROM sample;

結果

| result |
|--------|
| 200.0  |

この関数では、dividend の値をすべて合計し、それを関数の引数として渡された divisor で割っています。

今回のサンプルデータでは、dividend の値は以下の通りです：

• 100
• 200
• 300

これらを合計すると

100 + 200 + 300 = 600

そして、関数呼び出し時に divisor = 3 を渡しているため、最終的な計算は

600 / 3 = 200.0

ここでのポイントは、divisor が 非集約引数（NOT AGGREGATE） として定義されていることです。
この指定により、divisor は各行に対して異なる値ではなく、グループ全体に対して 1 つの定数として扱われるため、スカラー値として正しく使用できます。

このように、集計処理の中で定数やパラメータを柔軟に使いたい場合に、UDAF の NOT AGGREGATE は有効です。

集約パラメータと非集約パラメータの違いを表に整理すると下記となります。

まとめ

以上、BigQuery のユーザー定義集計関数（UDAF）を実際に試してみました。
UDAF を使えば、標準関数では表現しにくい集計処理を関数として再利用可能な形で実装できます。

BigQuery のユーザー定義集計関数（UDAF）は、標準の集計関数では表現しきれないような複雑な集計処理を実装できる強力な機能です。
特に、複数の列を組み合わせた集計や、条件付きで値を集計したい場合、あるいはビジネスロジックに基づいた独自の集計関数を再利用可能な形で定義したい場合に非常に有効です。

今回紹介した ScaledAverage や ScaledSum のように、複数の引数を受け取って比率や加重平均を計算したり、外部から渡された定数を使って集計結果を制御したりする処理は、UDF や標準関数では適切に表現できません。
UDAF を使うことで、そうした処理を関数として切り出し、クエリの可読性や再利用性を高めることができます。

一方で、UDAF には「関数本体は1つの式で完結させる必要がある」「引数のスコープに制限がある」など、いくつかの構文的な制約も存在します。
また、処理が複雑になりすぎるとパフォーマンスに影響を与える可能性もあるため、適用する場面は慎重に見極める必要があります。

今後の分析基盤の構築やビジネスロジックを反映した集計処理の実装において、積極的に活用していきたいと思います！

参考

• User-defined aggregate functions  |  BigQuery  |  Google Cloud
• User-defined functions  |  BigQuery  |  Google Cloud