SQLデータサイエンス100本ノック(記録)81~100

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


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;
```
# 問８２　
S-082: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たなproduct_3を作成せよ。なお、中央値について1円未満は四捨五入とする。補完実施後、各項目について欠損が生じていないことも確認すること。
```php 
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
```
# 問８３　
S-083: 単価（unit_price）と原価（unit_cost）の欠損値について、各商品の小区分（category_small_cd）ごとに算出した中央値で補完した新たなproduct_4を作成せよ。なお、中央値について1円未満は四捨五入とする。補完実施後、各項目について欠損が生じていないことも確認すること。
```php
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
```
# 問８４　
S-084: 顧客テーブル（customer）の全顧客に対し、全期間の売上金額に占める2019年売上金額の割合を計算せよ。ただし、販売実績のない場合は0として扱うこと。そして計算した割合が0超のものを抽出せよ。 結果は10件表示させれば良い。
```php 
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;
```
# 問８５　
S-085: 顧客テーブル（customer）の全顧客に対し、郵便番号（postal_cd）を用いて経度緯度変換用テーブル（geocode）を紐付け、新たなcustomer_1を作成せよ。ただし、複数紐づく場合は経度（longitude）、緯度（latitude）それぞれ平均を算出すること。
```php 
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)
);
```
# 問８６　
S-086: 前設問で作成した緯度経度つき顧客テーブル（customer_1）に対し、申込み店舗コード（application_store_cd）をキーに店舗テーブル（store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客の緯度・経度を用いて距離（km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示すれば良い。
```php 
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
```
# 問８７　
S-087: 顧客テーブル（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客テーブル（customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。
```php
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
```
# 問８８　
S-088: 前設問で作成したデータを元に、顧客テーブルに統合名寄IDを付与したテーブル（customer_n）を作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。
・重複していない顧客：顧客ID（customer_id）を設定
・重複している顧客：前設問で抽出したレコードの顧客IDを設定
```php 
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
```
# 問８９　
S-089: 売上実績のある顧客に対し、予測モデル構築のため学習用データとテスト用データに分割したい。それぞれ8:2の割合でランダムに分割し、テーブルを作成せよ。
```php 
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
;
```
# 問９０　
S-090: レシート明細テーブル（receipt）は2017年1月1日〜2019年10月31日までのデータを有している。売上金額（amount）を月次で集計し、学習用に12ヶ月、テスト用に6ヶ月のモデル構築用データを3テーブルとしてセット作成せよ。データの持ち方は自由とする。
```php
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
);

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
```
# 問９１　
S-091: 顧客テーブル（customer）の各顧客に対し、売上実績のある顧客数と売上実績のない顧客数が1:1となるようにアンダーサンプリングで抽出せよ。
```php 
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;
```
# 問９２　
S-092: 顧客テーブル（customer）では、性別に関する情報が非正規化の状態で保持されている。これを第三正規化せよ。
```php 
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
```
# 問９３　
S-093: 商品テーブル（product）では各カテゴリのコード値だけを保有し、カテゴリ名は保有していない。カテゴリテーブル（category）と組み合わせて非正規化し、カテゴリ名を保有した新たな商品テーブルを作成せよ。
```php 
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;
```
# 問９４　
S-094: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。
・ファイル形式はCSV（カンマ区切り）
・ヘッダ有り
・文字コードはUTF-8
```php 
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv' WITH CSV HEADER encoding 'UTF-8'
```
# 問９５　
S-095: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。
・ファイル形式はCSV（カンマ区切り）
・ヘッダ有り
・文字コードはSJIS
```php 
COPY product_full TO '/tmp/data/S_product_full_SJIS_header.csv' WITH CSV HEADER encoding 'SJIS'
```
# 問９６　
S-096: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。
・ファイル形式はCSV（カンマ区切り）
・ヘッダ無し
・文字コードはUTF-8
```php 
COPY product_full TO '/tmp/data/S_product_full_UTF-8_noh.csv' WITH CSV encoding 'UTF-8'
```
# 問９７　
S-097: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭3件を表示させ、正しくとりまれていることを確認せよ。
・ファイル形式はCSV（カンマ区切り）
・ヘッダ有り
・文字コードはUTF-8
```php 
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
```
# 問９８　
S-098: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭3件を表示させ、正しくとりまれていることを確認せよ。
・ファイル形式はCSV（カンマ区切り）
・ヘッダ無し
・文字コードはUTF-8
```php 
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_noh.csv' WITH CSV encoding 'UTF-8'

SELECT * FROM product_full LIMIT 3　
```
# 問９９　
S-099: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。
・ファイル形式はTSV（タブ区切り）
・ヘッダ有り
・文字コードはUTF-8
```php 
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv' WITH CSV HEADER DELIMITER E'\t' encoding 'UTF-8'
```
# 問１００　
S-100: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。また、先頭10件を表示させ、正しくとりまれていることを確認せよ。
・ファイル形式はTSV（タブ区切り）
・ヘッダ有り
・文字コードはUTF-8
```php 
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
```