🕌

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

7 min read

新しく出てきた内容

case式(検索case式)
cast
substr
文字列の連結
least
trunc

http://mickindex.sakura.ne.jp/database/db_case.html

https://www.postgresql.jp/document/13/html/functions-math.html

S-051:レシート明細テーブル(receipt)の売上エポック秒(sales_epoch)を
     日付(timestamp型)に変換し、"日"だけ取り出して
     レシート番号(receipt_no)、レシートサブ番号(receipt_sub_no)とともに抽出せよ。   
    なお、"日"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

select to_char(extract(day from to_timestamp(sales_epoch)),'FM00') as sales_day, 
       receipt_no,
       receipt_sub_no
 from receipt limit 10;

S-052:レシート明細テーブル(receipt)の売上金額(amount)を顧客ID(customer_id)ごとに
     合計の上、売上金額合計に対して2000円以下を0、2000円超を1に2値化し、
     顧客ID、合計金額とともに10件表示せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    --検索case句を使って条件分岐する。

select customer_id,
       sum(amount), 
       case when sum(amount) <= 2000 then '0'
        else '1' end as amount_flg 
 from receipt where customer_id not like 'Z%' group by customer_id limit 10;

---別解
with customer_amount as (select customer_id, sum(amount) as amount
                          from receipt where customer_id not like 'Z%' group by customer_id)
select customer_id,
       amount,
       case when amount <= 2000 then '0' else '1' end as amount_flg
 from customer_amount limit 10;

検索CASECASE WHEN color = '1' THEN 'red'
       WHEN color = '2' THEN 'blue'
       ELSE 'green' END 

S-053:顧客テーブル(customer)の郵便番号(postal_cd)に対し、
     東京(先頭3桁が100〜209のもの)を1、それ以外のものを0に2値化せよ。
    さらにレシート明細テーブル(receipt)と結合し、
     全期間において買い物実績のある顧客数を、作成した2値ごとにカウントせよ。

with cust as (select customer_id,
                     postal_cd, 
                     case when 100 <= cast(substr(postal_cd, 1, 3) as integer) and cast(substr(postal_cd, 1, 3) as integer) <=209 then 1
                      else 0 end as postal_flg
	       from customer),
     rece as (select customer_id,
                     sum(amount) as amount
               from receipt group by customer_id)
select cu.postal_flg,
       count(cu.postal_flg)
 from cust as cu inner join rece as r on cu.customer_id = r.customer_id group by cu.postal_flg;

https://postgresweb.com/post-368

--S-054はSQL向きではないため割愛する
S-054: 顧客テーブル(customer)の住所(address)は、埼玉県、千葉県、東京都、神奈川県のいずれかとなっている。都道府県毎にコード値を作成し、顧客ID、住所とともに抽出せよ。値は埼玉県を11、千葉県を12、東京都を13、神奈川県を14とすること。結果は10件表示させれば良い。


S-055:レシート明細テーブル(receipt)の売上金額(amount)を顧客ID(customer_id)ごとに
     合計し、その合計金額の四分位点を求めよ。
    その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、
     顧客ID、売上金額合計とともに表示せよ。
    カテゴリ値は上から順に1〜4とする。結果は10件表示させれば良い。

       最小値以上第一四分位未満
       第一四分位以上第二四分位未満
       第二四分位以上第三四分位未満
       第三四分位以上
with customer_amount as (select customer_id,
                                sum(amount) as amount 
                          from receipt group by customer_id),
     quartile as (select percentile_cont(0.25) within group (order by amount) as pct25,
                         percentile_cont(0.5) within group (order by amount) as pct50,
	                 percentile_cont(0.75) within group (order by amount) as pct75
                   from customer_amount)
select cu.customer_id,
       cu.amount, 
       case when cu.amount < q.pct25 then 1 
	    when q.pct25 <= cu.amount and cu.amount < q.pct50 then 2 
	    when q.pct50 <= cu.amount and cu.amount < q.pct75 then 3 
	else 4 end as quartile_point 
 from customer_amount as cu, quartile as q limit 10;


