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

欠損値[79~83]

81【平均値で補完】

S-081: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たなproduct_2を作成せよ。なお、平均値について1円未満は四捨五入とする。補完実施後、各項目について欠損が生じていないことも確認すること。

%%sql
-- もしproduct_2がすでに存在していれば削除しておく
DROP TABLE IF EXISTS product_2;


CREATE TABLE product_2 AS (
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_avg) as unit_price,
        COALESCE(unit_cost, cost_avg) as unit_cost
    FROM
        product
    CROSS JOIN (
        SELECT
            ROUND(AVG(unit_price)) AS unit_avg,
            ROUND(AVG(unit_cost)) AS cost_avg
        FROM
            product
    ) stats_product
)

%%sql

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_2 LIMIT 10;

• COALESCE(カラム, 値)：カラム中のNULLの位置を指定した値で補完する
• FROM テーブル1 CROSS JOIN テーブル2：すべてのテーブルの行とすべてのテーブルの列で組み合わせたてぶーるを作成する

82【中央値で補完】

S-082: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たなproduct_3を作成せよ。なお、中央値について1円未満は四捨五入とする。補完実施後、各項目について欠損が生じていないことも確認すること。

%%sql
DROP TABLE IF EXISTS product_3;
CREATE TABLE product_3 AS (
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_med) as unit_price,
        COALESCE(unit_cost, cost_med) as unit_cost
    FROM
        product
    CROSS JOIN (
        SELECT
            ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)) AS unit_med,
            ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)) AS cost_med
        FROM
            product
    ) stats_product
)

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_3 LIMIT 10

83【カテゴリごとの中央値で補完】

S-083: 単価（unit_price）と原価（unit_cost）の欠損値について、各商品の小区分（category_small_cd）ごとに算出した中央値で補完した新たなproduct_4を作成せよ。なお、中央値について1円未満は四捨五入とする。補完実施後、各項目について欠損が生じていないことも確認すること。

%%sql
DROP TABLE IF EXISTS product_4;
CREATE TABLE product_4 AS (
    WITH category_median AS(
        SELECT
            category_small_cd, 
            ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)) AS unit_med,
            ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)) AS cost_med
        FROM product
        GROUP BY category_small_cd
    )
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_med) as unit_price,
        COALESCE(unit_cost, cost_med) as unit_cost
    FROM
        product
    JOIN
        category_median
    USING(category_small_cd)
)

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_4 LIMIT 10

84【除算エラーを0で代替】

S-084: 顧客テーブル（customer）の全顧客に対し、全期間の売上金額に占める2019年売上金額の割合を計算せよ。ただし、販売実績のない場合は0として扱うこと。そして計算した割合が0超のものを抽出せよ。 結果は10件表示させれば良い。

%%sql
WITH sales_amount_2019 AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount_2019
    FROM
        receipt
    WHERE
        20190101 <= sales_ymd AND sales_ymd <= 20191231
    GROUP BY
        customer_id
),
sales_amount_all AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount_all
    FROM
        receipt
    GROUP BY
        customer_id
)
SELECT
    a.customer_id,
    COALESCE(b.sum_amount_2019, 0) AS sales_amount_2019,
    COALESCE(c.sum_amount_all, 0)  AS sales_amount_all,
    CASE COALESCE(c.sum_amount_all, 0)  WHEN 0 THEN 0 ELSE COALESCE(b.sum_amount_2019, 0) * 1.0 / c.sum_amount_all END AS sales_rate
FROM
    customer a
LEFT JOIN
    sales_amount_2019 b
ON a.customer_id = b.customer_id
LEFT JOIN
    sales_amount_all c
ON a.customer_id = c.customer_id
WHERE CASE COALESCE(c.sum_amount_all, 0)  WHEN 0 THEN 0 ELSE COALESCE(b.sum_amount_2019, 0) * 1.0 / c.sum_amount_all END > 0
LIMIT 10;

座標データ[85~86]

85【郵便番号をジオコードに変換】

S-085: 顧客テーブル（customer）の全顧客に対し、郵便番号（postal_cd）を用いて経度緯度変換用テーブル（geocode）を紐付け、新たなcustomer_1を作成せよ。ただし、複数紐づく場合は経度（longitude）、緯度（latitude）それぞれ平均を算出すること。

