2019.08.18
SQLのgroup byサンプルコード集 count、like、join等の組み合わせ例
TOPICS
SQLのgroup byのサンプルコードを集めてみました。
グルーピングして集計したり、グループ内の最大値、最小値を求める際に便利です。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
group byで集計するサンプルコード
１つの項目を使って、group byで集計する例
部署番号(dept_no)と、社員番号(emp_no)を格納したテーブル、dept_empを使って集計してみましょう。
部署ごとの社員数を抽出してみましょう。
dept_noごとのレコードの数をgroup byで集計することで求められます。なお、在籍中の社員のみ対象とするために、to_date（在籍期間終了日）が”9999-01-01″の社員のみという条件を指定します。
|
1 2 3 4 5 6 7 8 9 |
SELECT dept_no, count(*) FROM dept_emp WHERE to_date = "9999-01-01" group by dept_no |
結果はこうなります。並びは、dept_no順にソートされます。
結果
group byの集計をjoin（結合）する例
せっかく集計したのですが、どの部署に何人いるのかわかりにくいので、テーブルdepartmentsと結合して、部署名（dept_name）を表示させましょう。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT departments.dept_name, count(*) FROM dept_emp left join departments ON dept_emp.dept_no = departments.dept_no WHERE to_date = "9999-01-01" group by departments.dept_name |
結果はこうなります。並びは、dept_nameでソートされます。
結果
likeを使ったワイルドカード指定のgroup by集計の例
テーブルtitles（肩書）を使って、ワイルドカード指定でgroup by集計をしてみましょう。
それぞれの肩書を持つ人数を抽出してみます。
titlesテーブルには、社員の過去の肩書も含まれていて、to_dateが”9999-01-01″のものが最新の肩書になります。
まず、単純にtitleで集計するとこうなります。
|
1 2 3 4 5 6 7 8 9 |
SELECT title, count(*) FROM titles WHERE to_date="9999-01-01" group by title |
結果はこうなります。
結果
「Assisttant Engineer」と「Engineer」と「Senior Engineer」、「Staff」と「Senior Staff」を、それぞれ一つにまとめて集計してみましょう。
それぞれlikeで”%Engineer%”、”%Staff%”とワイルドカード指定すればいけそうですね。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT CASE WHEN title like '%Engineer%' THEN 'Engineer' WHEN title like '%Staff%' THEN 'Staff' ELSE title END as Katagaki , count(*) FROM titles WHERE to_date="9999-01-01" group by Katagaki |
結果はこうなります。
結果
CASEは、項目に条件分岐を含むプログラム処理的な動きをさせることができます。
group byでmax、min(最大最小）・平均・合計を求める例
salaries(給与）テーブルを使って、部署ごとに、もっとも高い給料をもとめてみましょう。
salariesテーブルには、各社員の過去の時点の給与も含まれているため、to_date=”9999-01-01″の、最新の給与のみ対象とします。
また、部署名を表示させるため、emp_dept(社員番号と部署番号テーブル）、departments(部署テーブル）を結合します。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT departments.dept_name as bushomei, max(salary) as max_kyuuryo FROM salaries left join dept_emp ON dept_emp.emp_no = salaries.emp_no left join departments ON departments.dept_no = dept_emp.dept_no WHERE salaries.to_date="9999-01-01" group by bushomei |
結果はこうなります。もっとも給与が高い社員がいるのは、Sales（営業部）でした。
同様に、「 max(salary) as max_kyuuryo 」の部分を変更すれば、平均、最小、合計を求めることができます。
group byにminを組み合わせた例
minを使って、各部署の給与がもっとも安い人を求めました。
|
1 |
min(salary) as min_kyuuryo |
各部署とも、それほど大きな差はないようです。
group byにavgを組み合わせた例
avgを使って、各部署の平均給与を求めました。
|
1 |
avg(salary) as avg_kyuuryo |
平均で見ても、営業部の給料は高め。
group byにsumを組み合わせた例
sumで各部署の給与合計を求めました。１年に支払われる給与合計がわかります。
|
1 |
sum(salary) as sum_kyuuryo |
人数が多いため、Development（開発）部署の給与合計がもっとも高くなりました。
group byで最新のレコードを求める例
dept_emp(社員番号、部署番号の紐付け用テーブル）を使って、各部署の一番の新入社員を求めてみましょう。
日付データに対してminとgroup byを組み合わせることで、グループ内の最新日付のデータを抽出できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT departments.dept_name, employees.first_name, employees.last_name, max(dept_emp.from_date) FROM dept_emp left join departments ON departments.dept_no = dept_emp.dept_no left join employees ON dept_emp.emp_no = employees.emp_no WHERE to_date="9999-01-01" GROUP BY departments.dept_name |
結果はこうなります。
group byの集計結果をhavingで絞り込む例
先ほど、group byで部署別の給与平均を出した例を発展させて、「部署別の平均給与（７万ドル超え限定）」を抽出してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT departments.dept_name as bushomei, avg(salary) as avg_kyuuryo FROM salaries left join dept_emp ON dept_emp.emp_no = salaries.emp_no left join departments ON departments.dept_no = dept_emp.dept_no WHERE salaries.to_date="9999-01-01" group by bushomei having avg_kyuuryo > 70000 |
結果はこうなりました。
結果
group by に続けて、havingに条件を指定すると絞り込みができるんですね。
「whereは、集計前の絞り込み条件」「havingは、集計後の絞り込み条件」を指定する、と覚えておくとよいでしょう。
日付範囲指定でgroup by集計する例
employeesテーブルから、年ごとの新入社員数を求めてみましょう。
|
1 2 3 4 5 6 7 |
SELECT count(*) as shinnyushain, date_format(hire_date,'%Y') as year FROM `employees` group by year |
結果はこうなります。
結果
徐々に新入社員の数が減って、なんと2000年には13人。
date_format(hire_date,’%Y’) as yearの箇所を、以下のように変更することで、年月別の集計が可能になります。
|
1 |
date_format(hire_date,'%Y-%M') as year |
結果
ポテパンが提供するサービスについて
本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。
ポテパンフリーランス
.png)
フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面（税金や保険など）についてお答えいたします！フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。
ポテパンキャリア
.png)
エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。
ポテパンキャンプ
ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。
株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください！
.png)
この記事をシェア
関連記事
