💯

BigQuery で ROW_NUMBER(), RANK() を使うな！

2022/06/16に公開

どういうことか

たとえば created_at が最も新しいレコード 1 件だけ取ってきたいとか、成績のよいレコード上位 5 件を取ってきたいといったとき、よくある方法として RANK() や ROW_NUMBER() のような番号付け関数を使う方法が思い浮かぶと思いますが、BigQuery ではこれらの関数ではなく ARRAY_AGG() 集計分析関数を使うことが推奨されています。

先に結論を

ARRAY_AGG() を使うことでクエリの計算を最適化でき、スロット使用量（計算量）が少なく済みます。スロット使用量の上限を定めている場合、非効率なクエリがいくつも実行されるとキューイングされる可能性があるため理由がなければ ARRAY_AGG() を使いましょう。

ドキュメントによれば ORDER BY 句が各 GROUP BY 句のトップレコードを除くすべてを捨てることができるため効率がいいというように記述があります。いらないレコードまで番号付けして後から捨ててしまう RANK() や ROW_NUMBER() と異なって、早い段階でトップレコード以外を切り捨てているところがミソです。

Using the ARRAY_AGG() aggregate analytic function instead of using numbering functions, such as RANK() or ROW_NUMBER(), allows a query to run more efficiently because the ORDER BY clause is allowed to drop everything except the top record on each GROUP BY clause.

SELECT
  event.*
FROM (
  SELECT id, ARRAY_AGG(
    t ORDER BY t.created_at DESC LIMIT 1
  )[OFFSET(0)] event
  FROM
    `dataset.table` t
  GROUP BY
    id
)

違いを見てみる

BigQuery には public dataset が用意されているので、今回はこの中から bigquery-public-data.austin_311.311_service_requests という手頃なサイズのテーブルを使って検証してみることにします。

たとえば、リクエストのタイプ（complaint_description）ごとの、最も新しくリクエストが作成された日時（created_date）がいつなのか知りたいとしましょう。

`RANK()` の場合

select *except(rnk) from (
  select rank() over(partition by t.complaint_description order by t.created_date desc) rnk,
  *
  from `bigquery-public-data.austin_311.311_service_requests` t
)
where rnk = 1
order by unique_key;

RANK() での実行の詳細

クエリの結果を抜粋すると以下のようになり、1000 レコードあったものが最も新しい日付のレコードが採用された上で 77 のリクエストタイプごとにまとめられます。

クエリの結果

`ARRAY_AGG()` の場合

select event.* from (
  select
    complaint_description,
    array_agg(t order by t.created_date desc limit 1)[offset(0)] event
  from `bigquery-public-data.austin_311.311_service_requests` t
  group by complaint_description
)
order by unique_key;

ARRAY_AGG() での実行の詳細

※クエリの結果はまったく同じなので省略

結果から分かること

顕著な違いとしては、経過時間・消費したスロット時間・シャッフルされたバイト数が ARRAY_AGG() の方が断然小さいところです。なお、消費したスロット時間とはクエリの実行時間全体でクエリによって消費されたスロットの合計量をミリ秒単位で考慮したもので、シャッフルされたバイト数は分散した処理ノード間でのデータのやり取りが行われたデータ量です。

消費したスロット時間のままで扱っても構いませんが、この値をクエリの実行時間（ミリ秒）で割ることでクエリの平均スロット使用量が分かるのでより直感的な値で考えてみます。そうすると RANK() では 14.26、ARRAY_AGG() では 0.33 になり、1000 レコード程度のテーブルでも文字通り桁違いに計算が最適化されていることが分かります。

また、スロットに関する詳しい話はこちらでも紹介されています。

追加で

今回挙げた計算量の最適化以外にもいくつかパフォーマンスを最適化できる観点がまとまっています。

もしちょっとでも参考になりましたら Like をお願いします！💯