%%sql

DROP TABLE IF EXISTS customer_1;

CREATE TABLE customer_1 AS (
    WITH geocode_avg AS(
        SELECT
            postal_cd,
            AVG(longitude) as m_longitude,
            AVG(latitude) as m_latitude
        FROM
            geocode
        GROUP BY 
            postal_cd
    )
    SELECT 
        *
    FROM
        customer c
    JOIN
        geocode_avg g
    USING(postal_cd)
);

86【緯度経度から距離を計算】

S-086: 前設問で作成した緯度経度つき顧客テーブル（customer_1）に対し、申込み店舗コード（application_store_cd）をキーに店舗テーブル（store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客の緯度・経度を用いて距離（km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示すれば良い。
$$
緯度（ラジアン）：\phi \
経度（ラジアン）：\lambda \
距離L = 6371 * arccos(sin \phi_1 * sin \phi_2

• cos \phi_1 * cos \phi_2 * cos(\lambda_1 − \lambda_2))
  $$

%%sql
SELECT
    c.customer_id,
    c.address AS customer_address,
    s.address AS store_address,
    (
        6371 * ACOS( 
            SIN(RADIANS(c.m_latitude)) * SIN(RADIANS(s.latitude)) + 
            COS(RADIANS(c.m_latitude)) * COS(RADIANS(s.latitude)) * COS(RADIANS(c.m_longitude) - RADIANS(s.longitude))
        )
    ) AS distance 
FROM
    customer_1 c
JOIN
    store s
ON
    c.application_store_cd = s.store_cd
limit 10

名寄せ[87~88]

87【完全⼀致】

S-087: 顧客テーブル（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客テーブル（customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。

'''
%%sql

DROP TABLE IF EXISTS customer_u;
CREATE TABLE customer_u AS (
WITH sales_amount AS(
SELECT
c.customer_id,
c.customer_name,
c.postal_cd,
SUM(r.amount) as sum_amount
FROM
customer c

LEFT JOIN
    receipt r        
ON c.customer_id = r.customer_id
GROUP by
    c.customer_id, c.customer_name, c.postal_cd
),
sales_ranking AS(
SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY customer_name, postal_cd ORDER BY sum_amount desc, customer_ID ) as rank
FROM sales_amount
)
SELECT c.*
FROM
    customer c
JOIN
    sales_ranking r
ON
    c.customer_id = r.customer_id
    and r.rank = 1

)
SELECT cnt, cnt_u, cnt - cnt_u AS diff FROM
(SELECT count(1) as cnt FROM customer) customer
CROSS JOIN (SELECT count(1) as cnt_u FROM customer_u) customer_u;
'''

88【変換データ作成】

S-088: 前設問で作成したデータを元に、顧客テーブルに統合名寄IDを付与したテーブル（customer_n）を作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。

• 重複していない顧客：顧客ID（customer_id）を設定
• 重複している顧客：前設問で抽出したレコードの顧客IDを設定

%%sql

DROP TABLE IF EXISTS customer_n;
CREATE TABLE customer_n AS (
    SELECT 
        c.*, 
        u.customer_id as integration_id
    FROM 
        customer c
    JOIN
        customer_u u
    ON c.customer_name = u.customer_name
        and c.postal_cd = u.postal_cd
)
SELECT count(1) FROM customer_n
WHERE customer_id != integration_id

データ分割

89【ホールドアウト法】

S-089: 売上実績のある顧客に対し、予測モデル構築のため学習用データとテスト用データに分割したい。それぞれ8:2の割合でランダムに分割し、テーブルを作成せよ。

%%sql

SELECT SETSEED(0.1);

CREATE TEMP TABLE IF NOT EXISTS sales_record_customer_id AS (
    SELECT customer_id ,ROW_NUMBER()OVER(ORDER BY RANDOM()) AS row
    FROM customer
    LEFT JOIN receipt USING(customer_id)
    GROUP BY customer_id
    HAVING SUM(amount) IS NOT NULL 
);

