データサイエンス100本ノックをSQLで解いていったうえで個人的に躓いたポイントをメモしていきます。この記事では前半の51問目までが対象です。データベースはPostgreSQL12です。
基本的にはこちらの書籍を学習した後に解いているのでこの書籍で解決できそうな部分は省略、とはいえ忘れてる部分は調べつつやってるので重複する部分もあります。
S-013 正規表現を条件に入れる
select *
from customer
where
status_cd ~ '^[A-F]'
limit 10;前問までの感覚でstatus_cd like ‘^[A-F]’とするとうまくいかない。正規表現を使う場合は~を使う。^は先頭の文字を指定しブラケット[]内の文字1つと一致すればマッチしたと判断。A-FでA,B,…,E,Fと文字コード範囲指定ができる。ちなみに末尾は$をつける。
S-022 テーブルのユニーク件数をカウント
dintinctを使えばユニークなデータを持ってこれるため、持ってきてからデータをカウントする方法
select count(*)
from (
select distinct customer_id
from receipt
) x;でも解答ではcount内にdistinctを入れることでシンプルな記述に
select count(distinct customer_id) from receipt;S-028 中央値、最頻値を取得する
PostgreSQLには中央値を取得する関数（MEDIAN）は存在しないようです。PERCENTILE_CONTを用いる。これは順序集合集約関数と呼ばれ少し書き方が違います。
select store_cd, percentile_cont(0.5) within group(order by amount) as median
from receipt
group by store_cd
order by median desc
limit 5;最頻値も順序集合集約関数
select store_cd, mode() within group(order by product_cd) as mode
from receipt
group by store_cd;S-030 統計量を取得する
統計処理用の集約関数を使用する。
stddev,varianceは過去に使われたものの名残のようなので、母集団、標本集団どちらを取得しているかわかる関数を使っておくほうが無難。調べればすぐに出る問題だけど一応メモです。
S-038 条件付きの結合
with customer_amount as (
select customer_id, sum(amount) as sum_amount
from receipt
where customer_id not like 'Z%'
group by customer_id
)
select c.customer_id, coalesce(ca.sum_amount, 0)
from customer as c left join customer_amount as ca
on c.customer_id = ca.customer_id
where c.gender_cd = '1'
limit 10;receiptテーブルには購入履歴のある顧客のみしかデータがないため、買い物実績がない顧客を含めるためにはcustomerテーブルのcustomer_idが必要とわかります。合計を求めたいのでreceiptテーブル内で顧客ごとの合計を求めるサブクエリを使います。
これとcustomerテーブルを結合しますが、買い物実績がない顧客を残すために外部結合を使います。この際、購入実績がないデータは0にしたいためcoalesce関数で0埋めをすること。最後に性別コードで女性のみを取得すればOKです。
S-045 日付型と文字列の変換
castを使ってできます。
select customer_id, cast(birth_day as varchar) as birth_day
from customer
limit 10;日付を文字列へ、文字列を日付へ変換する関数があるのでそちらを使ってもできます。出力形式は色々あるのでこちらを参考に。
select customer_id, to_char(birth_day, 'YYYYMMDD') as birth_day
from customer
limit 10;
コメント