データサイエンス100本ノック（SQL）81~90
追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく
S-081:単価（unit_price）と原価（unit_cost）の欠損値について、
それぞれの平均値で補完した新たなproduct_2を作成せよ。
なお、平均値について1円未満は四捨五入とする。
補完実施後、各項目について欠損が生じていないことも確認すること。
PostgreSQL
drop table if exists product_2;
create table if not exists product_2 as
with unit_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 unit_avg;
#平均値算出 --u_p_avg：403、u_c_avg：302
select (round(avg(unit_price))) as u_p_avg,
(round(avg(unit_cost))) as u_c_avg
from product;
#欠損値確認
select * from product_2 where product_cd = 'P040802007';
BigQuery
drop table if exists [データセット名].product_2;
create table if not exists [データセット名].product_2 as
with unit_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 unit_avg;
select *
from [データセット名].product_2
where unit_price is null or
unit_cost is null;
--create table if not exists【テーブル名】as：その【テーブル名】が存在しない場合に新たに作成
S-082:単価（unit_price）と原価（unit_cost）の欠損値について、
それぞれの中央値で補完した新たなproduct_3を作成せよ。
なお、中央値について1円未満は四捨五入とする。
補完実施後、各項目について欠損が生じていないことも確認すること。
PostgreSQL
drop table if exists product_3;
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_;
--u_p_med：252、u_c_med：189
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 * from product_3 where product_cd = 'P040802007';
BigQuery
drop table if exists [データセット名].product_3;
create table if not exists [データセット名].product_3 as
with avg_ as (
select round(percentile_cont(unit_price, 0.5) over()) as med_u_p,
round(percentile_cont(unit_cost, 0.5) over()) 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_;
S-083:単価（unit_price）と原価（unit_cost）の欠損値について、
各商品の小区分（category_small_cd）ごとに算出した中央値で
補完した新たなproduct_4を作成せよ。
なお、中央値について1円未満は四捨五入とする。
補完実施後、各項目について欠損が生じていないことも確認すること。
PostgreSQL
drop table if exists product_4;
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;
BigQuery
drop table if exists [データセット名].product_4;
create table if not exists [データセット名].product_4 as
with csc_med as
(
select distinct category_small_cd,
round(percentile_cont(unit_price, 0.5) over(partition by category_small_cd)) as csc_up_med,
round(percentile_cont(unit_cost, 0.5) over(partition by category_small_cd)) as csc_uc_med
from [データセット名].product
)
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
order by product_cd asc;
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件表示させれば良い。
PostgreSQL
with sales_amount_all 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_all.customer_id,
sales_2019.sum as sales_amount_2019,
sales_all.sum as sales_amount,
sales_2019.sum * 1.0 / sales_all.sum * 1.0 as percentage
from sales_amount_all as sales_all
inner join sales_amount_2019 as sales_2019
on sales_all.customer_id = sales_2019.customer_id
limit 10;
BigQuery
with sales_amount_all as (
select cu.customer_id,
sum(r.amount) as sum
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) as sum
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_all.customer_id,
sales_2019.sum as sales_amount_2019,
sales_all.sum as sales_amount,
sales_2019.sum * 1.0 / sales_all.sum * 1.0 as percentage
from sales_amount_all as sales_all
inner join sales_amount_2019 as sales_2019
on sales_all.customer_id = sales_2019.customer_id
limit 10;
S-085:顧客テーブル（customer）の全顧客に対し、郵便番号（postal_cd）を用いて
経度緯度変換用テーブル（geocode）を紐付け、新たなcustomer_1を作成せよ。
ただし、複数紐づく場合は
経度（longitude）、緯度（latitude）それぞれ平均を算出すること。
PostgreSQL
drop table if exists customer_1;
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;
BigQuery
drop table if exists [データセット名].customer_1;
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) )
PostgreSQL
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;
BigQuery
BigQueryにラジアンを計算できる関数が無いため行わない
--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