DROP TABLE IF EXISTS customer_train;
CREATE TABLE customer_train AS
SELECT customer.*
FROM sales_record_customer_id
LEFT JOIN customer USING(customer_id)
WHERE sales_record_customer_id.row < (SELECT COUNT(0) FROM sales_record_customer_id) *0.8
;

DROP TABLE IF EXISTS customer_test;
CREATE TABLE customer_test AS
SELECT customer.* FROM sales_record_customer_id LEFT JOIN customer USING(customer_id)
EXCEPT
SELECT * from customer_train
;

90【時系列データを分割する】工事中

S-090: レシート明細テーブル（receipt）は2017年1月1日〜2019年10月31日までのデータを有している。売上金額（amount）を月次で集計し、学習用に12ヶ月、テスト用に6ヶ月のモデル構築用データを3テーブルとしてセット作成せよ。データの持ち方は自由とする。

%%sql
-- SQL向きではないため、やや強引に記載する（分割数が多くなる場合はSQLが長くなるため現実的ではない）
-- また、秒単位のデータなど時系列が細かく、かつ長期間に渡る場合はデータが膨大となるため注意（そのようなケースではループ処理でモデル学習ができる言語が望ましい）
-- 学習データ(0)とテストデータ(1)を区別するフラグを付与する
DROP TABLE IF EXISTS sales_amount ;

CREATE TABLE sales_amount AS (
    SELECT 
        SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym, 
        SUM(amount) AS sum_amount,
        row_number() OVER(PARTITION BY NULL ORDER BY 
                         SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
    FROM
        receipt
    GROUP BY sales_ym
);

-- SQLでは限界があるが、作成データセットの増加に伴いなるべく使いまわしができるものにする
-- WITH句内のLAG関数について、ラグ期間を変えれば使い回せるよう記述
DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
    WITH lag_amount AS (
        SELECT sales_ym, sum_amount, LAG(rn, 0) OVER (ORDER BY rn) AS rn
        FROM sales_amount
    )
    SELECT sales_ym, sum_amount, CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount WHERE rn <= 18);

DROP TABLE IF EXISTS series_data_2 ;

CREATE TABLE series_data_2 AS (
    WITH lag_amount AS (
        SELECT sales_ym, sum_amount, LAG(rn, 6) OVER (ORDER BY rn) AS rn
        FROM sales_amount
    )
    SELECT sales_ym, sum_amount, CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount WHERE rn <= 18);

DROP TABLE IF EXISTS series_data_3 ;

CREATE TABLE series_data_3 AS (
    WITH lag_amount AS (
        SELECT sales_ym, sum_amount, LAG(rn, 12) OVER (ORDER BY rn) AS rn
        FROM sales_amount
    )
    SELECT sales_ym, sum_amount, CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount WHERE rn <= 18)

SELECT * FROM series_data_1

91【アンダーサンプリング】

S-091: 顧客テーブル（customer）の各顧客に対し、売上実績のある顧客数と売上実績のない顧客数が1:1となるようにアンダーサンプリングで抽出せよ。

%%sql

SELECT SETSEED(0.1);

WITH pre_table_1 AS(
    SELECT
        c.*
        ,COALESCE(r.amount,0) AS r_amount
    FROM
        customer c
    LEFT JOIN
        receipt r
    ON
        c.customer_id=r.customer_id
)
,pre_table_2 AS(
    SELECT
        customer_id
        ,CASE WHEN SUM(r_amount)>0 THEN 1 ELSE 0 END  AS is_buy_flag
        ,CASE WHEN SUM(r_amount)=0 THEN 1 ELSE 0 END  AS is_not_buy_flag
    FROM
        pre_table_1
    GROUP BY
        customer_id
)
,pre_table_3 AS(
    SELECT
        *
        ,ROW_NUMBER() OVER(PARTITION BY is_buy_flag ORDER BY RANDOM())
    FROM
        pre_table_2
    CROSS JOIN
        (SELECT SUM(is_buy_flag) AS buying FROM pre_table_2) AS t1
    CROSS JOIN
        (SELECT SUM(is_not_buy_flag) AS not_buying FROM pre_table_2) AS t2
)
,pre_table_4 AS(
    SELECT
        *
    FROM
        pre_table_3
    WHERE
        row_number<=buying
        AND
        row_number<=not_buying
)

