SQLデータサイエンス100本ノック(記録)41〜60

問４１　

S-041: レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前日からの売上金額増減を計算せよ。なお、計算結果は10件表示すればよい。

WITH sales_amount_by_date AS(
    SELECT sales_ymd, SUM(amount) AS amount
    FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
)
SELECT sales_ymd, LAG(sales_ymd, 1) OVER(ORDER BY sales_ymd) AS lag_ymd, amount,
    LAG(amount, 1) OVER(ORDER BY sales_ymd) AS lag_amount,
    amount - LAG(amount, 1) OVER(ORDER BY sales_ymd) AS diff_amount
FROM sales_amount_by_date
LIMIT 10;

問４２　

S-042: レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、１日前、２日前、３日前のデータを結合せよ。結果は10件表示すればよい。

WITH sales_amount_by_date AS(
    SELECT sales_ymd, SUM(amount) AS amount
    FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
),sales_amount_with_lag AS(
SELECT sales_ymd, amount,
    LAG(sales_ymd,1) OVER (ORDER BY sales_ymd) AS lag_ymd_1,
    LAG(amount,1) OVER (ORDER BY sales_ymd) AS lag_amount_1,
    LAG(sales_ymd,2) OVER (ORDER BY sales_ymd) AS lag_ymd_2,
    LAG(amount,2) OVER (ORDER BY sales_ymd) AS lag_amount_2,
    LAG(sales_ymd,3) OVER (ORDER BY sales_ymd) AS lag_ymd_3,
    LAG(amount,3) OVER (ORDER BY sales_ymd) AS lag_amount_3
FROM sales_amount_by_date
)
SELECT * FROM sales_amount_with_lag
/*3つずらしたのでNULLが表示されないようにする*/
WHERE lag_ymd_3 IS NOT NULL
ORDER BY sales_ymd
LIMIT 10;

問４３　

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

DROP TABLE IF EXISTS sales_summary;

CREATE TABLE sales_summary AS
    WITH gender_era_amount AS (
        SELECT c.gender_cd,
        TRUNC(age/ 10) * 10 AS era,
        SUM(r.amount) AS amount
        FROM customer c
        JOIN receipt r
        ON c.customer_id = r.customer_id
        GROUP BY c.gender_cd, era
    )
    select era,
        MAX(CASE gender_cd WHEN '0' THEN amount ELSE 0 END) AS male ,
        MAX(CASE gender_cd WHEN '1' THEN amount ELSE 0 END) AS female,
        MAX(CASE gender_cd WHEN '9' THEN amount ELSE 0 END) AS unknown
    FROM gender_era_amount
    GROUP BY era
    ORDER BY era

SELECT * FROM sales_summary;

問４４　

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

SELECT era, '00' as gender_cd , male AS amount FROM sales_summary
UNION ALL
SELECT era, '01' as gender_cd, female AS amount FROM sales_summary
UNION ALL
SELECT era, '99' as gender_cd, unknown AS amount FROM sales_summary

問４５　

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

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

問４６　

S-046: 顧客テーブル（customer）の申し込み日（application_date）はYYYYMMDD形式の文字列型でデータを保有している。これを日付型（dateやdatetime）に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。

SELECT customer_id, TO_DATE(application_date, 'YYYYMMDD') from customer LIMIT 10;

問４７　

S-047: レシート明細テーブル（receipt）の売上日（sales_ymd）はYYYYMMDD形式の数値型でデータを保有している。これを日付型（dateやdatetime）に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

SELECT TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD'),
receipt_no, receipt_sub_no FROM receipt LIMIT 10;

問４８　

S-048: レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）は数値型のUNIX秒でデータを保有している。これを日付型（timestamp型）に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

SELECT TO_TIMESTAMP(sales_epoch) AS sales_date, receipt_no, 
receipt_sub_no FROM receipt LIMIT 10;

問４９　

S-049: レシート明細テーブル（receipt）の販売エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"年"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

SELECT TO_CHAR(EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)), 'FM9999') 
AS sales_year, receipt_no, receipt_sub_no
FROM receipt LIMIT 10;

問５０　

S-050: レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"月"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、"月"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

SELECT 
    TO_CHAR(EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)), 'FM00') 
AS sales_month, receipt_no, receipt_sub_no
FROM receipt LIMIT 10;

問５１　

S-051: レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"日"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、"日"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

SELECT TO_CHAR(EXTRACT(DAY FROM TO_TIMESTAMP(sales_epoch)), 'FM00') 
AS sales_day, receipt_no, receipt_sub_no 
FROM receipt LIMIT 10;

問５２　

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

SELECT
    customer_id,
    SUM(amount) AS sum_amount,
    CASE
        WHEN SUM(amount)  > 2000 THEN 1
        WHEN SUM(amount) <= 2000 THEN 0
    END AS 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）と結合し、全期間において買い物実績のある顧客数を、作成した2値ごとにカウントせよ。

WITH cust 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
),
rect AS(
    SELECT customer_id, SUM(amount)
    FROM receipt
    GROUP BY customer_id
)
SELECT c.postal_flg, COUNT(1)
FROM rect r
JOIN cust c
ON r.customer_id = c.customer_id
GROUP BY c.postal_flg;

問５４　

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

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

問５５　

S-055: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額合計とともに表示せよ。カテゴリ値は上から順に1〜4とする。結果は10件表示させれば良い。
・最小値以上第一四分位未満
・第一四分位以上第二四分位未満
・第二四分位以上第三四分位未満
・第三四分位以上

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) as sum_amount
    FROM
        receipt
    GROUP BY
        customer_id
),
sales_pct AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS pct25,
        PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS pct50,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS pct75
    FROM
        sales_amount
)
SELECT
    a.customer_id,
    a.sum_amount,
    CASE
        WHEN a.sum_amount < pct25 THEN 1
        WHEN pct25 <= a.sum_amount and a.sum_amount < pct50 THEN 2
        WHEN pct50 <= a.sum_amount and a.sum_amount < pct75 THEN 3
        WHEN pct75 <= a.sum_amount THEN 4
    END AS pct_flg
FROM sales_amount a
CROSS JOIN sales_pct p
LIMIT 10

問５６　

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

SELECT
    customer_id,
    birth_day, 
    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

問５７　

S-057: 前問題の抽出結果と性別（gender）を組み合わせ、新たに性別×年代の組み合わせを表すカテゴリデータを作成せよ。組み合わせを表すカテゴリの値は任意とする。先頭10件を表示させればよい

SELECT
    customer_id,
    birth_day,
    gender_cd || 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;

問５８　

S-058: 顧客テーブル（customer）の性別コード（gender_cd）をダミー変数化し、顧客ID（customer_id）とともに抽出せよ。結果は10件表示させれば良い。

SELECT
    customer_id,
    CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS gender_male,
    CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS gender_female,
    CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS gender_unknown
FROM
    customer
LIMIT 10;

問５９　

S-059: レシート明細テーブル（receipt）の(1)売上金額（amount）を顧客ID（customer_id）ごとに合計し、(2)合計した売上金額を平均0、標準偏差1に標準化して顧客ID、売上金額合計とともに表示せよ。標準化に使用する標準偏差は、不偏標準偏差と標本標準偏差のどちらでも良いものとする。(1)ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。

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_samp(sum_amount) AS std_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    (sum_amount - avg_amount) / std_amount AS normal_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10;

問６０　

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

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,
    -- Min-Max-Normalization
    (sum_amount - min_amount) * 1.0 / (max_amount -  min_amount) * 1.0 AS scale_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10