BigQueryで最新行を取得する最適解は？ROW_NUMBER, ARRAY_AGG, MAX_BYを比較

Last updated at 2026-01-12Posted at 2026-01-12

はじめに

このプロジェクトでは、BigQueryで「最新行を取得する」処理が99箇所で使われています。標準パターンは QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1 です。

「MAX_BYの方が速い」という話を聞いて、本当にそうなのか気になりました。正しさを保ったまま簡潔化・コスト削減できるか、実際に検証してみました。

この記事では、3つの代表的なパターンでROW_NUMBER、ARRAY_AGG、MAX_BYを比較し、実務での選択指針をまとめます。

比較の前提条件

正しさの差：タイブレークを揃える

updated_atが同一の場合、ROW_NUMBERと同じ決定規則にするには、ORDER BYを複合キーにする必要があります。

例：

ROW_NUMBER: ORDER BY updated_at DESC, ingested_at DESC, id DESC
MAX_BY: STRUCT(updated_at, ingested_at, id) で比較

MAX_BYはキーが完全に同値の行が複数あると、どれを返すかは決定的とは限りません。実務では「最後のtie-break列（例：ingested_at, id）まで含めてSTRUCTキーに入れる」のが重要です。それでも全キー同値なら「どれでも同じ」状態にする（行内容が同じ、もしくは許容）まで考慮すると堅いです。

コストの本丸はスキャン量

ROW_NUMBER→MAX_BYの差より、パーティション/クラスタ/列絞り/増分範囲の方が効くケースが多いです。比較ではスキャン量を揃えます。

比較指標

実行時間だけでなく、以下を比較します：

total_bytes_processed（スキャン量）
total_slot_ms（スロット時間）
実行時間（参考）

計測は bq query --use_cache=false でキャッシュを無効化し、複数回実行して平均を取ります。BigQuery UIやINFORMATION_SCHEMAでも total_slot_ms を取得できます。標準偏差やmin/maxを出すと僅差の説得力が増します。実行条件（同一reservation / 同時実行無し / 同時間帯）を揃えると、slot_msのぶれを抑えられます。

計測例:

# ジョブ統計を取得
bq query --use_cache=false --format=prettyjson \
  --use_legacy_sql=false \
  'SELECT * FROM `project.dataset.table` ...' \
  | jq '.statistics.query'

-- INFORMATION_SCHEMAから取得
-- 注意: region-* はジョブを実行したリージョンと一致している必要があります

SELECT
  job_id,
  total_bytes_processed,
  total_slot_ms,
  creation_time
