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)が[一般提供されました](https://cloud.google.com/bigquery/docs/release-notes#August_08_2023)
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のアップデートが楽しみですね！

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

https://www.hrbrain.co.jp/recruit

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