Zenn
🕌

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

2021/12/02に公開
PostgreSQL
SQL
初心者
idea

新しく出てきた内容

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

追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく

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

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

BigQuery
 select format_date('%d', cast(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(cast(sales_epoch as int64)), 'Asia/Tokyo') as date)) 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句を使って条件分岐する。

PostgreSQL
 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;

BigQuery
 select customer_id,
        sum(amount) as amount_sum,
        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;


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

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

PostgreSQL
 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;

BigQuery
 with cust as (
               select customer_id,
                      postal_cd,
                      case when 100 <= cast(substr(postal_cd, 1, 3) as int64)
                                and cast(substr(postal_cd, 1, 3) as int64) <=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
 order by cu.postal_flg asc;

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件表示させれば良い。

       最小値以上第一四分位未満
       第一四分位以上第二四分位未満
       第二四分位以上第三四分位未満
       第三四分位以上

PostgreSQL
 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;

BigQuery
 with customer_amount as (
                          select customer_id,
                                 sum(amount) as amount
                          from [データセット名].receipt
                          group by customer_id
                         ),
      quartile as (
                   select distinct percentile_cont(amount, 0.25) over() as pct25,
                                   percentile_cont(amount, 0.5) over() as pct50,
                                   percentile_cont(amount, 0.75) over() 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件を表示させればよい。

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

BigQuery
 select customer_id,
        birth_day,
        age,
        case when age / 10 * 10 < 60 then trunc(age / 10) * 10
                                     else 60
             end as era
 from [データセット名].customer;

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

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

PostgreSQL
 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;

BigQuery
 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 trunc(age / 10) * 10
                                                       else 60
                               end as era
                   from [データセット名].customer
                  )
 select customer_id,
        birth_day,
        gender_code || cast(era as string) 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件表示させれば良い。

PostgreSQL
 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;

BigQuery
 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型から脱却することが出来る。

PostgreSQL
 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;

BigQuery
 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;

Discussion