データサイエンス100本ノック（SQL）71~80
新しく出てきた内容
age ( timestamp, timestamp )
random()
create文でテーブル作成
delete文でテーブル削除
追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく
S-071:レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、
顧客テーブル(customer)の会員申込日(application_date)からの経過月数を計算し、
顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。
結果は10件表示させれば良い（なお、sales_ymdは数値、
application_dateは文字列でデータを保持している点に注意）。
1ヶ月未満は切り捨てること。
PostgreSQL
create table if not exists sales as select distinct customer_id,
sales_ymd
from receipt;
create table if not exists app_date as select customer_id,
application_date
from customer;
select distinct sa.customer_id,
sa.sales_ymd,
ap.application_date,
extract(year from age(to_date(cast(sa.sales_ymd as text),'YYYYMMDD'), to_date(ap.application_date, 'YYYYMMDD'))*12) as elapsed_months
from sales as sa
inner join app_date as ap
on sa.customer_id = ap.customer_id;
BigQuery
create table if not exists [データセット名].sales as select distinct customer_id,
sales_ymd
from [データセット名].receipt;
create table if not exists [データセット名].app_date as select customer_id,
application_date
from [データセット名].customer;
select sa.customer_id,
sa.sales_ymd,
ap.application_date,
date_diff(parse_date('%Y%m%d', cast(sa.sales_ymd as string)), parse_date('%Y%m%d', ap.application_date), month) as elapsed_months,
from [データセット名].sales as sa
inner join [データセット名].app_date as ap
on sa.customer_id = ap.customer_id;
--EXTACT()とAGE()を組み合わせて算出する。
age ( timestamp, timestamp ) → interval
引数間の減算。日数だけでなく年と月を使用した「言葉による」結果を生成
age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days
S-072:レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、
顧客テーブル(customer)の会員申込日(application_date)からの経過年数を計算し、
顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。
結果は10件表示させれば良い（なお、sales_ymdは数値、
application_dateは文字列でデータを保持している点に注意）。
1年未満は切り捨てること。
PostgreSQL
select distinct sa.customer_id,
sa.sales_ymd,
ap.application_date,
extract(year from age(to_date(cast(sa.sales_ymd as text),'YYYYMMDD'), to_date(ap.application_date, 'YYYYMMDD'))) as elapsed_years
from sales as sa
inner join app_date as ap
on sa.customer_id = ap.customer_id;
BigQuery
create temp function sales_date(sales_ymd int64) as (
parse_date('%Y%m%d', cast(sales_ymd as string))
);
create temp function appli_date(application_date string) as (
parse_date('%Y%m%d', application_date)
);
select sa.customer_id,
sa.sales_ymd,
ap.application_date,
date_diff(sales_date(sa.sales_ymd), appli_date(ap.application_date), year)
+ if(
date_diff(
sales_date(sa.sales_ymd),
date_add(
appli_date(ap.application_date),
interval
date_diff(
sales_date(sa.sales_ymd),
appli_date(ap.application_date),
year
)
year
),
day
)
>= 0, --条件式
0, --真のとき返す値、
-1 --偽のとき返す値
)
from [データセット名].sales as sa
inner join [データセット名].app_date as ap
on sa.customer_id = ap.customer_id;
*12が無いことが先程の問題と異なる
S-073:レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、
顧客テーブル（customer）の会員申込日（application_date）からの
エポック秒による経過時間を計算し、
顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。
結果は10件表示させれば良い（なお、sales_ymdは数値、
application_dateは文字列でデータを保持している点に注意）。
なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする。
PostgreSQL
select distinct sa.customer_id,
sa.sales_ymd,
ap.application_date,
extract(epoch from to_date(cast(sa.sales_ymd as text),'YYYYMMDD'))
- extract(epoch from to_date(ap.application_date, 'YYYYMMDD')) as elapsed_epoch_time
from sales as sa
inner join app_date as ap
on sa.customer_id = ap.customer_id;
BigQuery
select distinct sa.customer_id,
sa.sales_ymd,
ap.application_date,
unix_seconds(parse_timestamp("%Y%m%d", cast(sa.sales_ymd as string), 'Asia/Tokyo')) as sales_ymd_epoch,
unix_seconds(parse_timestamp("%Y%m%d", ap.application_date, 'Asia/Tokyo')) as applicaton_date_epoch,
unix_seconds(parse_timestamp("%Y%m%d", cast(sa.sales_ymd as string), 'Asia/Tokyo'))
- unix_seconds(parse_timestamp("%Y%m%d", ap.application_date, 'Asia/Tokyo')) as elapsed_epoch_time
from [データセット名].sales as sa
inner join [データセット名].app_date as ap
on sa.customer_id = ap.customer_id;
S-074:レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、
当該週の月曜日からの経過日数を計算し、売上日、当該週の月曜日付とともに表示せよ。
結果は10件表示させれば良い
（なお、sales_ymdは数値でデータを保持している点に注意）。
--dow：EXTRACT関数において、日曜日（0）から土曜日（6）までの曜日を抽出する。
（isodow：月曜日（1）から日曜日（7）までの曜日）
--つまり、月曜日が1なので、そこからの経過日数を求めるには-1する必要がある。
--dateとdouble precisionはそのまま計算できないので、
double precisionをintegerに変換する必要がある。
PostgreSQL
select distinct customer_id,
to_date(cast(sales_ymd as text),'YYYYMMDD') as sales_ymd,
extract(dow from to_date(cast(sales_ymd as text),'YYYYMMDD') -1) as elapsed_days,
to_date(cast(sales_ymd as text),'YYYYMMDD')
- cast(extract(dow from to_date(cast(sales_ymd as text),'YYYYMMDD') -1) as integer) as mondays
from receipt;
BigQuery
select distinct customer_id,
parse_date('%Y%m%d', cast(sales_ymd as string)) as sales_ymd,
extract(dayofweek from parse_date('%Y%m%d', cast(sales_ymd as string)) - 2) as elapsed_days,
parse_date('%Y%m%d', cast(sales_ymd as string))
- extract(dayofweek from parse_date('%Y%m%d', cast(sales_ymd as string)) - 2) as mondays
from [データセット名].receipt;
S-075:顧客テーブル（customer）からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。
PostgreSQL
select *
from customer
where random() < 0.01
limit 10;
BigQuery
select *
from [データセット名].customer
where rand() < 0.01
limit 10;
--S-076は解答を見ても良く分からなかったので割愛する。
S-076: 顧客テーブル（customer）から性別（gender_cd）の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。
S-077:レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、
合計した売上金額の外れ値を抽出せよ。
ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
なお、ここでは外れ値を平均から3σ以上離れたものとする。結果は10件表示させれば良い。
PostgreSQL
create table if not exists customer_sum_amount as
select customer_id,
sum(amount) as sum_amount
from receipt
where customer_id not like 'Z%'
group by customer_id;
with statistic as (
select avg(sum_amount) as avg_amount,
stddev_samp(sum_amount) as std_amount
from customer_sum_amount
)
select customer_id,
sum_amount
from customer_sum_amount,
statistic
where sum_amount < avg_amount - 3 * std_amount or
avg_amount + 3 * std_amount < sum_amount;
BigQuery
create table if not exists [データセット名].customer_sum_amount as select customer_id,
sum(amount) as sum_amount
from [データセット名].receipt
where customer_id not like 'Z%'
group by customer_id;
with statistic as (
select avg(sum_amount) as avg_amount,
stddev_samp(sum_amount) as std_amount
from [データセット名].customer_sum_amount
)
select customer_id,
sum_amount
from [データセット名].customer_sum_amount,
statistic
where sum_amount < avg_amount - 3 * std_amount or
avg_amount + 3 * std_amount < sum_amount;
ここでは平均+3σより大きいものだけを表示させている。
（平均-3σより小さいとマイナスになるのでそもそも存在しない）
S-078:レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、
合計した売上金額の外れ値を抽出せよ。
ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
なお、ここでは外れ値を第一四分位と第三四分位の差であるIQRを用いて、
「第一四分位数-1.5×IQR」よりも下回るもの、
または「第三四分位数+1.5×IQR」を超えるものとする。結果は10件表示させれば良い。
PostgreSQL
with statistic as (
select percentile_cont(0.25) within group (order by sum_amount) as per25,
percentile_cont(0.75) within group (order by sum_amount) as per75,
percentile_cont(0.75) within group (order by sum_amount)
- percentile_cont(0.25) within group (order by sum_amount) as iqr
from customer_sum_amount
)
select customer_id,
sum_amount
from customer_sum_amount,
statistic
where per75 + 1.5 * iqr < sum_amount
limit 10;
BigQuery
with statistic as (
select percentile_cont(sum_amount, 0.25) over() as per25,
percentile_cont(sum_amount, 0.75) over() as per75,
percentile_cont(sum_amount, 0.75) over()
- percentile_cont(sum_amount, 0.25) over() as iqr
from [データセット名].customer_sum_amount
)
select distinct
customer_id,
sum_amount
from [データセット名].customer_sum_amount,
statistic
where per75 + 1.5 * iqr < sum_amount
limit 10;
S-079: 商品テーブル（product）の各項目に対し、欠損数を確認せよ。
PostgreSQL
select count(*) - count(product_cd) as product_cd_null,
count(*) - count(category_major_cd) as category_major_cd_null,
count(*) - count(category_medium_cd) as category_medium_cd_null,
count(*) - count(category_small_cd) as category_small_cd_null,
count(*) - count(unit_price) as unit_price_null,
count(*) - count(unit_cost) as unit_cost_null
from product;
BigQuery
select count(*) - count(product_cd) as product_cd_null,
count(*) - count(category_major_cd) as category_major_cd_null,
count(*) - count(category_medium_cd) as category_medium_cd_null,
count(*) - count(category_small_cd) as category_small_cd_null,
count(*) - count(unit_price) as unit_price_null,
count(*) - count(unit_cost) as unit_cost_null
from [データセット名].product;
S-080:商品テーブル（product）のいずれかの項目に欠損が発生しているレコードを
全て削除した新たなproduct_1を作成せよ。
なお、削除前後の件数を表示させ、
前設問で確認した件数だけ減少していることも確認すること。
--削除前の件数
select count(product_cd),
count(category_major_cd),
count(category_medium_cd),
count(category_small_cd),
count(unit_price),
count(unit_cost)
from product;
回答
PostgreSQL
create table if not exists product_1 as select *
from product
where unit_price is not null or
unit_cost is not null;
--別解
create table if not exists product_1 as select * from product;
delete from product_1 where unit_price is null or unit_cost is null;
BigQuery
create table if not exists [データセット名].product_1 as select *
from [データセット名].product
where unit_price is not null or
unit_cost is not null;
--削除後の件数
select count(product_cd),
count(category_major_cd),
count(category_medium_cd),
count(category_small_cd),
count(unit_price),
count(unit_cost)
from product_1;
Discussion