始めに
- この記事は筆者がデータサイエンス100本ノックSQL編を実施するにあたって、学んだことをメモ書きしたものです。あくまで筆者の学習履歴を残すものになります。
- ここから分散など統計の知識が必要となる、統計学の知識も少しずつ必要になってくる。学生時代に学んだはずの数学の知識を忘れている筆者には徐々に辛くなってくる。統計学も勉強必要と痛感。
本編
21
- COUNTの使い方。
- COUNT(1)のように1を入れる。
22
- DISTINCTを覚える。重複を削除することが出来る構文。
23
- GROUP BY で特定の項目毎に出力結果を表示することが出来る。
- SUM（）で合計が出来る。
24
- MAXで最大値をとってくる。
- この回答では、各Custom_id毎の最大値をもってきている。
- データ全体の中で売上日が最大のものを持ってきている訳ではない。（最初勘違いしていた）
25
- MAXで最小値を昇順でとってこれる
- 24も25も出力結果同じやけど大丈夫か？
- 顧客ID:売上日の数=1:1のものが多くMAXだろうと、MINだろうと結果が変わらない。
- 次の#26を見れば納得。
26
- HAVING句GROUP BYでグルーピングしたものに対して条件を追加したい時に使う。WHEREだとGROUP BYにかからないので注意。使い分ける。
- !=は否定を意味する。
27
- AVGで平均を算出することができる。
- ORDER BY ～DESCも覚える。
28★難しい
- 構文
- PERCENTILE_CONT(percent) WITHIN GROUP(ORDER BY expression [ASC|DESC]) OVER( [PARTITION BY partition_list])
- このサイトがわかりやすい
- ざっくり解説。上位50%に相当する行の値をGROP BYでグループ化したデータの中からいい感じに取り出している。
- PERCENTILE_CONT(値)：指定したパーセントタイルの値を引数にとり、該当のデータを返す。
- OVER句ではなく、WITHIN GROPU(ORDER BY カラム)でソート順を指定する必要がある。
- 中央値自体への馴染みが自分はなさすぎる。統計に知識が乏しい。
29
- mode関数
- #28同様にWITHIN GROUPでソートするのがポイント。
30★難しい
- VAR_POP, VARIANCE：標本分散を返す
- https://cs.wingarc.com/manual/drsum/5.6/ja/UUID-2a86a5c4-f9b8-1408-753c-3574f790a031.html
- https://bellcurve.jp/statistics/course/8614.html
- 母平均と標本平均、不偏分散と標本分散【マーケターのためのデータサイエンスの時間】～5限目～
31
- 標準偏差の使い方を学ぶ。
- 標準偏差はざっくりいうと、数値のばらつき具体を指すもの。
- 標準偏差が小さいと数値がまとまっている、大きいとバラつきがあることになる。
- 標準偏差とは？意味から求め方、分散との違いまでわかりやすく解説
32
- 問題の意味が理解できない。
- 上から並べて25%の位置にあるデータ、50%にあるデータ・・・それぞれを探してねという質問だと思う。きっと。
- PERCENTILEの使い方を学ぶ。
- 順番に並べてと該当データの位置を特定するための関数。
- 分散関数としては2種類ある。
- PERCENTILE_CONT
- PERCENTILE_DISC
- OVER句ではなく、WITHIN GROUPでソート順を指定しないといけない。
33
- HAVING句を学ぶ問題。
- ASで別名をつけているが、その別名は使えないので注意。
- 再度HAVING句の中で平均を出す必要がある。
- SELECTではあくまでどんな名前で表示しますか？しか指定できていないと理解。
34
- WITH サブクエリ名 AS(SELECTなど)で新たにサブクエリをまとめられる。
- SUMなど関数を使用して作られた列を操作したい時にWITHを使う。
- 今回の場合で言えばSUM(amount)で売上金額を合計したい状況で、その他の抽出条件としてZ始まりではなく、customer_id毎という条件をWITHで付している。
- サブクエリはもっと勉強したほうがいいい。
- サブクエリをの結果（今回で言えば、Z始まりじゃなくてcustomer_id別になったデータの作成をサブクエリ（WITH）で作っている。
35
- 34に平均以上という条件を付したもの。
- 34同様にサブクエリでミニテーブルをまずは作り、そのミニターブルに対してクエリを実行するイメージ作る。
- 平均以上は以下のコードで条件抽出
WHERE sum_amount >= (SELECT AVG(sum_amount) FROM customer_amount)
- 条件抽出の文章内に、SELECT、FROMが入っているのが特徴的。
36
- 内部結合の問題
- ここからreceipt以外にstoreのテーブルを使うので、storeもテーブルとして読み込めているか注意すること。
- JOIN テーブル名
- ON テーブル名.カラム名 = テーブル名.カラム名
- で結合するイメージとなる。
37
- 36と同じ。結合するテーブルが異なるだけ。
38
- 急に問題が難しくなる。
- WITHを使うのとLEFT JOINを使うのがポイント
- WITHは変数のイメージ
- SQL WITH句のサンプル | ITSakura https://itsakura.com/sql-with
- WITHの中身の解説
- receiptテーブルのamountカラムをSumで合計し、sum_amountという列名をつける
- かつデータはcustomer_id毎に持ってほしい
- COALESCE
- SQL関数coalesceの使い方と読み方 | ⬢ Appirits spirits https://spirits.appirits.com/doruby/8666/
- 空欄に引数を変える関数
- 今回でいうと、引数のゼロを指定しているので、合計が無いならゼロを返す設定となっているのがわかる。
- これがなければ、合計がゼロのデータはNULLになってしまう
- 読み方はコウアレス
- あとはWHEREで性別が1に絞って、顧客コードZ始まりのものに絞っている。
- WHEREでゼロでないに絞るは難しいのかな？
39
- WITHでまたテーブルを作るタイプ。WITH句
- 今回は38と違って2つのテーブルをWITHで作宇。
- COALESCEが無いとIDの無い顧客が発生する。
40
- CROSS結合とは？
- クロス結合とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 https://wa3.i-3-i.info/word15316.html
参考リンク
- データサイエンス100本ノック 構造化データ加工編 ガイドブック 森谷 和弘(著/文) - ソシム | 版元ドットコム
- 【SQL編】データサイエンス100本ノック【21~40】 - Qiita https://qiita.com/Strix9289/items/a7b813ade174ebb27409