【SQL編】データサイエンス100本ノック解説【41~60】

始めに

[21~40]の解説はこちら(https://qiita.com/Strix9289/items/a7b813ade174ebb27409)
[60~80]お解説はこちら(https://qiita.com/Strix9289/items/846a430d0885373898fb)

#結合[36~42]

41【n件前のデータとの結合】

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

%%sql
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;

1. 「amountを日付ごとに集計する」をWITH句でまとめる
2. WITH句で作ったテーブルとそのテーブルを一つずらしたテーブルと、差分を計算したカラムを作る

• LAG(カラム, 値) OVER(ORDER BY カラム)：整列させたカラムを値分だけずらす

42【過去n件のデータとの結合】

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

%%sql
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;

1. 売上金額を日付ごとに集計したテーブルをWITH句で作る
2. 上で作ったテーブルを1つずつずらしたものと、2つずらしたものを合わせたテーブルを別にWITH句でつくる。

縦横変換

43【縦から横に変換】

S-043: レシート明細テーブル（receipt）と顧客テーブル（customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリテーブル（sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。

ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

%%sql
-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）

-- これから作るが既にある場合、sales_summaryを事前に削除する
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;

• DROP TABLE IF EXISTS テーブル名：指定されたテーブルを削除する
• CREATE TABLE テーブル名 AS(selectなど)：新たなテーブルを作る
• TRANK：数値の小数点以下を切り捨てた整数を返す
• CASE WHEN 条件式1 THEN 返り値1 [WHEN 条件式2 THEN 返り値2] [ELSE 返り値] END：場合分けをする

44【横から縦に変換】

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

%%sql
-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）
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

• テーブル1 UNION [ALL] テーブル2：複数のテーブルの和集合を取る。ALLを指定することで

データ変換[45~58]

45【日付型から文字データに変換】

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

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

• TO_CHAR(値, '書式')：指定された値を指定された書式の文字データに変換
• 今回は、日付型のカラムを文字データのYYYYMMDD形式に変換している
• 詳細()

46【文字データを日付型に変換】

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

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

• TO_DATE(値, 書式)：指定された値を指定された形式の日付型に変換
• 今回は、文字データのカラムをYYYYMMDD形式の日付型に変換
• 詳細()

47【数値データを日付型に変換】

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

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

• CAST(値 AS 書式)：指定された値を指定された型に変換
• VARCHAR：可変調文字列型
• 今回は、まず、YYYYMMDD形式の数値型を文字型に直してから、日付型に変換する

48【UNIX時間を日付型に変換】

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

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

• TO_TIMESTAMP(値)；TIMESTAMP型に変換
• 詳細はこちら()

49【日付要素の取り出し（年）】

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

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

• EXTRACT(日付要素 FROM 元の日付要素)：指定した要素を取り出す
• 'FM'：書式の幅になるまで先頭を0又は末尾を空白で埋める。
• '9'：数値。正の場合は先頭に空白が付き、負の場合はマイナス記号(-)が付く。
• 詳細はこちら(http://itref.fc2web.com/oracle/function/to_char.html)

50【日付要素の取り出し（月）】

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

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

• '0'：数値。正の場合は先頭に空白が付き、負の場合はマイナス記号(-)が付く。桁数に足りない分は0で埋められる。

51【日付要素の取り出し（日）】

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

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

52【数値データを⼆値(0/1)データに変換する】

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

%sql
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;

• CASE WHEN 条件式1 THEN 返り値1 [WHEN 条件式2 THEN 返り値2] [ELSE 返り値] END：場合分けをする

53【⽂字データを⼆値(0/1)データに変換する】

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

%%sql
-- 東京に1, それ以外を0とするpostal_flgというカラムを持つcustを作る
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
)
-- 2値化されたそれぞれの行についてカウント
SELECT c.postal_flg, COUNT(1)
FROM rect r
JOIN cust c
ON r.customer_id = c.customer_id
GROUP BY c.postal_flg;

• SUBSTR(文字列, 開始位置, 切り取り文字数)：文字列を切り取る（開始位置は1から始める）

54【カテゴリ化】

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

%%sql
-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）
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;

• SUBSTR(文字列, 開始位置, 切り取り文字数)：文字列を切り取る（開始位置は1から始める）

55【カテゴリ化】

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

• 最小値以上第一四分位未満
• 第一四分位以上第二四分位未満
• 第二四分位以上第三四分位未満
• 第三四分位以上

%%sql
-- 売上金額の合計を計算する
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

56【カテゴリ化(件数の少ないカテゴリを寄せる)】

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

%%sql
SELECT
    customer_id,
    birth_day,
　　-- 年齢の10の位を取り出し、INTEGER型に変換。そして、都の最小値を取る  
  　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(値1, 値2,...)：引数の最小値を取る

57【カテゴリ化(カテゴリ同士の組み合わせ)】

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

%%sql
SELECT
    customer_id,
    birth_day,
    -- 例えば、160なら女性で、60歳以上
    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;

• '||'： 文字列データに任意の文字列を連結したり、複数の文字列データを連結する

58【ダミー変数化】

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

%%sql
-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）
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;

数値化[60~62]

59【正規化(z-score)】

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

%%sql
-- (1)：売上金額の合計を計算＆'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
),
-- (2)：平均0、分散1に正規化
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;

60【正規化(Min=0, Max=1)】

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

%%sql
-- 売上金額の合計を計算＆'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,
    -- 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

• 詳細(https://mathwords.net/dataseikika)

[60~80]解説はこちら(https://qiita.com/Strix9289/items/846a430d0885373898fb)