ANY_VALUE(HAVING MAX)を使ったデータ処理の最適化

こんにちは、HRBrainでバックエンドエンジニアをしている稲垣です。
今年、K-POPにドハマりしてしまいましたので、アドカレでは BEST K-POP 2023 みたいな記事でバズリ狙いを考えたのですが、技術要素を盛り込むことができず断念しました。

今年は業務でBigQueryを使う機会が多かったため、今年リリースされたBigQueryの機能の中から ANY_VALUE(HAVING MAX) について紹介しようと思います。

ユースケース例

ANY_VALUE関数を説明するために、ユースケースとして履歴データの検索を考えてみたいと思います。

以下のように、ユーザごとに好きなフルーツの変更履歴を管理するテーブルがあります。

• id: 主キー
• name: ユーザ名
• fruit: フルーツ名
• date: 登録日

テーブル: user_favorite_fruits

id	name	fruit	date
1	takashi	grape	2023-12-15
2	takashi	orange	2023-12-01
3	takashi	apple	2023-11-17
4	takashi	banana	2023-11-03
5	takashi	mango	2023-10-20
6	takashi	pineapple	2023-10-06
7	takashi	kiwi	2023-09-22
8	takashi	strawberry	2023-09-08
9	takashi	lemon	2023-08-25
10	takashi	melon	2023-08-11
11	hanako	kiwi	2024-12-05
12	hanako	banana	2023-11-21
13	hanako	mango	2023-11-07
14	hanako	apple	2023-10-24
15	hanako	lemon	2023-10-10
16	hanako	orange	2023-09-26
17	hanako	strawberry	2023-09-12
18	hanako	melon	2023-08-29
19	hanako	pineapple	2023-08-15
20	hanako	grape	2023-08-01

このテーブルから各ユーザが特定の日時点で好きなフルーツを抽出します。
前提として、各ユーザはより新しい登録日のフルーツの登録があるまでは、登録日が最新のフルーツが一貫してお気に入りな、一途で良い人だとします。

たとえば、2023-11-10時点で好きなデータを抽出した結果は以下のようになることを期待しています。

id	name	fruit	date
4	takashi	banana	2023-11-03
13	hanako	mango	2023-11-07

SQLを考える

上記の結果を取得できるSQLとして、自分がまず思いついたのは以下のSQLでした。

SELECT
    f.id,
    f.name,
    f.fruit
FROM
    `[dataset].user_favorite_fruits` f
INNER JOIN (
    SELECT
        name,
        MAX(date) as max_date
    FROM
        `[dataset].user_favorite_fruits`
    WHERE
        date <= '2023-11-10'
    GROUP BY
        name
) latest_fruits ON f.name = latest_fruits.name AND f.date = latest_fruits.max_date

サブクエリ（latest_fruits）で各ユーザーについて、指定された日付（この例では'2023-11-10'）より前の最新の登録日のフルーツのエントリを見つけます。そして、メインクエリでこの情報を使用して、各ユーザーの最新の好きなフルーツを取得します。

他にもROW_NUMBER()を使う方法などもあるようですが、どちらも複数のSELECT句を組み合わせて実現することになります。

ANY_VALUE(HAVING MAX)を使って改善する

ANY_VALUE(HAVING MAX)を使うことで上記のSQLはよりシンプルに書くことができるようになります。

まず、ANY_VALUE関数についてですが、この関数自体はGROUP BY句などで、指定された列以外の列の集約値を決定する際に、任意の単一の値を選択するのに使われます。
（ただし、この際に選択される値は非決定的です。）

以下のような感じで使います。

SELECT
  name,
  ANY_VALUE(fruit) as fruit,
  MAX(date) AS max_date
FROM
  `[dataset].user_favorite_fruits`
WHERE
  date <= '2023-11-10'
GROUP BY
  name

上記のクエリではたとえば以下のような結果を返します。
この結果ではdateはMAX関数で決定していますが、fruitはANY_VALUE関数で取得しているため、なにが選択されているかはわかりません。もちろんdateとの関係もありません。

name	fruit	date
takashi	kiwi	2023-11-03
hanako	lemon	2023-11-07

これだけだと、ちょっと使いどころが難しいな、と思っていたのですが、ANY_VALUE(x HAVING MAX y)が一般提供されました
ANY_VALUE() にHAVING MAX句で値に制限を追加することができます。
ANY_VALUE(x HAVING MAX y)と記述することで、集約した結果、yが最大である行に限定しxの値を取得することができるようになっています。

これを使うことで、今回の例では以下のようにクエリを書けるようになります。

SELECT
  name,
  -- 変更前: ANY_VALUE(fruit) as fruit,
  ANY_VALUE(fruit HAVING MAX date) as fruit,
  MAX(date) AS max_date
FROM
  `[dataset].user_favorite_fruits`
WHERE
  date <= '2023-11-10'
GROUP BY
  name

どうでしょうか、最初のサブクエリを使った例と比べるとクエリがシンプルになり、格段に読みやすくなったと思います。

注意点として、ANY_VALUE(x HAVING MAX y)は非決定的で1つの値に定めるため、今回の例のように日付単位で履歴を管理していて、最新の同一日付のお気に入りフルーツがある場合は、同じ結果になりません。
（サブクエリを使っている方は同一日付のものをすべて返します。）

なので、最大値・最小値による制約で行が一意に定まりやすいテーブルで使うことでより恩恵を受けることができると思います。

まとめ

今年GAになった ANY_VALUE(x HAVING MAX y) について紹介させていただきました。
自分も、履歴データを扱うユースケースでこの句を活用することができました。
今年もたくさんのアップデートがありましたが、来年もBigQueryのアップデートが楽しみですね！

弊社ではエンジニアを募集しています！

それでは、良い年末をお過ごしください！