S-056:顧客テーブル(customer)の年齢(age)をもとに10歳刻みで年代を算出し、
     顧客ID(customer_id)、生年月日(birth_day)とともに抽出せよ。
    ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。
    先頭10件を表示させればよい。

select customer_id,
       birth_day,
       age,
       case when age / 10 * 10 < 60 then age / 10 * 10
        else 60 end as era
 from customer;

---別解1
with customer_era as (select customer_id,
                             birth_day,
			     age,
                             case when age / 10 * 10 < 60 then age / 10 * 10 else 60 end as era
		       from customer)
select * from customer_era where era = 10;

---別解2
with era as (select customer_id,
                    birth_day,
                    age,
                    least(cast(trunc(cast(age as numeric) /10) * 10 as integer),60) as least
              from customer)
select * from era where least = 10;

--SQLにおける整数同士の除算は小数や余りが切り捨てられることに注意。
 (45/10 = 4.5の 0.5が切り捨てられて 4 になる。)
 truncはnumeric(数値)型を受ける。
--LEAST(値1, 値2,...):引数の最小値を取る


S-057:前問題の抽出結果と性別(gender)を組み合わせ、
     新たに性別×年代の組み合わせを表すカテゴリデータを作成せよ。
    組み合わせを表すカテゴリの値は任意とする。先頭10件を表示させればよい。
     --'||': 文字列データに任意の文字列を連結したり、複数の文字列データを連結する

with category as (select customer_id,
                         case when gender = '男性' then 0 when gender = '女性' then 1 else 9 end as gender_code,
                         birth_day,
                         case when age / 10 * 10 < 60 then age / 10 * 10 else 60 end as era from customer)
select customer_id, birth_day, gender_code || cast(era as text) as gender_era
 from category;

--S-058はSQL向きではないため割愛する
S-058: 顧客テーブル(customer)の性別コード(gender_cd)をダミー変数化し、顧客ID(customer_id)とともに抽出せよ。結果は10件表示させれば良い。


S-059:レシート明細テーブル(receipt)の売上金額(amount)を
     顧客ID(customer_id)ごとに合計し、合計した売上金額を平均0、標準偏差1に
     標準化して顧客ID、売上金額合計とともに表示せよ。
    標準化に使用する標準偏差は、不偏標準偏差と標本標準偏差のどちらでも良い。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    結果は10件表示させれば良い。

with customer_amount as (select customer_id,
                                sum(amount) as amount
                          from receipt 
                          where customer_id not like 'Z%' group by customer_id),
     mean_std as (select avg(amount) as mean,
                         stddev_samp(amount) as std
                   from customer_amount)
select customer_id,
       amount,
       (amount - mean) / std as std_amount
 from customer_amount, mean_std limit 10;

std_amountのところで標準化している。


S-060:レシート明細テーブル(receipt)の売上金額(amount)を
     顧客ID(customer_id)ごとに合計し、合計した売上金額を最小値0、最大値1に
     正規化して顧客ID、売上金額合計とともに表示せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    結果は10件表示させれば良い。
     --1.0を掛けることによりとりあえずint型から脱却することが出来る。

with customer_amount as (select customer_id, sum(amount) as amount
                          from receipt 
                          where customer_id not like 'Z%' group by customer_id),
     max_min_ as (select max(amount) as max_,
                         min(amount) as min_
                   from customer_amount)
select customer_id,
       amount,
       cast((amount - min_) as numeric) / cast((max_ - min_) as numeric) as nor_amount
 from customer_amount, max_min_ limit 10;

---別解
select customer_id,
       amount,
       (amount - min_)*1.0 / (max_ - min_)*1.0 as nor_amount
 from customer_amount, max_min_ limit 10;

Discussion

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