🤪

snowflakeでWhereとrow_numberの併用でハマったお話

2022/07/20に公開約4,300字

前置き

こんにちは。株式会社GENDAのこみぃです。

最近はBIツールの普及などで長文のSQLを書く機会が増えてきたと思います。
SQLも長文になるとちょっとしたプログラミングで、

本日のお話は初歩的なようで初歩的でない、しかしやっぱり初歩的なお話です。

やりたかったこと

今回集計したデータはとある売上のデータなのですが、売上のデータと別で商品のマスターデータの変更履歴が別に存在するデータでした。

そのものズバリの例はもちろん出せませんが、ざっくり言うと以下のような感じです。

まず売上のデータがあります。

sales_histories

booth_id count sold_at
1 2 2022-10-01 00:00:00
1 1 2022-10-02 00:00:00
2 2 2022-10-02 00:00:00
1 5 2022-10-03 00:00:00
2 2 2022-10-03 00:00:00
1 1 2022-10-05 00:00:00

そして、booth_idに対応する販売している商品の変更履歴として以下のようなデータがあります

change_histories

booth_id item_id updated_at
1 item_001 2022-09-30 00:00:00
2 item_005 2022-10-02 00:00:00
1 item_002 2022-10-02 00:00:00
2 item_001 2022-10-02 00:00:00
2 item_002 2022-10-03 00:00:00
1 item_005 2022-10-05 00:00:00

これらをうまくつなげて、sales_historiesにitem_idを付与したいという感じですね。

やったことと

こういう場合にはrow_numberを使うのが有効です。

https://docs.snowflake.com/ja/sql-reference/functions/row_number.html

具体的にはこんな感じ。

use_row_number
with
add_row_numbers as (
select
  a.booth_id,
  a.count,
  a.sold_at,
  b.item_id,
  row_number() over (partition by booth_id order by updated_at desc) as rn  
from
  sales_histories a
  left join
  change_histories b
    on a.booth_id = b.booth_id
       and
       a.sold_at >= b.updated_at
)

select
  booth_id,
  count,
  sold_at,
  item_id
from
  add_row_numbers
where
  rn = 1
;

売上データの売上の日付以前の変更履歴をjoinした上でupdated_at順で番号をつけて、1番目のレコードだけ取り出す感じですね。

上の例で実際にSQLを実行するとこういう感じできれいに取れます。

booth_id count sold_at item_id
1 2 2022-10-01 00:00:00 item_001
1 1 2022-10-02 00:00:00 item_002
2 2 2022-10-02 00:00:00 item_001
1 5 2022-10-03 00:00:00 item_002
2 2 2022-10-03 00:00:00 item_002
1 1 2022-10-05 00:00:00 item_005

起きたこと

さて、実際にはもっと大量にレコードがありますので、特定の商品が売れた履歴だけを抽出したいことがあるでしょう。

そういうわけでwhereで絞り込みましょう
どうせ絞り込むんだから中間テーブルの時点で絞ったほうがいいよね(フラグ)

use_row_number_with_inner_where
with
add_row_numbers as (
select
  a.booth_id,
  a.count,
  a.sold_at,
  b.item_id,
  row_number() over (partition by booth_id order by updated_at desc) as rn  
from
  sales_histories a
  left join
  change_histories b
    on a.booth_id = b.booth_id
       and
       a.sold_at >= b.updated_at
where
  b.item_id = 'item_001' -- <- ここにwhereを入れた
)

select
  booth_id,
  count,
  sold_at,
  item_id
from
  add_row_numbers
where
  rn = 1
;

本当は欲しかったもの

booth_id count sold_at item_id
1 2 2022-10-01 00:00:00 item_001
2 2 2022-10-02 00:00:00 item_001

実際に出てくる出力結果

booth_id count sold_at item_id
1 2 2022-10-01 00:00:00 item_001
1 1 2022-10-02 00:00:00 item_001
2 2 2022-10-02 00:00:00 item_001
1 5 2022-10-03 00:00:00 item_001
2 2 2022-10-03 00:00:00 item_001
1 1 2022-10-05 00:00:00 item_001

?????

原因

聡い方はおわかりかと思いますが、こうなった原因は whereの位置 が原因です。
というのも、今回の位置に置いたwhereはrow_numberよりも先に適用されます。

change_historiesをwhereで絞ってからrow_numberが付与されるというわけですね。
こうなるとitem_001以外に変更した履歴が雲散霧消したのと同じになるので、すべての売上履歴がitem_001のもの扱いになってしまっていました。

対策

row_numberをつけた中間テーブルの後で絞ればこうはなりませんでした。
要するにこういうことですね。

use_row_number_with_inner_where
with
add_row_numbers as (
select
  a.booth_id,
  a.count,
  a.sold_at,
  b.item_id,
  row_number() over (partition by booth_id order by updated_at desc) as rn  
from
  sales_histories a
  left join
  change_histories b
    on a.booth_id = b.booth_id
       and
       a.sold_at >= b.updated_at
)

select
  booth_id,
  count,
  sold_at,
  item_id
from
  add_row_numbers
where
  rn = 1
  and
  b.item_id = 'item_001'  -- <- ここ!!!!!
;

まとめ

本日のまとめは以下になります。

whereをウィンドウ関数と組み合わせるときは適用される順番に気をつけよう!!!!

結びの言葉

データ抽出用のSQLは現状は実はあんまり記事がないので、これからも自分のやらかしは積極的に共有していきたいと思います。

もっと詳しいお話が知りたいという方は、私が知っている範囲ならお答えできますのでTwitterあたりでお気軽にお声がけください。
@kommy_jp

なお、株式会社GENDAでは、一緒に働くデータ関連の人材を絶賛募集中です。
興味がある方は是非お声がけください。

https://genda.jp/

本日はこのあたりで。
それじゃあ、バイバイ!

Discussion

ログインするとコメントできます