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

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

前回の記事

tech.arms-soft.co.jp

S-041: レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前回売上があった日からの売上金額増減を計算せよ。そして結果を10件表示せよ。

WITH receipt AS (
    SELECT sales_ymd, SUM(amount)AS sum_amount
    FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
    ) 

SELECT sales_ymd, sum_amount - LAG(sum_amount, 1) OVER(ORDER BY sales_ymd) AS diff_amount
FROM receipt
LIMIT 10;

LAG()は、指定した行の前の値を取得できます。
副問合せで、日付ごとの合計売上金額を取得し、SELECT文で、日付ごとの合計売上金額 - 一日前の合計売上金額を計算することで、売上金額増減を求めています。

S-042: レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、前回、前々回、3回前に売上があった日のデータを結合せよ。そして結果を10件表示せよ。

WITH receipt AS (
    SELECT sales_ymd, SUM(amount)AS sum_amount
    FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
    ) 

SELECT
    sales_ymd,
    LAG(sum_amount, 1) OVER(ORDER BY sales_ymd) AS amount1,
    LAG(sum_amount, 2) OVER(ORDER BY sales_ymd) AS amount2,
    LAG(sum_amount, 3) OVER(ORDER BY sales_ymd) AS amount3
FROM receipt
LIMIT 10;

S-043: レシート明細データ（receipt）と顧客データ（customer）を結合し、性別コード（gender_cd）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリデータを作成せよ。性別コードは0が男性、1が女性、9が不明を表すものとする。ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

WITH gender_age_amount AS (
SELECT sum(amount) AS sum_amount,gender_cd,
CASE
WHEN age < 10 THEN 'under10'
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 AND age < 70 THEN '60代'
WHEN age >= 70 AND age < 80 THEN '70代'
WHEN age >= 80 AND age < 90 THEN '80代'
WHEN age >= 90 THEN '90代'
ELSE 'other'
END AS format_age
FROM receipt
JOIN customer
ON receipt.customer_id = customer.customer_id
GROUP BY format_age,gender_cd
)
SELECT
format_age AS 年代,
SUM(CASE WHEN gender_cd = '1' THEN sum_amount END) AS 女性の売上金額,
SUM(CASE WHEN gender_cd = '0' THEN sum_amount END) AS 男性の売上金額,
SUM(CASE WHEN gender_cd = '9' THEN sum_amount END) AS 性別不明の売上金額
FROM gender_age_amount
GROUP BY format_age
ORDER BY format_age;

副問合せで、年代、性別ごとの売上金額を取得しています。年代ごとのCASE文は、もう少しシンプルに書けると思うのですが。。。
SELECT文で、副問合せで取得した年代、性別ごとの売上金額を、それぞれ性別ごとに取得します。

S-044: 043で作成した売上サマリデータ（sales_summary）は性別の売上を横持ちさせたものであった。このデータから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を\"00\"、女性を\"01\"、不明を\"99\"とする。

この問題は、S-043で取得したデータを使用するようです。上で書いた状態では、この問題は解けないようなので、少し内容を書き換えます。

CREATE TABLE sales_summary AS
    WITH gender_age_amount AS (
        SELECT sum(amount) AS sum_amount,gender_cd,
        CASE
        WHEN age < 10 THEN 'under10'
        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 AND age < 70 THEN '60代'
        WHEN age >= 70 AND age < 80 THEN '70代'
        WHEN age >= 80 AND age < 90 THEN '80代'
        WHEN age >= 90 THEN '90代'
        ELSE 'other'
        END AS format_age
        FROM receipt
        JOIN customer
        ON receipt.customer_id = customer.customer_id
        GROUP BY format_age,gender_cd
        )
    SELECT
        format_age,
        SUM(CASE WHEN gender_cd = '1' THEN sum_amount END) AS 女性の売上金額,
        SUM(CASE WHEN gender_cd = '0' THEN sum_amount END) AS 男性の売上金額,
        SUM(CASE WHEN gender_cd = '9' THEN sum_amount END) AS 性別不明の売上金額
        
        FROM gender_age_amount
    GROUP BY
        format_age
    ORDER BY
        format_age;

    SELECT
        *
    FROM sales_summary;

こうすることで、S-043で取得したデータを、一つのテーブルとして持つことができます。

その上で

SELECT era, '00' AS gender_cd , 女性の売上金額 AS amount FROM sales_summary
UNION ALL
SELECT era, '01' AS gender_cd, 男性の売上金額 AS amount FROM sales_summary
UNION ALL
SELECT era, '99' AS gender_cd, 性別不明の売上金額 AS amount FROM sales_summary;

UNION ALLすることで、S-043では横に持っていた性別のデータを縦に持つことができました。

S-045: 顧客データ（customer）の生年月日（birth_day）は日付型でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに10件表示せよ。

SELECT
    customer_id, 
    TO_CHAR(birth_day, 'YYYYMMDD') AS birth_day
FROM customer 
LIMIT 10

まとめ

今回はS-043、S-044の問題が難しかったです。
もう少しスッキリ取得できるような気もするので、引き続き勉強していきます。