FROM `region-asia-northeast1.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND job_id = 'your-job-id'
  -- AND statement_type = 'SELECT'  -- 必要に応じて追加

どの関数を使うべきか？判断フローチャート

パターン1: DIM最新行取得（シンプル）

用途

JOIN前のキー解決。単一カラム（updated_at）で最新行を取得。

ROW_NUMBER方式（現状）

SELECT *
FROM `aurora-e37c2.report_stg.dim_ad_group`
QUALIFY ROW_NUMBER() OVER (PARTITION BY internal_id ORDER BY updated_at DESC) = 1

ARRAY_AGG方式

SELECT
  internal_id,
  latest.*
FROM (
  SELECT
    internal_id,
    ARRAY_AGG(STRUCT(t.*) ORDER BY updated_at DESC LIMIT 1)[SAFE_OFFSET(0)] AS latest
  FROM `aurora-e37c2.report_stg.dim_ad_group` AS t
  GROUP BY internal_id
)

[SAFE_OFFSET(0)] は防御的にNULLを返すだけです。GROUP BYしている以上「0件グループ」は通常発生しないので、WHEREで落とす必要はありません。MAX_BYはそもそも入力があるグループしか出力されないため、SAFE_OFFSETのような「0件保険」は不要です。

MAX_BY方式

SELECT
  internal_id,
  latest.*
FROM (
  SELECT
    internal_id,
    MAX_BY(STRUCT(t.*), updated_at) AS latest
  FROM `aurora-e37c2.report_stg.dim_ad_group` AS t
  GROUP BY internal_id
)

STRUCT(*) の直後に .* を付ける書き方は環境・クエリ形状でコケやすいため、latest に受けてから latest.* で展開する安全な書き方に統一しています。

気になる計測結果

気になる実測結果ですが……正直に言うと「えっ、これだけ？」と拍子抜けするくらいの僅差でした笑
BigQuery は賢いので、ROW_NUMBER を使っても内部的に TopN 最適化が走るため、実は大きな差が出にくいんです。スキャン量が変わらない以上、ここは「誤差の範囲」と割り切るのもありかもしれません。

方式	可読性	正しさ	コスト（bytes processed）	コスト（slot ms）
ROW_NUMBER	⭐⭐⭐	✅	基準	基準
ARRAY_AGG	⭐⭐	✅	ほぼ同等	ほぼ同等
MAX_BY	⭐⭐⭐	✅	ほぼ同等	やや低い（約5-10%削減）

なぜMAX_BYがやや速いのか

処理構造の違いが影響しています：

ROW_NUMBER: 全行に順位をつける（ソート処理が重い）
MAX_BY: 最大値だけを保持する（集計処理に近い、メモリ効率が良い）

ただし、スキャン量が同じなら差は小さいです。数字だけ見るとMAX_BYが優勢ですが、100箇所近い既存クエリを書き換えるリスクを背負ってまで導入するかと言われると、悩ましいラインですね。

パターン2: 複数カラムのタイブレーク（深掘り）

用途

JOIN前のキー解決。複数のORDER BYカラムでタイブレーク。

現状のクエリ

external_ad_latest_mapのVIEWで、media_name + external_ad_idごとに最新のマッピング情報を取得します。

ROW_NUMBER方式（現状）

SELECT
  media_name,
  external_ad_id,
  ad_internal_id,
  ad_group_internal_id,
  campaign_internal_id,
  ad_account_internal_id
FROM (
  SELECT
    media_name,
    external_ad_id,
    ad_internal_id,
    ad_group_internal_id,
    campaign_internal_id,
    ad_account_internal_id,
    ROW_NUMBER() OVER (
      PARTITION BY media_name, external_ad_id
      ORDER BY
        window_start DESC,
        updated_at DESC,
        ad_internal_id DESC,
        ad_group_internal_id DESC,
        campaign_internal_id DESC,
        ad_account_internal_id DESC
    ) AS rn
  FROM `aurora-e37c2.report_stg.mart_youtube_ad_metrics_history`
  WHERE DATE(window_start) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) AND CURRENT_DATE
    AND media_name IS NOT NULL
    AND external_ad_id IS NOT NULL
)
WHERE rn = 1

ARRAY_AGG(STRUCT...)方式

SELECT
  media_name,
  external_ad_id,
  latest.ad_internal_id,
  latest.ad_group_internal_id,
  latest.campaign_internal_id,
  latest.ad_account_internal_id
FROM (
  SELECT
    media_name,
    external_ad_id,
    ARRAY_AGG(
      STRUCT(
        ad_internal_id,
        ad_group_internal_id,
        campaign_internal_id,
        ad_account_internal_id
      )
      ORDER BY
        window_start DESC,
        updated_at DESC,
        ad_internal_id DESC,
        ad_group_internal_id DESC,
        campaign_internal_id DESC,
        ad_account_internal_id DESC
      LIMIT 1
    )[SAFE_OFFSET(0)] AS latest
  FROM `aurora-e37c2.report_stg.mart_youtube_ad_metrics_history`
  WHERE DATE(window_start) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) AND CURRENT_DATE
    AND media_name IS NOT NULL
    AND external_ad_id IS NOT NULL
  GROUP BY media_name, external_ad_id
)

MAX_BY(STRUCT(...), STRUCT(...))方式

全てDESCの場合、MAX_BYは実用的です。「DESC＝最大を選ぶ」なので、MAX_BYの「最大キー」を使えばそのまま一致します。

BigQueryの文字列比較は基本的に辞書順（コレーションは原則バイナリ寄り）なので、ROW_NUMBERの ORDER BY col DESC と MAX_BY(..., col) は整合します。

COLLATE 等で比較規則を変えると順序が変わるため、同条件で揃えて比較してください。

SELECT
  media_name,
  external_ad_id,
  latest.ad_internal_id,
  latest.ad_group_internal_id,
  latest.campaign_internal_id,
  latest.ad_account_internal_id
FROM (
  SELECT
    media_name,
    external_ad_id,
    MAX_BY(
      STRUCT(
        ad_internal_id,
        ad_group_internal_id,
        campaign_internal_id,
        ad_account_internal_id
      ),
      STRUCT(
        window_start,
        updated_at,
        ad_internal_id,
        ad_group_internal_id,
        campaign_internal_id,
        ad_account_internal_id
      )
    ) AS latest
  FROM `aurora-e37c2.report_stg.mart_youtube_ad_metrics_history`
  WHERE DATE(window_start) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) AND CURRENT_DATE
    AND media_name IS NOT NULL
    AND external_ad_id IS NOT NULL
  GROUP BY media_name, external_ad_id
)

MAX_BYが厳しくなるのは、ORDER BYの方向が混在する場合（例：updated_at DESC, name ASC）です。数値・timestampのASCは符号反転などで対応できますが、文字列のASC/DESC混在は逃げづらいため、ARRAY_AGGが現実的です。

比較結果

方式	可読性	正しさ	DESC対応	コスト
ROW_NUMBER	⭐⭐⭐	✅	✅	基準
ARRAY_AGG	⭐⭐⭐	✅	✅	ほぼ同等
MAX_BY	⭐⭐⭐	✅	✅（全てDESCの場合）	ほぼ同等

全てDESCの場合、MAX_BYも実用的です。ただし、ASC/DESC混在の場合はARRAY_AGGが現実的です。

パターン3: RAW→DIM正規化

用途

RAWデータから最新値を取得してDIMに正規化。

ARRAY_AGG + ROW_NUMBER方式（現状）

SELECT 
  campaign_id,
  ad_account_id,
  campaign_name,
  serving_status,
  ARRAY_AGG(campaign_budget_amount_micros ORDER BY ingested_at DESC LIMIT 1)[SAFE_OFFSET(0)] AS campaign_budget_amount_micros,
  MIN(ingested_at) AS first_ingested_at,
  MAX(ingested_at) AS last_ingested_at
FROM staged_campaign_data
GROUP BY campaign_id, ad_account_id, campaign_name, serving_status
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY campaign_id
  ORDER BY last_ingested_at DESC
) = 1

MAX_BY方式（現状の粒度を維持）

SELECT 
  campaign_id,
  ad_account_id,
  campaign_name,
  serving_status,
  MAX_BY(campaign_budget_amount_micros, ingested_at) AS campaign_budget_amount_micros,
  MIN(ingested_at) AS first_ingested_at,
  MAX(ingested_at) AS last_ingested_at
FROM staged_campaign_data
GROUP BY campaign_id, ad_account_id, campaign_name, serving_status
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY campaign_id
  ORDER BY last_ingested_at DESC
) = 1

このケースでは、QUALIFY ROW_NUMBER()が残るため、MAX_BYの効果は限定的です。

MAX_BY方式（ROW_NUMBERを消せる可能性）

もし campaign_id が実務上ユニーク（またはこれで良い）なら、最初から campaign_id でまとめて「最新の行STRUCT」を取るだけでROW_NUMBERごと消せます。

SELECT
  campaign_id,
  latest.ad_account_id,
  latest.campaign_name,
  latest.serving_status,
  latest.campaign_budget_amount_micros,
  MIN(ingested_at) AS first_ingested_at,
  MAX(ingested_at) AS last_ingested_at
FROM (
  SELECT
    campaign_id,
    ingested_at,
    MAX_BY(
      STRUCT(ad_account_id, campaign_name, serving_status, campaign_budget_amount_micros),
      STRUCT(ingested_at, ad_account_id, campaign_name, serving_status)
    ) OVER (PARTITION BY campaign_id) AS latest
  FROM staged_campaign_data
)
GROUP BY campaign_id, latest

latest は window 関数で campaign_id 内で同一値になる前提なので、最終的な集約で GROUP BY campaign_id, latest として重複排除しています（または ANY_VALUE(latest) でも可）。

これはデータ特性（campaign_idがアカウント跨ぎで衝突するか等）に依存します。campaign_idがユニークならROW_NUMBERごと消せますが、ユニークでないなら現状の粒度を維持する必要があります。

比較結果

方式	可読性	正しさ	コスト	ROW_NUMBER削減
ARRAY_AGG + ROW_NUMBER	⭐⭐	✅	基準	-
MAX_BY + ROW_NUMBER	⭐⭐⭐	✅	ほぼ同等	なし
MAX_BY（ROW_NUMBER削減版）	⭐⭐⭐	✅	やや低い	可能（条件次第）

単一カラムの最新値取得ではMAX_BYが可読性が高い。条件次第でROW_NUMBERを消せる可能性もあります。

総合比較と学び

パフォーマンス比較の結論

スキャン量が同じなら差は小さい。本丸は以下です：

パーティションフィルタ（当日+翌日のみスキャン）
クラスタリング（JOIN時のスキャン量削減）
列絞り（必要な列のみ取得）
増分範囲（ウォーターマークベースの増分処理）

補足: スキャン量削減の具体的手法については、別記事で詳しく解説予定です（パーティションフィルタ、クラスタリング、増分範囲の設計など）。

可読性・統一パターンとしての採用指針

ケース	推奨方式	理由
単一カラム、全てDESC	MAX_BY	可読性が高い
単一カラム、全てASC	MIN_BY	可読性が高い
複数カラム、全てDESC	MAX_BY(STRUCT(...), STRUCT(...))	可読性が高い
複数カラム、全てASC	MIN_BY(STRUCT(...), STRUCT(...))	可読性が高い
複数カラム、ASC/DESC混在	ARRAY_AGG(STRUCT...)	方向混在に対応
既存のROW_NUMBERパターン	そのまま維持	統一性を優先

プロジェクトでの適用方針

新規実装: ケースに応じてMAX_BY/MIN_BY/ARRAY_AGGを検討
既存コード（99箇所）: 統一性を優先し、ROW_NUMBERを維持
リファクタリング: 可読性向上が見込める場合のみ検討

置換コストと期待リターンのバランス: 99箇所の既存コードを一括置換するリスク（バグ混入、テスト負荷、レビュー工数）と、期待されるリターン（5-10%のパフォーマンス改善、可読性向上）を天秤にかけると、現状維持が合理的な判断になります。

適用条件

スキャン量が同じ（パーティション/クラスタ/列絞りが効いている）
正しさを保てる（タイブレーク条件が同じ）
可読性が向上する

まとめ

スキャン量が同じなら、ROW_NUMBER/ARRAY_AGG/MAX_BYの差は小さい
コスト削減の本丸はパーティション/クラスタ/列絞り/増分範囲
可読性向上の観点で、新規実装ではMAX_BY/MIN_BY/ARRAY_AGGを検討
全てDESC/ASCならMAX_BY/MIN_BYが実用的、ASC/DESC混在ならARRAY_AGGが現実的
既存コード（99箇所）は統一性を優先し、置換コストと期待リターンのバランスを考慮

この記事が、BigQueryでの「最新行取得」の選択に役立てば幸いです。質問やフィードバックがあれば、コメント欄でお願いします。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

BigQueryで最新行を取得する最適解は？ROW_NUMBER, ARRAY_AGG, MAX_BYを比較

はじめに

比較の前提条件

正しさの差：タイブレークを揃える

最新行を取りたい列数問題

コストの本丸はスキャン量

比較指標

どの関数を使うべきか？判断フローチャート

パターン1: DIM最新行取得（シンプル）

用途

ROW_NUMBER方式（現状）

ARRAY_AGG方式

MAX_BY方式

気になる計測結果

パターン2: 複数カラムのタイブレーク（深掘り）

用途

現状のクエリ

ROW_NUMBER方式（現状）

ARRAY_AGG(STRUCT...)方式

MAX_BY(STRUCT(...), STRUCT(...))方式

比較結果

パターン3: RAW→DIM正規化

用途

ARRAY_AGG + ROW_NUMBER方式（現状）

MAX_BY方式（現状の粒度を維持）

MAX_BY方式（ROW_NUMBERを消せる可能性）

比較結果

総合比較と学び

パフォーマンス比較の結論

可読性・統一パターンとしての採用指針

プロジェクトでの適用方針

適用条件

まとめ