🌊

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

7 min read

新しく出てきた内容

age ( timestamp, timestamp )
random()
create文でテーブル作成
delete文でテーブル削除


S-071:レシート明細テーブル(receipt)の売上日(sales_ymd)に対し、
     顧客テーブル(customer)の会員申込日(application_date)からの経過月数を計算し、
     顧客ID(customer_id)、売上日、会員申込日とともに表示せよ。
    結果は10件表示させれば良い(なお、sales_ymdは数値、
     application_dateは文字列でデータを保持している点に注意)。
    1ヶ月未満は切り捨てること。

select distinct r.customer_id,
                r.sales_ymd,
		cu.application_date, 
                extract(year from age(to_date(cast(r.sales_ymd as text),'YYYYMMDD'), to_date(cu.application_date, 'YYYYMMDD')) * 12) as elapsed_months,
		age(to_date(cast(r.sales_ymd as text),'YYYYMMDD'), to_date(cu.application_date, 'YYYYMMDD'))*12,
 from receipt as r inner join customer as cu on r.customer_id = cu.customer_id limit 10;

--別解
select distinct r.customer_id,
                r.sales_ymd,
		cu.application_date, 
   	        extract(year from age(to_date(cast(sales_ymd as text),'YYYYMMDD'),
		to_date(cu.application_date, 'YYYYMMDD'))) * 12
                  + extract(month from age(to_date(cast(sales_ymd as text),'YYYYMMDD'),
		to_date(cu.application_date, 'YYYYMMDD'))) as "elapsed months"
 from receipt as r inner join customer as cu on r.customer_id = cu.customer_id limit 10;

--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年未満は切り捨てること。

select distinct r.customer_id,
                r.sales_ymd,
		cu.application_date,
                extract(year from age(to_date(cast(r.sales_ymd as text),'YYYYMMDD'), to_date(cu.application_date, 'YYYYMMDD'))) as elapsed_years,
                age(to_date(cast(r.sales_ymd as text),'YYYYMMDD'), to_date(cu.application_date, 'YYYYMMDD'))
 from receipt as r inner join customer as cu on r.customer_id = cu.customer_id limit 10;

*12が無いことが先程の問題と異なる


S-073:レシート明細テーブル(receipt)の売上日(sales_ymd)に対し、
     顧客テーブル(customer)の会員申込日(application_date)からの
     エポック秒による経過時間を計算し、
     顧客ID(customer_id)、売上日、会員申込日とともに表示せよ。
    結果は10件表示させれば良い(なお、sales_ymdは数値、
     application_dateは文字列でデータを保持している点に注意)。
    なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする。

select distinct r.customer_id,
                r.sales_ymd,
		cu.application_date,
                extract(epoch from to_date(cast(sales_ymd as text),'YYYYMMDD'))
	          - extract(epoch from to_date(application_date,'YYYYMMDD')) as elapsed_epoch_time
 from receipt as r inner join customer as cu on r.customer_id = cu.customer_id limit 10;

S-074:レシート明細テーブル(receipt)の売上日(sales_ymd)に対し、
     当該週の月曜日からの経過日数を計算し、売上日、当該週の月曜日付とともに表示せよ。
    結果は10件表示させれば良い
   (なお、sales_ymdは数値でデータを保持している点に注意)。
    --dow:EXTRACT関数において、日曜日(0)から土曜日(6)までの曜日を抽出する。
    (isodow:月曜日(1)から日曜日(7)までの曜日)
    --つまり、月曜日が1なので、そこからの経過日数を求めるには-1する必要がある。
    --dateとdouble precisionはそのまま計算できないので、
      double precisionをintegerに変換する必要がある。

with elapsed as (select 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
	          from receipt)
select *,
       sales_ymd - cast(elapsed_days as integer) as mondays
 from elapsed limit 10;

S-075:顧客テーブル(customer)からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。

select * from customer where random() < 0.01 limit 10;

--S-076は解答を見ても良く分からなかったので割愛する。
S-076: 顧客テーブル(customer)から性別(gender_cd)の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。


S-077:レシート明細テーブル(receipt)の売上金額(amount)を顧客単位に合計し、
     合計した売上金額の外れ値を抽出せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    なお、ここでは外れ値を平均から3σ以上離れたものとする。結果は10件表示させれば良い。

with customer_amount as (select customer_id,
                                sum(amount) as sum_amount
                          from receipt where customer_id not like 'Z%' group by customer_id),
     statistic as (select avg(sum_amount) as avg_amount,
                          stddev_samp(sum_amount) as std_amount
                    from customer_amount)
select customer_id,
       sum_amount
 from customer_amount, statistic where avg_amount + std_amount * 3 < sum_amount;

ここでは平均+3σより大きいものだけを表示させている。
(平均-3σより小さいとマイナスになるのでそもそも存在しない)


S-078:レシート明細テーブル(receipt)の売上金額(amount)を顧客単位に合計し、
     合計した売上金額の外れ値を抽出せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    なお、ここでは外れ値を第一四分位と第三四分位の差であるIQRを用いて、
    「第一四分位数-1.5×IQR」よりも下回るもの、
     または「第三四分位数+1.5×IQR」を超えるものとする。結果は10件表示させれば良い。

with customer_amount as (select customer_id, sum(amount) as sum_amount from receipt where customer_id not like 'Z%' group by customer_id),
     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_amount)
select customer_id,
       sum_amount
 from customer_amount, statistic where per75 + 1.5 * iqr < sum_amount limit 10;

S-079: 商品テーブル(product)の各項目に対し、欠損数を確認せよ。

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;

回答

create table product_1 as select * from product;
delete from product_1 where unit_price is null or unit_cost is 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

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