30
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

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

Last updated at Posted at 2023-12-08

こんにちは、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のアップデートが楽しみですね!

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

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

30
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
30
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?