BigQueryで 最新の値 などを集計する場合に ARRAY_AGG と MAX_BY (と ROW_NUMBER)で 計算効率は違うのか？

はじめに

先日まで知らなかったのですが、BigQueryに MIN_BY や MAX_BY という集約関数が2023年の８月頃に追加されたようです。(→Reference)
これは例えば、「人口の最も大きな都市名」みたいなのを集計したいときに MAX_BY(city_name, population) というように書けるすぐれモノで、今までちょっと面倒な書き方をしないといけなかったのですが、かなり直感的に書けるようになったと言えます。

計算コストの点でも有利である、というような話を聞いたので、簡単に検証してみたので共有します。

ROW_NUMBER(), ARRAY_AGG(), MAX_BY() の計算コストの比較

今までは ROW_NUMBER や ARRAY_AGG を使った方法があったと思うので、それらと比較してみます。

case1

サンプルとして、「user_id別の key-value の更新ログ」を表す data というテーブルに対して、
(user_id, key) 毎に 最新の value を取得するというケースを用います。
※ 更新時刻は event_time という列です。

それぞれのQueryは以下のような感じになります。

ROW_NUMBER方式

SELECT
  user_id,
  key,
  value as latest_value
FROM data
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, key ORDER BY event_time DESC) = 1

ARRAY_AGG方式

SELECT
  user_id,
  key,
  ARRAY_AGG(value ORDER BY event_time DESC LIMIT 1)[OFFSET(0)] as latest_value
FROM data
GROUP BY 1, 2

MAX_BY方式

SELECT
  user_id,
  key,
  MAX_BY(value, event_time) as latest_value
FROM data
GROUP BY 1, 2

結果は以下の通りになりました。

	実行時間	課金されるバイト数	スロット（ミリ秒）
ROW_NUMBER(1回目)	46秒	33.74 GB	2,824,981
ROW_NUMBER(2回目)	50秒	33.74 GB	2,679,523
ROW_NUMBER(3回目)	49秒	33.74 GB	2,961,964
ARRAY_AGG (1回目)	49秒	33.74 GB	2,981,721
ARRAY_AGG (2回目)	60秒	33.74 GB	3,166,819
ARRAY_AGG (3回目)	55秒	33.74 GB	3,808,180
MAX_BY(1回目)	48秒	33.74 GB	2,955,808
MAX_BY(2回目)	48秒	33.74 GB	2,877,443
MAX_BY(3回目)	46秒	33.74 GB	2,851,954

注目すべきは スロット（ミリ秒）の部分かなと思います。バイト数などは参考程度に残しておきました。
しかしあれ、おかしいな。大差ない..。

よくよく考えると、ちょっとデータの期間が短かったので user_id, key 毎の更新数が少なかったので差が出なかったのかもしれません。
ちょっと違うケースでもう一度試してみます。

case2

今度は user_id 毎に (任意のKeyの)最新の value` を取得するというケースを用います。
※ あまり意味は無いですが、単なる検証なのでOKとします。
これで 集約対象となる値の数が増えるので差が出やすくなることを期待します。

それぞれのQueryは以下のような感じになります。

ROW_NUMBER方式

SELECT
  user_id,
  value as latest_value
FROM data
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) = 1

ARRAY_AGG方式

SELECT
  user_id,
  ARRAY_AGG(value ORDER BY event_time DESC LIMIT 1)[OFFSET(0)] as latest_value
FROM data
GROUP BY 1

MAX_BY方式

SELECT
  user_id,
  MAX_BY(value, event_time) as latest_value
FROM data
GROUP BY 1

結果は以下の通りになりました。

	実行時間	課金されるバイト数	スロット（ミリ秒）
ROW_NUMBER(1回目)	26秒	21.11 GB	1,613,476
ROW_NUMBER(2回目)	28秒	21.11 GB	1,465,383
ARRAY_AGG (1回目)	21秒	21.11 GB	1,040,092
ARRAY_AGG (2回目)	22秒	21.11 GB	1,029,843
MAX_BY(1回目)	18秒	21.11 GB	755,268
MAX_BY(2回目)	16秒	21.11 GB	626,505

スロット（ミリ秒）は、予想と期待通り ROW_NUMBER > ARRAY_AGG > MAX_BY という傾向が見て取れます。

さいごに

記述の簡潔さもパフォーマンスも MAX_BY, MIN_BY が優秀そうです。

ARRAY_AGG 方式のメリットはまだ少しあって、 ORDER BY event_time, another_value と順序を複数指定したいときは良い...とは思いますが、だいたいの場合は MAX_BY等を使っていくのが良さそうです。