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

SQL バックエンド

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

前回の記事

tech.arms-soft.co.jp

S-056: 顧客データ（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに10件表示せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。

SELECT customer_id, birth_day,
CASE
WHEN age < 10 THEN '10歳以下'
WHEN age >= 10 AND age < 20 THEN '10代'
WHEN age >= 20 AND age < 30 THEN '20代'
WHEN age >= 30 AND age < 40 THEN '30代'
WHEN age >= 40 AND age < 50 THEN '40代'
WHEN age >= 50 AND age < 60 THEN '50代'
WHEN age >= 60 THEN '60代'
END AS every_age
FROM customer
LIMIT 10;

年代をCASE文で取得しましたが、力技な気がします。
回答を確認したところ、以下のように書いていました。

SELECT
    customer_id,
    birth_day,
    -- 確認用の項目
    -- age,
    LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) AS era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
-- 確認用の条件
-- HAVING LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) >= 60
LIMIT 10;

LEAST：LEAST関数は式の任意の数のリストから最小値を選択します。 評価される全ての式は、結果として得られるデータの型と共通の型に変換できなくてはなりません。
www.postgresql.jp TRUNC：切り捨て
www.postgresql.jp

こちらの方が綺麗に書けていますが、グルーピングする必要があるのか？と疑問でした。

S-057: 056の抽出結果と性別コード（gender_cd）により、新たに性別×年代の組み合わせを表すカテゴリデータを作成し、10件表示せよ。組み合わせを表すカテゴリの値は任意とする。

SELECT
customer_id,
birth_day,
gender_cd || TO_CHAR(LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60), 'FM00') AS gender_era
FROM
customer
LIMIT 10;

S-058: 顧客データ（customer）の性別コード（gender_cd）をダミー変数化し、顧客ID（customer_id）とともに10件表示せよ。

SELECT
customer_id,
CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS male,
CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS female,
CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS unknown
FROM customer
LIMIT 10;

S-059: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を平均0、標準偏差1に標準化して顧客ID、売上金額合計とともに10件表示せよ。標準化に使用する標準偏差は、分散の平方根、もしくは不偏分散の平方根のどちらでも良いものとする。ただし、顧客IDが\"Z\"から始まるのものは非会員を表すため、除外して計算すること。

WITH sales_amount AS(
-- 合計金額を求めます
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
-- 売上の平均金額と、標準偏差を求めます
stats_amount AS (
    SELECT
    AVG(sum_amount) AS avg_amount,
    STDDEV_POP(sum_amount) AS stddev_amount
    FROM sales_amount
)
-- 標準化された値を求めます
SELECT
customer_id,
sum_amount,
(sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10;

どう取得するかより、問題文が難しかったですね……
標準偏差を求めるときに「STDDEV_POP」を使っていますが、「STDDEV_SAMP」でも求められます。
結果に差分があるらしいのですが、こちらは統計学の範囲になるので割愛します。

S-060: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を最小値0、最大値1に正規化して顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが\"Z\"から始まるのものは非会員を表すため、除外して計算すること。

WITH sales_amount AS(
-- 合計金額を求めます
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
-- 売上金額の最小値と最大値を求めます
stats_amount AS (
    SELECT
        MAX(sum_amount) AS max_amount,
        MIN(sum_amount) AS min_amount
    FROM
        sales_amount
)
-- -- 標準化された値を求めます
SELECT
    customer_id,
    sum_amount,
    1.0 * (sum_amount - min_amount)
            / (max_amount -  min_amount) AS scale_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10;

まとめ

今回はSQLがというより、問題文が難しいものが多かったです……
問題文が難しい場合でも、取得するための関数はあるので、使いこなしていきたいです。