今後の目標およびSQL100本ノック

前書き

8月（もしかして7月）から冒エンジニアスクールにて転職のための勉強に取り組むことにした。

経験値：VBAなどプログラミング多少、ITパスポート（要は未経験）

勉強に際しては、他者との関りや情報発信といった、今までの自分とはあまり縁のないことにも取り組む必要があると認識している。そこで下記のように目標を設定したい。

• スクール代表との面談：1回/月
• スクール講師との面談：1回/週
• スクール講師への質問：1回/日
• Qiitaへの記事投稿：1回/月

手始めに、先行して勉強していたSQL100本ノックのS-081～088までの自分なりの解説というか感想を以下に投稿する。（81以降はネットで解説見つからなかった+89以降はあまり興味出なかった。）

SQL100本ノック解説（S-081-088）

※GoogleColabで実行
※正確性は保証しません

• S-081
  「単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たな商品データを作成せよ。なお、平均値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。」

%%sql
CREATE TABLE product3 AS(
  SELECT * FROM product
);

UPDATE product3 SET unit_price = ROUND((SELECT AVG(unit_price) FROM product))
WHERE unit_price IS NULL;
UPDATE product3 SET unit_cost  = ROUND((SELECT AVG(unit_cost ) FROM product))
WHERE unit_cost  IS NULL;

SELECT
  COUNT(1) - COUNT(product_cd) AS c_pcd,
  COUNT(1) - COUNT(category_major_cd) AS c_cma,
  COUNT(1) - COUNT(category_medium_cd) AS c_cme,
  COUNT(1) - COUNT(category_small_cd) AS c_cs,
  COUNT(1) - COUNT(unit_price) AS c_up,
  COUNT(1) - COUNT(unit_cost) AS c_uc
FROM product3;

UPDATE部分について、こういうのをサブクエリというのかと認識。これまでの経験だとSELECT以下は変数で事前に置いておく感覚だが、SQLの仕様上このようになるのだろう。
AVGの算定はNULLを除外したほうが丁寧か。COUNTの方法は単純にNULLを数えるなど色々ある。

• S-082
  「単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。」

%%sql
CREATE TABLE product4 AS(
  SELECT * FROM product
);

UPDATE product4 SET unit_price = ROUND((SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price ASC) FROM product))
WHERE unit_price IS NULL;
UPDATE product4 SET unit_cost  = ROUND((SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost  ASC) FROM product))
WHERE unit_cost  IS NULL;

SELECT
  COUNT(1) - COUNT(product_cd) AS c_pcd,
  COUNT(1) - COUNT(category_major_cd) AS c_cma,
  COUNT(1) - COUNT(category_medium_cd) AS c_cme,
  COUNT(1) - COUNT(category_small_cd) AS c_cs,
  COUNT(1) - COUNT(unit_price) AS c_up,
  COUNT(1) - COUNT(unit_cost) AS c_uc
FROM product4;

S-081とほぼ同様。

• S-083
  「単価（unit_price）と原価（unit_cost）の欠損値について、各商品のカテゴリ小区分コード（category_small_cd）ごとに算出した中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。」

%%sql
CREATE TABLE product5 AS(
  SELECT * FROM product
);

UPDATE product5 SET unit_price 
  = ROUND((SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price ASC)
    FROM product WHERE category_small_cd = product5.category_small_cd))
WHERE unit_price IS NULL;

UPDATE product5 SET unit_cost  
  = ROUND((SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost  ASC)
    FROM product WHERE category_small_cd = product5.category_small_cd))
WHERE unit_cost  IS NULL;

SELECT
  COUNT(1) - COUNT(product_cd) AS c_pcd,
  COUNT(1) - COUNT(category_major_cd) AS c_cma,
  COUNT(1) - COUNT(category_medium_cd) AS c_cme,
  COUNT(1) - COUNT(category_small_cd) AS c_cs,
  COUNT(1) - COUNT(unit_price) AS c_up,
  COUNT(1) - COUNT(unit_cost) AS c_uc
FROM product5;

FROMの対象とそれ以外の区別と記載方法に慣れる必要がある。
そのままだが、FROM productのクエリ内なので、なにもなければproduct内のデータを示す。それ以外を用いる場合は.で記載する（この場合はproduct5）。

• S-084
  「顧客データ（customer）の全顧客に対して全期間の売上金額に占める2019年売上金額の割合を計算し、新たなデータを作成せよ。ただし、売上実績がない場合は0として扱うこと。そして計算した割合が0超のものを抽出し、結果を10件表示せよ。また、作成したデータに欠損が存在しないことを確認せよ。」

%%sql
-- データ作成
CREATE TABLE customer_2019 AS(
  SELECT customer_id, SUM(amount) AS sum_amount, 
         SUM(CASE WHEN sales_ymd >= 20190000 THEN amount ELSE 0 END) AS sum_amount_2019
  FROM receipt
  GROUP BY customer_id
)

%%sql
-- 欠損確認
SELECT COUNT(1) FROM customer_2019
WHERE customer_id IS NULL
   OR sum_amount IS NULL
   OR sum_amount_2019 IS NULL;

%%sql
-- 結果表示
SELECT customer_id, sum_amount, sum_amount_2019, 
       ROUND(sum_amount_2019*1.0/sum_amount *100.0) AS ratio_2019