SELECT COUNT(*) FROM pre_table_4 GROUP BY is_buy_flag;

正規化[92~94]

92【正規化】

S-092: 顧客テーブル（customer）では、性別に関する情報が非正規化の状態で保持されている。これを第三正規化せよ。

%%sql
DROP TABLE IF EXISTS customer_std;
CREATE TABLE customer_std AS (
    SELECT
        customer_id,
        customer_name,
        gender_cd,
        birth_day,
        age,
        postal_cd,
        application_store_cd,
        application_date,
        status_cd
    FROM
        customer
);

DROP TABLE IF EXISTS gender_std;
CREATE TABLE gender_std AS (
    SELECT distinct
        gender_cd, gender
    FROM
        customer
)
SELECT * FROM gender_std

93【非正規化】

S-093: 商品テーブル（product）では各カテゴリのコード値だけを保有し、カテゴリ名は保有していない。カテゴリテーブル（category）と組み合わせて非正規化し、カテゴリ名を保有した新たな商品テーブルを作成せよ。

%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full AS (
    SELECT
        p.product_cd,
        p.category_major_cd,
        c.category_major_name,
        p.category_medium_cd,
        c.category_medium_name,
        p.category_small_cd,
        c.category_small_name,
        p.unit_price,
        p.unit_cost
    FROM
        product p
    JOIN
        category c
    USING(category_small_cd)
)
SELECT * FROM product_full LIMIT 10

ファイル入出力[94~100]

94【CSV出力(コード変化なし)】

S-094: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。

• ファイル形式はCSV（カンマ区切り）
• ヘッダ有り
• 文字コードはUTF-8

%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv' WITH CSV HEADER encoding 'UTF-8'

• COPY テーブル名 TO 'ファイル名'
• 詳細(https://www.postgresql.jp/document/9.3/html/sql-copy.html)

95【CSV出力(コード変化あり)】

S-095: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。

• ファイル形式はCSV（カンマ区切り）
• ヘッダ有り
• 文字コードはSJIS

%%sql
COPY product_full TO '/tmp/data/S_product_full_SJIS_header.csv' WITH CSV HEADER encoding 'SJIS'

96【CSV出力(ヘッダなし、コード変換なし)】

S-096: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。

• ファイル形式はCSV（カンマ区切り）
• ヘッダ無し
• 文字コードはUTF-8

%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_noh.csv' WITH CSV encoding 'UTF-8'

97【CSV⼊⼒（ヘッダ有り、コード変換なし）】

S-097: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭3件を表示させ、正しくとりまれていることを確認せよ。

• ファイル形式はCSV（カンマ区切り）
• ヘッダ有り
• 文字コードはUTF-8

%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.csv' WITH CSV HEADER encoding 'UTF-8'

SELECT * FROM product_full LIMIT 3

98【CSV⼊⼒（ヘッダ無し、コード変換なし）】

S-098: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭3件を表示させ、正しくとりまれていることを確認せよ。

• ファイル形式はCSV（カンマ区切り）
• ヘッダ無し
• 文字コードはUTF-8

%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_noh.csv' WITH CSV encoding 'UTF-8'

SELECT * FROM product_full LIMIT 3

99【TSV出⼒（ヘッダ有り、コード変換なし）】

S-099: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。

• ファイル形式はTSV（タブ区切り）
• ヘッダ有り
• 文字コードはUTF-8

%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv' WITH CSV HEADER DELIMITER E'\t' encoding 'UTF-8'

• TSV (Tab Separated Values)データ同士がスペースで分割されている
• DELIMITER：区切り文字の指定

100【TSV⼊⼒（ヘッダ有り、コード変換なし）】

S-100: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭10件を表示させ、正しくとりまれていることを確認せよ。

• ファイル形式はTSV（タブ区切り）
• ヘッダ有り
• 文字コードはUTF-8

%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.tsv' WITH CSV HEADER DELIMITER E'\t' encoding 'UTF-8'

SELECT * FROM product_full LIMIT 3