📘

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

7 min read

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

create table if not exists product_2 as
 with avg_ as (select round(avg(unit_price)) as u_p_avg,
                      round(avg(unit_cost)) as u_c_avg
		from product)
 select product_cd,
        category_major_cd,
	category_medium_cd,
	category_small_cd, 
        coalesce(unit_price, u_p_avg) as unit_price, 
        coalesce(unit_cost, u_c_avg) as unit_cost 
  from product cross join avg_;

select round(avg(unit_price)) as u_p_avg,
       round(avg(unit_cost)) as u_c_avg from product;
--u_p_avg:403、u_c_avg:302
select * from product_2 where product_cd = 'P040802007';

--create table if not exists【テーブル名】as:その【テーブル名】が存在しない場合に新たに作成


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

create table if not exists product_3 as
 with avg_ as (select round(percentile_cont(0.5) within group (order by unit_price)) as med_u_p, 
		      round(percentile_cont(0.5) within group (order by unit_cost)) as med_u_c
		from product)
 select product_cd,
        category_major_cd,
	category_medium_cd,
	category_small_cd, 
        coalesce(unit_price, med_u_p) as unit_price, 
        coalesce(unit_cost, med_u_c) as unit_cost
  from product cross join avg_;

select round(percentile_cont(0.5) within group (order by unit_price)) as med_u_p, 
       round(percentile_cont(0.5) within group (order by unit_cost)) as med_u_c 
 from product;
--u_p_med:252、u_c_med:189
select * from product_3 where product_cd = 'P040802007';

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

create table if not exists product_4 as
 with csc_med as (select category_small_cd,
                         round(percentile_cont(0.5) within group (order by unit_price)) as csc_up_med,
                         round(percentile_cont(0.5) within group (order by unit_cost)) as csc_uc_med 
	           from product group by category_small_cd)
 select product_cd,
        category_major_cd,
        category_medium_cd,
        pr.category_small_cd, 
        coalesce(unit_price, csc_up_med) as unit_price, 
        coalesce(unit_cost, csc_uc_med) as unit_cost 
  from product as pr inner join csc_med as c_m on pr.category_small_cd = c_m.category_small_cd;
select category_small_cd,
       round(percentile_cont(0.5) within group (order by unit_price)) as csc_up_med,
       round(percentile_cont(0.5) within group (order by unit_cost)) as csc_uc_med
 from product group by category_small_cd;

/*
'P040802007'  040802: csc_up_med:313、csc_uc_med:235
'P050103021'  050103: csc_up_med:132、csc_uc_med:100
'P050405009'  050405: csc_up_med:178、csc_uc_med:134
'P060802026'  060802: csc_up_med:270、csc_uc_med:200
'P070202092'  070202: csc_up_med:238、csc_uc_med:179
'P080504027'  080504: csc_up_med:258、csc_uc_med:196
'P090204185'  090204: csc_up_med:694、csc_uc_med:521
*/
select * from product_4 where product_cd = 'P040802007';

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

with sales_amount as (select cu.customer_id, sum(r.amount)
                       from customer as cu inner join receipt as r
		         on cu.customer_id = r.customer_id 
                       group by cu.customer_id),
     sales_amount_2019 as (select cu.customer_id, sum(r.amount)
                            from customer as cu inner join receipt as r 
		              on cu.customer_id = r.customer_id
			    where 20190000 < r.sales_ymd and r.sales_ymd < 20191231 group by cu.customer_id)
select sales.customer_id,
       sales_2019.sum as sales_amount_2019,
       sales.sum as sales_amount,
       sales_2019.sum * 1.0 / sales.sum * 1.0 as percentage 
 from sales_amount as sales inner join sales_amount_2019 as sales_2019
   on sales.customer_id = sales_2019.customer_id limit 10;

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

create table if not exists customer_1 as
 with avg_ge as (select postal_cd,
                        avg(longitude) as m_longitude,
			avg(latitude) as m_latitude
	          from geocode group by postal_cd) 
 select cu.*, a_ge.m_longitude, a_ge.m_latitude
  from customer as cu inner join avg_ge as a_ge on cu.postal_cd = a_ge.postal_cd;

S-086:前設問で作成した緯度経度つき顧客テーブル(customer_1)に対し、
    申込み店舗コード(application_store_cd)をキーに店舗テーブル(store)と結合せよ。
   そして申込み店舗の緯度(latitude)・経度情報(longitude)と
    顧客の緯度・経度を用いて距離(km)を求め、
    顧客ID(customer_id)、顧客住所(address)、店舗住所(address)とともに表示せよ。
   計算式は簡易式で良いものとするが、
    その他精度の高い方式を利用したライブラリを利用してもかまわない。
   結果は10件表示すれば良い。
    --緯度(ラジアン):𝜙、経度(ラジアン):𝜆、
    --距離𝐿 = 6371 ∗ 𝑎𝑟𝑐𝑐𝑜𝑠( 𝑠𝑖𝑛𝜙1 ∗ 𝑠𝑖𝑛𝜙2 + 𝑐𝑜𝑠𝜙1 ∗ 𝑐𝑜𝑠𝜙2 ∗ 𝑐𝑜𝑠(𝜆1−𝜆2) )

select c1.customer_id, c1.address as c_ad, s.address as s_ad,
       6371 * acos(sin(radians(c1.m_latitude)) * sin(radians(s.latitude)) +
		   cos(radians(c1.m_latitude)) * cos(radians(s.latitude)) * cos(radians(c1.m_longitude - s.longitude))) as distance
 from customer_1 as c1 inner join store as s on c1.application_store_cd = s.store_cd limit 10;

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

select customer_name from customer; #21971行
select distinct customer_name from customer; #18689行
select distinct postal_cd from customer; #1239行

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

重複していない顧客:顧客ID(customer_id)を設定
重複している顧客:前設問で抽出したレコードの顧客IDを設定

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


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

Discussion

ログインするとコメントできます