結合で散らばったデータを繋ぐ

目標

内部結合、左外部結合、アンチ結合、自己結合を状況に応じて使い分け、ON句とWHERE句の違いを説明できるようになります。

なぜ重要なのか

結合で決めるべきことは、実は1つだけです。対応する行がない行を、残すのか捨てるのか。この決定を明確にしないと、レポートの数字が静かに誤ります。

特に危険なのは、左外部結合の後のWHERE句です。右側のテーブルのカラムに条件をかけると、対応する行がなくてNULLが入った行が、その条件で不明になり、まるごと消えます。つまり、外部結合を使ったのに、結果は内部結合になるのです。エラーも警告もなく静かに起こるので、このラボで2つのケースを並べて作り、行数を数えてみる経験が重要です。

もう1つは、ファンアウトです。注文1つに明細が3つあれば、結合結果は3行になり、その上で注文金額を合計すると3倍になります。結合が行数を変えるという事実を、常に意識する必要があります。

ステップ

注文と顧客を結合して、order_id、customer_name、total_amountを含むビューv_order_customerを作ります。
すべての顧客と、その顧客の注文件数を含むビューv_customer_ordersを作ります。カラムはid、name、order_countで、注文がない顧客は0である必要があり、顧客数は400人のままである必要があります。
一度も注文していない顧客のid、nameを含むビューv_never_orderedを作ります。
注文明細と商品を結合して、order_id、product_name、quantity、unit_priceを含むビューv_item_detailを作ります。
countryがKRの顧客の、statusがpaidの注文の明細を含むビューv_kr_paid_itemsを作ります。カラムはorder_id、product_idで始まる必要があります。
すべての顧客と、その顧客の決済完了の注文件数を含むビューv_left_paidを作ります。カラムはid、paid_countで、顧客数は400人のままである必要があります。
同じ都市に住むtierがvipの顧客のペアを含むビューv_vip_pairsを作ります。カラムはa_id、b_id、cityで、同じペアが2回出てはいけません。
statusがpaid、shipped、deliveredのいずれかなのに、完了した決済がない注文を含むビューv_unpaid_ordersを作ります。

参考

結合結果の行数を先に数えてみる習慣が、バグを減らします。
count(*)とcount(컬럼)の違いを、ステップ2で自分で確認してください(プレースホルダーはカラム名です)。
よくある間違い1: ステップ6でAND o.status = 'paid'をWHEREに移すと、顧客数が減ります。
よくある間違い2: ステップ8で決済行の存在だけを確認すると、失敗した決済がある注文を見逃します。

注文に顧客名を付ける

注文と顧客を結合して、order_id、customer_name、total_amountを含むビューv_order_customerを作ります。

2つのテーブルをつなぐカラムをON句に書きます。結合条件を抜かすと、行が掛け算で増えます。

注文のない顧客まで残す

すべての顧客と、その顧客の注文件数を含むビューv_customer_ordersを作ります。カラムはid、name、order_countで、注文がない顧客は0である必要があり、顧客数は400人のままである必要があります。

左側のテーブルをすべて残す結合を使います。件数を数えるときにcount(*)を使うと、対応する行がない行も1になります。

一度も注文していない顧客を探す

一度も注文していない顧客のid、nameを含むビューv_never_orderedを作ります。

存在しないことを確認する方法は、いくつかあります。NOT EXISTSがNULLに対して安全です。

3つのテーブルをつなぐ

注文明細と商品を結合して、order_id、product_name、quantity、unit_priceを含むビューv_item_detailを作ります。

結合は何度でもつなげられます。各結合ごとに、条件を抜かしていないか確認してください。

結合結果に条件をかける

countryがKRの顧客の、statusがpaidの注文の明細を含むビューv_kr_paid_itemsを作ります。カラムはorder_id、product_idで始まる必要があります。

顧客テーブルの条件と注文テーブルの条件が、それぞれどこに付くべきか考えてみてください。

ON句とWHERE句の違いを確認する

すべての顧客と、その顧客の決済完了の注文件数を含むビューv_left_paidを作ります。カラムはid、paid_countで、顧客数は400人のままである必要があります。

外部結合で右側のテーブルの条件をWHEREに移すと、対応する行がない行が消えます。全体の顧客数が維持されているか数えてみてください。

同じテーブルを自分自身と結合する

同じ都市に住むtierがvipの顧客のペアを含むビューv_vip_pairsを作ります。カラムはa_id、b_id、cityで、同じペアが2回出てはいけません。

同じテーブルに、異なるエイリアスを付けます。同じペアが2回出ないようにするには、idの間に不等号の条件をかけます。

決済が確認されていない注文を探す

statusがpaid、shipped、deliveredのいずれかなのに、完了した決済がない注文を含むビューv_unpaid_ordersを作ります。

決済行がまったくない場合と、あるが失敗した場合の両方を含める必要があります。存在条件の中に、状態の条件まで入れる必要があります。