【QuestHR】第5回データ分析リテラシー基礎研修 / 5Data Literacy

ピボット的な集計表をSQLだけで作る。値フィルターに相当するHAVINGの勘所まで網羅する。

Transcript

1. データの集計とグループ化 第5回 QuestHR株式会社 データ分析リテラシー 基礎研修 ExcelのピボットテーブルをSQLで実現する
2. 本日の学習目標 1. 集計関数を使い、データの件数・合計・平均などを計算できるようになる。 2. GROUP BYを使い、「部署ごと」のようにデータをグループ化して集計で きるようになる。 3. HAVINGを使い、集計した結果をさらに絞り込めるようになる。
3. 本日の アジェンダ 1. 前回の復習 2. なぜ集計が必要か？ 3. 演習準備 4. 講義①：集計関数 5. 講義②：グループ化 6. 講義③：集計結果の絞り込み 7. 総合演習 8. まとめ
4. 前回の復習 前回は、複数のテーブルを繋ぐ「JOIN」を学びました！ • INNER JOIN : 両方のテーブルに存在するデータだけを結合 • LEFT JOIN : 左のテーブルを基準に、全てのデータを結合 → これにより　「誰が、どんな評価を受けたか」という詳細なリス　 トを作れるようになった！
5. なぜ集計が必要か？
6. 演習準備
7. 講義① 集計関数
8. まずはグループ化する前に 列全体を計算する基本的な関数を覚えよう！ • COUNT(列名) : データの件数を数える ◦ COUNT(*)と書くと、NULLを含む全ての行数を数える • SUM(列名) : 数値の合計を計算する • MAX(列名) : 数値の最大値を見つける • MIN(列名) : 数値の最小値を見つける • AVG（列名） : 数値の平均を計算する 列全体を一つの数値に要約する
9. ハンズオン① 集計関数を書いてみよう！
10. お題 scoresテーブルを使って 2022年度の評価スコアの「合計点」と 「平均点」を計算してみましょう！ 集計関数を書いてみよう！
11. 講義② グループ化
12. GROUP BYは ピボットテーブルの「行」に、どの項目（例：部署）を 入れるかを決めるのと同じ！ 「グループ分け」と「名札作り」
13. Step1 GROUP BY departmentで、まず部署ごとに「グループ分け」する 「グループ分け」と「名札作り」
14. Step2 グループごとに「集計」し、1枚の「名札」に要約する 「グループ分け」と「名札作り」
15. •SELECT句には、GROUP BYで指定 した列と集計関数しか書けない •GROUP BYはWHERE句の後に書く 書き方のルール
16. 講義② グループ化 GROUP BYの基本構文
17. 1. FROM ： 「部屋にいる全社員（employeesテーブル）」を対象にする。 2. GROUP BY ： 「部署（department）ごとに集まってください！」とグループ分けをする。 （ステップ1） 3. SELECT ： 「各グループの部署名と、その人数（COUNT(*)）を書いた名札を作ってくださ い」と命令し、NULLを含む全ての行数を数える。（ステップ2） GROUP BYの基本構文 SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;
18. 実行結果🎉 SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;
19. ハンズオン② GROUP BYを書いてみよう！
20. お題 employeesテーブルとscoresテーブルをJOINし 部署（department）ごとに スコア（score）の平均点を計算してみましょう！ （2022年度の評価のみ対象） GROUP BYを書いてみよう！
21. 講義③ 集計結果の絞り込み
22. HAVING句を使おう！ ピボットテーブルの「値フィルター」と同じ！ ▪WHEREとHAVINGの違い • WHERE : グループ化する前の、元のデータを絞り込む。 • HAVING : グループ化した後の、集計結果を絞り込む。 集計した「後」で、さらに絞り込みたい！
23. 講義③ 集計結果の絞り込み HAVINGの基本構文
24. HAVINGの基本構文 お題 ： 「社員が2名以上いる部署」を調べる SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) >= 2; -- 集計結果に対する条件 1. FROM ： employeesテーブルから全てのデータを読み込む。 2. GROUP BY ： department列の値でデータをグループに分ける。（ここで「営業部グルー プ」「開発部グループ」などが作られる） 3. HAVING : 各グループの名前（department）と、そのグループの行数（COUNT(*)）を表示 する。 4. SELECT : 最終的に残ったグループについて、表示する列を決める。
25. ハンズオン③ HAVINGを書いてみよう！
26. お題 部署別の平均評価スコアを計算し その平均スコアが 85点以上の部署だけを表示してみましょう！ （2022年度の評価のみ対象） HAVINGを書いてみよう！
27. 総合演習
28. お題 employeesテーブルを使い 退職年（resignation_dateの年）ごとに 離職者数を集計してください！ これまでの知識を総動員！
29. 実行結果の確認
30. まとめ
31. ▪できるようになったこと • 集計関数：COUNT, AVG, SUMで、データの要約ができる • GROUP BY：データをグループ化し、グループごとの集計ができる（ピ ボットの「行」） • HAVING：集計結果に対して、さらに条件を指定して絞り込める（ピボット の「フィルター」） • SQLでピボットテーブルのような集計表を作れるようになった！ まとめ
32. 次回予告
33. 第6回 総合演習！ SQLを使った実践データ分析