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

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

前回の記事

tech.arms-soft.co.jp

S-052: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計の上、売上金額合計に対して2,000円以下を0、2,000円より大きい金額を1に二値化し、顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが\"Z\"から始まるのものは非会員を表すため、除外して計算すること。

SELECT customer_id, SUM(amount) as sum_amount,
　　　CASE
　　　WHEN SUM(amount) <= 2000 THEN 0
　　　WHEN SUM(amount) > 2000 THEN 1
　　　END AS sum_amount_flg
FROM receipt
WHERE customer_id NOT LIKE 'Z%'
GROUP BY customer_id
LIMIT 10;

取得結果

S-053: 顧客データ（customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に二値化せよ。さらにレシート明細データ（receipt）と結合し、全期間において売上実績のある顧客数を、作成した二値ごとにカウントせよ。

WITH customer_flg AS (
    SELECT
        customer_id,
        postal_cd,
        CASE
            WHEN CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) BETWEEN 100 AND 209 THEN 1
            ELSE 0
        END AS postal_flg
    FROM
        customer
),
receipt_customer AS(
    SELECT DISTINCT
        customer_id
    FROM
        receipt
)
SELECT 
    customer_flg.postal_flg, 
    COUNT(DISTINCT customer_flg.customer_id) AS customer_cnt
FROM
    customer_flg
JOIN
    receipt_customer
ON (customer_flg.customer_id = receipt_customer.customer_id) 
GROUP BY
    customer_flg.postal_flg;

取得結果

副問い合わせした結果を、JOINで結合しています。
一つ目のSELECT文では、顧客データテーブル（customer）の郵便番号（postal_cd）を、CASE文の中で、東京都ならば1、それ以外ならば0のフラグ分けをしています。
二つ目のSELECT文では、レシート明細テーブル（receipt）から、重複した顧客IDを除外して取得しています。
三つ目のSELECT文では、一つ目と二つ目の結果を結合して、郵便番号のフラグごとの顧客数をカウントしています。

結合の条件を書く際に、ON句を使用していますが、下記のような書き方もできます。

USING (customer_id);

S-054: 顧客データ（customer）の住所（address）は、埼玉県、千葉県、東京都、神奈川県のいずれかとなっている。都道府県毎にコード値を作成し、顧客ID、住所とともに10件表示せよ。値は埼玉県を11、千葉県を12、東京都を13、神奈川県を14とすること。

SELECT customer_id, address,
CASE SUBSTR(address,1, 3)
WHEN '埼玉県' THEN 11
WHEN '千葉県' THEN 12
WHEN '東京都' THEN 13
WHEN '神奈川' THEN 14
END AS pref_code
FROM customer
LIMIT 10;

取得結果

少し無理やり感がありますね。CASE文の条件に統一感がないのでLIKE検索を使った方がいいかなと思います。
LIKE検索を使った場合は、下記のようになります。

SELECT customer_id, address,
CASE
WHEN address LIKE '埼玉県' THEN 11
WHEN address LIKE '千葉県' THEN 12
WHEN address LIKE '東京都' THEN 13
WHEN address LIKE '神奈川県' THEN 14
END AS pref_code
FROM customer
LIMIT 10;

S-055: レシート明細（receipt）データの売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額合計とともに10件表示せよ。カテゴリ値は順に1〜4とする。

・最小値以上第1四分位未満・・・ 1を付与
・第1四分位以上第2四分位未満・・・ 2を付与
・第2四分位以上第3四分位未満・・・ 3を付与
・第3四分位以上・・・ 4を付与

WITH customer_amount AS(
SELECT customer_id, SUM(amount) AS sum_amount
FROM receipt
GROUP BY customer_id),
per_amount AS (
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS per_25,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS per_50,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS per_75,
FROM customer_amount
)
SELECT customer_id, sum_amount,
CASE
WHEN sum_amount < per_25 THEN 1
WHEN per_25 <= sum_amount AND sum_amount < per_50 THEN 2
WHEN per_50 <= sum_amount AND sum_amount < per_75 THEN 3
WHEN per_75 <= sum_amount THEN 4
END AS per_GROUP
FROM per_amount
CROSS  JOIN customer_amount
LIMIT 10;

取得結果

問題に見覚えがあるかもしれません。
問題：S-032「レシート明細データ（receipt）の売上金額（amount）について、25％刻みでパーセンタイル値を求めよ。」の応用です。

S-032の回答は下記です。

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75,
    PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100
FROM receipt;

まとめ

どう求めたらいいかわからない場合でも、問題を分解すれば理解できるようになってきました。
引き続き、頑張りたいと思います！！