クエリを書くときは確率的な挙動にならないように注意しよう

自分用のメモです。以下のエントリで便利なスクリプトを作りました。

不可解な現象に遭遇

このスクリプトを使って、いくつかのデータを調べていましたが、ぱっと見不思議な現象に遭遇しました。

比較対象のテーブルAとB(クエリは同一のものを仕様。作成元がprodとdevで異なる)があり、特定のカラムで差分が大量に発生している
クエリ内のリネージを調べたところ、元データは完全に一致していることが分かった

罠はウィンドウ関数の利用方法にあった

「じゃあ、どこで差分が発生するんだよ...」となるわけですが、データに原因がないとすると、ありえるのはクエリです。乱数を利用しているわけでもないクエリで何で差分が発生するのか少し調べたところ、ウィンドウ関数を利用している箇所に問題があることが分かりました。

具体例があったほうが分かりやすいので、以下のような注文データを考えてみましょう。ユーザーが購入した金額とその日付が入っているようなシンプルな例です。ChatGPT君に作ってもらいました。

WITH order_data AS (
  SELECT * FROM UNNEST([
    STRUCT('Alice' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Alice' AS name, 120 AS price, '2024-08-02' AS created_at),
    STRUCT('Alice' AS name, 140 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Alice' AS name, 200 AS price, '2024-08-03' AS created_at),

    STRUCT('Bob' AS name, 250 AS price, '2024-08-01' AS created_at),
    STRUCT('Bob' AS name, 270 AS price, '2024-08-01' AS created_at),    -- 同率
    STRUCT('Bob' AS name, 300 AS price, '2024-08-02' AS created_at),
    STRUCT('Bob' AS name, 350 AS price, '2024-08-03' AS created_at),

    STRUCT('Charlie' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Charlie' AS name, 110 AS price, '2024-08-02' AS created_at),
    STRUCT('Charlie' AS name, 130 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Charlie' AS name, 150 AS price, '2024-08-03' AS created_at)
  ])
)
SELECT
  *
FROM
  order_data

このデータについて、例えばユーザーの初回購入に興味があったとしましょう。ウィンドウ関数であるROW_NUMBERやDENSE_RANKを使うと、初回購入に関するデータが絞れそうですね。

WITH order_data AS (
  SELECT * FROM UNNEST([
    STRUCT('Alice' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Alice' AS name, 120 AS price, '2024-08-02' AS created_at),
    STRUCT('Alice' AS name, 140 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Alice' AS name, 200 AS price, '2024-08-03' AS created_at),

    STRUCT('Bob' AS name, 250 AS price, '2024-08-01' AS created_at),
    STRUCT('Bob' AS name, 270 AS price, '2024-08-01' AS created_at),    -- 同率
    STRUCT('Bob' AS name, 300 AS price, '2024-08-02' AS created_at),
    STRUCT('Bob' AS name, 350 AS price, '2024-08-03' AS created_at),

    STRUCT('Charlie' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Charlie' AS name, 110 AS price, '2024-08-02' AS created_at),
    STRUCT('Charlie' AS name, 130 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Charlie' AS name, 150 AS price, '2024-08-03' AS created_at)
  ])
)
SELECT
  name,
  price,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY name ORDER BY created_at) AS row_number,
  DENSE_RANK() OVER (PARTITION BY name ORDER BY created_at) AS rank,
FROM order_data

あとはrow_numberかrankが1の行に絞れば初回購入が絞れそう...と思いきや、罠がここにあります。Bobが最初に購入した日付は2024-08-01ですが、この日にBobの購入に該当するレコードは2行あります。

こういったケースの場合、クエリ結果のrow_numberは順序不定になるため、270円のレコードのほうが250円のレコードより先にくることもありえます。rankは順序不定にはならなさそうですが、初回購入に関するレコードがユーザーの単位で重複してしまうため、適切ではなさそうです。

ざっくりとした集計をする場合には特に問題ないケースもあるかもしれませんが、特に信頼性が要求されるデータマートやエンドユーザーに使ってもらうデータプロダクトを作っている場合、こうした確率的な挙動をするクエリは注意が必要です。結果の再現がうまくできなかったり、ほとんど合ってるのに微妙な数値のずれに悩まされる原因になります。

うまく気付くためには?

「じゃあ、どうやったらこういうケースに気付けるか?」ですが、PARTITION BYで指定するカラムとORDER BYで指定するカラムでGROUP BYすれば、確率的な挙動の原因になる重複のレコードがあるかが分かります。より適切なカラムを指定するか、ORDER BYで指定するカラムを増やして確率的にならないようにしてあげるとよさそうですね。

WITH order_data AS (
  SELECT * FROM UNNEST([
    STRUCT('Alice' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Alice' AS name, 120 AS price, '2024-08-02' AS created_at),
    STRUCT('Alice' AS name, 140 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Alice' AS name, 200 AS price, '2024-08-03' AS created_at),

    STRUCT('Bob' AS name, 250 AS price, '2024-08-01' AS created_at),
    STRUCT('Bob' AS name, 270 AS price, '2024-08-01' AS created_at),    -- 同率
    STRUCT('Bob' AS name, 300 AS price, '2024-08-02' AS created_at),
    STRUCT('Bob' AS name, 350 AS price, '2024-08-03' AS created_at),

    STRUCT('Charlie' AS name, 100 AS price, '2024-08-01' AS created_at),
    STRUCT('Charlie' AS name, 110 AS price, '2024-08-02' AS created_at),
    STRUCT('Charlie' AS name, 130 AS price, '2024-08-02' AS created_at),  -- 同率
    STRUCT('Charlie' AS name, 150 AS price, '2024-08-03' AS created_at)
  ])
)
SELECT
  name,
  created_at,
  COUNT(*) AS cnt,
FROM
  order_data
GROUP BY name, created_at
ORDER BY cnt DESC