Zenn
🚗

いちいち冪等性を崩しがちな ORDER BY

2024/05/20に公開
SQL
window関数
冪等性
tech

はじめに

ブログの動機

ソートした際に同順位の値が複数ある際、実行のタイミングで異なる結果を返すことは知識として身についている方が多く、またそれ自体が問題になるケースはあまり多くありません。ORDER BYを使うタイミングは基本的に表示を整える時が多く、そのようなケースで問題になることは少ないからです。ただしwindow関数と組み合わせた際、冪等性を崩すことになることは知識として知っていたとしてもうっかり抜け落ちるケースを多くの現場で見てきたので、改めて備忘録としてメモしておきます。

なぜ抜け落ちてしまいがちか？

そもそもなぜミスしがちなのか。色々な理由があるとは思いますが、例えば以下のようなケースが考えられるかと思います。

  • dbtやDataformを使ったテストなどの際、結果自体の整合性は確認することが多いものの、複数回やった時に結果が変わるかどうかをテストすることがそもそも少ない
  • SQLを複数回動かして結果が変わるケース自体が少ないため、うっかり気づかないことが多い
  • order by でソートする際に指定したキーの一意性が処理の途中で崩れることがある

検証

例として以下のようなクエリをBig Query上で実行してみます。

create or replace temp table team
(
  id int64
  , ratio float64
  , check string
);

insert into team
values
  (1, 0.2, 'takashi'),
  (1, 0.3, 'ichiro'),
  (1, null, 'ken'),
  (1, null, 'takashi'),
  (2, 0.5, 'ichiro'),
  (2, null, 'yusuke'),
  (2, null, 'atsushi'),
  (2, 1.2, 'taro'),
  (3, null, 'jiro'),
  (3, null, 'saburo'),
  (3, null, 'taro')
;

select
  *
from
  team
qualify
  row_number() over (partition by id order by ratio) = 1
order by
  id
;

一度実行したクエリの結果とキャッシュを削除して再度実行したクエリの結果です。

case1 case2

order by ratioした際にnullが複数あるため、どのレコードが先頭に来るのか実行のたびに変わってしまっています。
今回はrow_number()で検証してみましたが、順位が変わることで影響を受ける関数（例えばfirst_value()など）は等しく同じような問題を抱えています。

まとめ

考えられる対応策としては以下のようなものがあります。

  • （可能な限り）order byを使う際は、その後の処理で冪等性が崩れないかを確認する
  • order byの対象となるキーの一意性が保証されているかを確認する

特に2は大切な観点かなと思います。おそらくorder byとwindow関数を組み合わせる際、多くのケースで一意性を前提としているケースが多いように思えます。そうでなく単純な順位であればrank()などの方が意味合い的にも冪等性の観点からも矛盾がないからです。最終結果のみならず中間テーブル、特に確認漏れしがちなサブクエリも含め、モデリングの肝となるであろうキーの一意性については改めて意識していく必要がありそうです。

Discussion