🌊

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

2021/12/02に公開

新しく出てきた内容

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