FROM customer_2019
WHERE sum_amount_2019 != 0
LIMIT 10;

SUM(CASE WHEN)の書き方がなかなか覚えられない、よくENDを忘れる。

• S-085
  「顧客データ（customer）の全顧客に対し、郵便番号（postal_cd）を用いてジオコードデータ（geocode）を紐付け、新たな顧客データを作成せよ。ただし、1つの郵便番号（postal_cd）に複数の経度（longitude）、緯度（latitude）情報が紐づく場合は、経度（longitude）、緯度（latitude）の平均値を算出して使用すること。また、作成結果を確認するために結果を10件表示せよ。」

%%sql
CREATE TABLE geocode2 AS(
  SELECT postal_cd, AVG(longitude) AS longitude, AVG(latitude) AS latitude
  FROM geocode
  GROUP BY postal_cd
);

CREATE TABLE customer2 AS(
  SELECT c.*, g.longitude, g.latitude
  FROM customer c JOIN geocode2 g ON c.postal_cd = g.postal_cd
);

SELECT * FROM customer2
LIMIT 10;

テーブル×2を作成したが、もう少し簡単にできないものか。

• S-086
  「085で作成した緯度経度つき顧客データに対し、会員申込店舗コード（application_store_cd）をキーに店舗データ（store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客住所（address）の緯度・経度を用いて申込み店舗と顧客住所の距離（単位：km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は以下の簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示せよ。」

%%sql
CREATE TABLE customer3 AS(
  SELECT c.*, s.longitude AS s_longitude, s.latitude AS s_latitude, s.address AS s_address
  FROM customer2 c JOIN store s ON c.application_store_cd = s.store_cd
);

SELECT customer_id, address, s_address,
       6371*ACOS(SIN(latitude*1.0/180.0)*SIN(s_latitude*1.0/180.0)+COS(latitude*1.0/180.0)*COS(s_latitude*1.0/180.0)*COS((longitude-s_longitude)*1.0/180.0)) AS kyori
FROM customer3
LIMIT 10;

距離の計算式の部分はもっと簡潔に書きたいが、区切りが難しい。

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

%%sql
-- ①顧客ごとの売上金額合計集計（FROM receipt）
WITH customer_amount AS(
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM receipt
  GROUP BY customer_id
),

-- ② ①と顧客情報を外部結合（購入履歴のない顧客を削除しないため）
customer5 AS(
  SELECT c.*, ca.sum_amount
  FROM customer c LEFT JOIN customer_amount ca ON c.customer_id = ca.customer_id
),

-- ③ 顧客id重複削除の前処理（制約条件によりランク付け）
ranked_customer AS(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_name, postal_cd
                              ORDER BY sum_amount DESC, customer_id ASC) AS row_num
  FROM customer5
)

SELECT
  (SELECT COUNT(1) FROM ranked_customer 
  WHERE row_num = 1) AS customer_nayose, -- 名寄せ顧客データ件数
  (SELECT COUNT(1) FROM customer) AS customer -- 顧客データ件数
;

解説というか、実施手順をコメント文に記載。
ROW_NUMBER()の制約条件に付いて、ANDやORを用いないで記述するのがしっくりこないが、こういうものなのだろう。
最後はサブクエリを用いて強引に件数をカウントした。

• S-088
  「S-088: 087で作成したデータを元に、顧客データに統合名寄IDを付与したデータを作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。
  重複していない顧客：顧客ID（customer_id）を設定
  重複している顧客：前設問で抽出したレコードの顧客IDを設定
  顧客IDのユニーク件数と、統合名寄IDのユニーク件数の差も確認すること。」

%%sql
-- ①顧客ごとの売上金額合計集計（FROM receipt）
CREATE TABLE customer_amount AS(
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM receipt
  GROUP BY customer_id
);

-- ② ①と顧客情報を外部結合（購入履歴のない顧客を削除しないため）
CREATE TABLE customer5 AS(
  SELECT c.*, ca.sum_amount
  FROM customer c LEFT JOIN customer_amount ca ON c.customer_id = ca.customer_id
);

DROP TABLE customer_amount;

-- ③ 顧客id重複削除の前処理（制約条件によりランク付け）
CREATE TABLE ranked_customer AS(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_name, postal_cd
                              ORDER BY sum_amount DESC, customer_id ASC) AS row_num
  FROM customer5
);

DROP TABLE customer5;

-- ④顧客データに統合名寄IDを付与したデータを作成
CREATE TABLE customer6 AS(
  SELECT customer_id,
        (SELECT customer_id FROM ranked_customer
          WHERE customer_name = customer.customer_name
            AND postal_cd = customer.postal_cd
            AND row_num = 1) AS u_customer_id
  FROM customer
);

DROP TABLE ranked_customer;

-- ⑤顧客IDのユニーク件数と、統合名寄IDのユニーク件数
SELECT COUNT(DISTINCT customer_id), COUNT(DISTINCT u_customer_id)
FROM customer6; 

customer6作成部分で、統合名寄IDを検出するのは、ranked_customer作成部と同手順だが、ROW_NUMBER()があるのでより単純化している。工夫すれば、ranked_customerを飛ばすこともできそう。
WITHとCREATE TABLEを併用できない？ので、一時テーブルを複数作成する形で実施したが、もっとシンプルに実施できないかと思う。