【SQL】データサイエンス100本ノック（構造化データ加工編）に挑戦！！36〜40

こんにちは、ドイです！前回に引き続き、データサイエンス100本ノックに挑戦していきます。

前回の記事

tech.arms-soft.co.jp

S-036: レシート明細データ（receipt）と店舗データ（store）を内部結合し、レシート明細データの全項目と店舗データの店舗名（store_name）を10件表示せよ。

SELECT receipt.*, store.store_name FROM receipt JOIN store ON receipt.store_cd = store.store_cd LIMIT 10;

S-037: 商品データ（product）とカテゴリデータ（category）を内部結合し、商品データの全項目とカテゴリデータのカテゴリ小区分名（category_small_name）を10件表示せよ。

SELECT product.*, category.category_small_name FROM product JOIN category ON product.category_small_cd = category.category_small_cd LIMIT 10;

S-038: 顧客データ（customer）とレシート明細データ（receipt）から、顧客ごとの売上金額合計を求め、10件表示せよ。ただし、売上実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが\"Z\"から始まるもの）は除外すること。

WITH customer_amount AS (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY customer_id
)
SELECT customer.customer_id, COALESCE(customer_amount.sum_amount,0)
FROM customer
LEFT JOIN customer_amount
ON customer.customer_id = customer_amount.customer_id
WHERE customer.gender_cd = '1'
and customer.customer_id  NOT LIKE 'Z%'
LIMIT 10;

LEFT JOIN （左外部結合）

問題文に「ただし、売上実績がない顧客については売上金額を0として表示させること。」とあります。
つまり、顧客データ（customer）に存在するが、レシート明細データ（receipt）にはデータが存在しない顧客データを取得する必要があります。先に指定したテーブルに存在して、後に指定したテーブルにはないデータも含めて取得する必要があるので、この問題では LEFT JOIN （左外部結合）しています。

S-039: レシート明細データ（receipt）から、売上日数の多い顧客の上位20件を抽出したデータと、売上金額合計の多い顧客の上位20件を抽出したデータをそれぞれ作成し、さらにその2つを完全外部結合せよ。ただし、非会員（顧客IDが\"Z\"から始まるもの）は除外すること。

WITH days AS (
    SELECT customer_id, COUNT(DISTINCT(sales_ymd)) count_ymd
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP  BY customer_id
    ORDER  BY count_ymd DESC
    LIMIT 20
),
amount AS (
    SELECT customer_id, SUM(amount) sum_amount
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP  BY customer_id
    ORDER  BY sum_amount DESC
    LIMIT 20
)
SELECT COALESCE(days.customer_id, amount.customer_id), days.count_ymd, amount.sum_amount
FROM days
FULL JOIN amount
ON days.customer_id = amount.customer_id;

FULL JOIN（完全外部結合）

問題文にある通り「完全外部結合せよ」とあります。この問題では
①売上日数の多い顧客の上位20件を抽出したデータ
②売上金額合計の多い顧客の上位20件を抽出したデータ
を取得する必要があります。

①のみに該当するデータ
②のみに該当するデータ
①と②どちらにも該当するデータの3パターンが取得の対象です。

LEFT JOIN （左外部結合）では「先に指定したテーブルに存在して、後に指定したテーブルにはないデータも含めて取得」しますが、FULL JOIN（完全外部結合）は、「先に指定したテーブルにしか存在しないデータ、後に指定したテーブルにしか存在しないデータの両方を取得」することができます。

S-040: 全ての店舗と全ての商品を組み合わせたデータを作成したい。店舗データ（store）と商品データ（product）を直積し、件数を計算せよ。

SELECT COUNT(1) FROM store CROSS JOIN product;

CROSS JOIN（クロス結合）

指定した全ての組み合わせのデータを取得して、全ての組み合わせで結合します。数学の確立のようですね。（サイコロ問題のような…）

まとめ

今回は結合に関する問題が多くありました。問題を正しく理解しないと、どう結合すればいいのか悩んでしまいますね。
次回も引き続き頑張っていきます！！