【データサイエンスSQL100本ノック】20~30問

はじめに

• SQLの勉強をしないといけないと思っていたところTwitterでバズっていたのでやってみることにしました！
• 実務でSQLは必須のためやって損はないと思ったため
• 何か質問やご指摘あればコメント頂けると嬉しいです！

環境

• M1 MacBook Air
• Docker for Desktop
• postgresql
• dbver (SQLクライアントツール使用)
• ↓実践環境は以下の通りです
  

問題

問21

S-021: レシート明細データ（receipt）に対し、件数をカウントせよ。

select count(1)
from receipt;

• count(*)：NULLがあるか関係なく取得された値の行を取得

問22

S-022: レシート明細データ（receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。

select count(distinct customer_id)
from receipt;

• distinnct：値の重複を除外して出力

問23

S-023: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。

select store_cd
	,sum(amount) as amount
	,sum(quantity) as quantity
from receipt
group by store_cd

• sum：合計する際にNULLを除外する
• as：カラム名がsumになってしまうのでasで名前設定
• group by：グループ化する

問24

S-024: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）を求め、10件表示せよ。

select
	customer_id,
	max(sales_ymd)
from receipt
group by customer_id
limit 10

• max：カラムの最大値を抽出する(新しい売上年月順)
• group by ：グループ化する
• limit：取得件数を選択

問25

S-025: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も古い売上年月日（sales_ymd）を求め、10件表示せよ。

select 
	customer_id,
	min(sales_ymd)
from receipt
group by customer_id
limit 10;

• min：カラムの最小値
• group by ：グループ化する
• limit：取得件数を選択

問26

S-026: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）と古い売上年月日を求め、両者が異なるデータを10件表示せよ。

select customer_id,
	max(sales_ymd),
	min(sales_ymd)
from receipt
group by customer_id
having max(sales_ymd) != min(sales_ymd)
limit 10;

• max：カラムの最大値を取得
• min：カラムの最小値を取得
• group by：グループ化する
• having：グループに値する条件式を追記
• limit ：取得件数を選択

問27

S-027: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、降順でTOP5を表示せよ。

select
	store_cd,
	avg(amount) as avg_amount
from receipt
group by store_cd
order by avg_amount desc
limit 5;

• AVG：平均値を求める
• group byとavg：グループ化事に平均値を取得
• order by：(デフォルトだと昇順),descをつけると(昇順)

問28

S-028: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

select 
	store_cd,
	percentile_cont(0.5) within group(order by amount) as amount_median
from receipt
group by store_cd
order by amount_median desc
limit 5;

• PERCENTILE_CONT()：指定したパーセントカウントの引数をとり該当のデーターを返す
• WITHIN GROUP(ORDER BY カラム名)：ソート順に並び替える(昇順,降順)

問29

S-029: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求め、10件表示させよ。

select 
	store_cd,
	mode() within group (order by product_cd) as product_cd_
from 
	receipt
group by
	store_cd
order by 
	store_cd
limit 10;

• mode() within group (order by カラム名) ：mode関数で最頻値を取得する
• as：名前を変更する
• group by：グループ化
• order by：ソート順に並び替える(デフォルトは昇順,desc→降順)

問30

S-030: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の分散を計算し、降順で5件表示せよ。

select
	store_cd,
	var_pop(amount) as vars_amount
from receipt
group by store_cd
order by vars_amount desc 
limit 5;

• var_pop()：母標準分散を返します

• var_samp()：標本分散を返します

• VARIANCE()：母標準分散を返します

• https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_std

• percentile_cont()：指定された値の%に相当する値を返す

• overではなくwithin group(order by カラム名)でソート順を指定する

• https://cs.wingarc.com/manual/drsum/5.5/ja/UUID-aba632ca-4a60-7689-1cef-7a5e8ae4a0f0.html

参考資料