SnowflakeでWhereとrow_numberの併用でハマったお話
前置き
こんにちは。株式会社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を使うのが有効です。
具体的にはこんな感じ。
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で絞り込みましょう
どうせ絞り込むんだから中間テーブルの時点で絞ったほうがいいよね（フラグ）
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をつけた中間テーブルの後で絞ればこうはなりませんでした。
要するにこういうことですね。
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では、一緒に働くデータ関連の人材を絶賛募集中です。
興味がある方は是非お声がけください。
本日はこのあたりで。
それじゃあ、バイバイ！
Discussion