はじめに
先日まで知らなかったのですが、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等を使っていくのが良さそうです。