SQL 入門 2 ～ JOIN / GROUP BY / サブクエリ（副問合せ）で脱入門 研修コースに参加してみた
2020-11-18 更新
今回参加した研修コースは SQL 入門 2 ～ JOIN / GROUP BY / サブクエリ（副問合せ）で脱入門 です。
参加してみると、簡単な SELECT 文を使ったことがある方が、トランザクション、JOIN や GROUP BY、HAVING などの集約関数に加えて、サブクエリまで SQL 文を書いてみて、脱入門する内容でした。
タイトルにある “脱入門” は、まさに私でした。
今までは JOIN が必要なときは他の人に書いてもらったものをコピペ! で何とかやってきましたが、このコースで簡単な結合から順番に体験できたので、とても理解が進みました!
開発や DBA だけでなく、ビジネス職も SQL を書く時代なので、そういった方にもとてもオススメです!
では、どんな内容だったのかレポートします!!
この記事は 2019 年 3 月 27 日に公開され、一部情報とスタイルを更新しています
もくじ
コース情報
| 想定している受講者 |
|
|---|---|
| 受講目標 | RDBMS 上での SQL によるデータ操作について、実務レベルで必要なより多くのことを理解・できるようになる。 |
講師紹介
データベース大好きな 坂井 恵さん が登壇されました。
データベースをこよなく愛する講師。日本 MySQL ユーザ会副代表。
そういえば、先日、坂井さんが「第 6 回 OSC アワード」を受賞されてらっしゃいました! おめでとうございます!!
受賞理由：日本 MySQL ユーザ会として、全国各地の OSC に出展し、MySQL の認知・普及に務めるだけでなく、他のプロダクトとの交流を積極的に行い、オープンソースコミュニティの活性化に寄与しているため。
(上記記事より引用)
坂井さんと言えば MySQL ですね。
個人的には SE カレッジで、坂井さんに MySQL の安全なバージョンアップやアップデートのやり方を聞いてみたいところです。5.7 -> 8.0。。
基本的な SQL
まずはサラッと基本をおさらいします。
- 大きく2つ
- 更新
- INSERT
- UPDATE
- DELETE
- 参照系
- SELECT
- WHERE 句
- 条件指定
- =
- <
- LIKE
- <> // 値除外
- 複数条件
- [条件1] AND [条件2] AND ･･･
- [条件1] OR [条件2] OR ･･･
- 条件指定
- WHERE 句
- SELECT
- 更新
今日の目標
入門レベルを超えられそうな目標を挙げていただきました!
トランザクションをやってみましょう
これは以前の坂井さんに登壇頂いたコースでも扱っていますので、ぜひそのレポートをご覧ください!
研修コースに参加してみた
JOIN
つづいて、SQL の最初の難関 JOIN です。
1. ただ並べただけではわからない
select * from pref, area;
+-----------+--------------+---------+----+--------------+
| pref_code | pref_name | area_id | id | area_name |
+-----------+--------------+---------+----+--------------+
| 06 | 山形県 | 2 | 2 | 東北地方 |
| 06 | 山形県 | 2 | 3 | 関東地方 |
| 06 | 山形県 | 2 | 5 | 近畿地方 |
| 12 | 千葉県 | 3 | 2 | 東北地方 |
| 12 | 千葉県 | 3 | 3 | 関東地方 |
| 12 | 千葉県 | 3 | 5 | 近畿地方 |
| 13 | 東京都 | 3 | 2 | 東北地方 |
| 13 | 東京都 | 3 | 3 | 関東地方 |
| 13 | 東京都 | 3 | 5 | 近畿地方 |
| 27 | 大阪府 | 5 | 2 | 東北地方 |
| 27 | 大阪府 | 5 | 3 | 関東地方 |
| 27 | 大阪府 | 5 | 5 | 近畿地方 |
| 30 | 和歌山県 | 5 | 2 | 東北地方 |
| 30 | 和歌山県 | 5 | 3 | 関東地方 |
| 30 | 和歌山県 | 5 | 5 | 近畿地方 |
+-----------+--------------+---------+----+--------------+2. area_id と id が一致するものは意味がありそう
select * from pref, area
where pref.area_id = area.id;
+-----------+--------------+---------+----+--------------+
| pref_code | pref_name | area_id | id | area_name |
+-----------+--------------+---------+----+--------------+
| 06 | 山形県 | 2 | 2 | 東北地方 |
| 12 | 千葉県 | 3 | 3 | 関東地方 |
| 13 | 東京都 | 3 | 3 | 関東地方 |
| 27 | 大阪府 | 5 | 5 | 近畿地方 |
| 30 | 和歌山県 | 5 | 5 | 近畿地方 |
+-----------+--------------+---------+----+--------------+いい感じですね。ただ、いまの現場でこんな SQL は普通、書きません。なぜなら、JOIN 構文があるからです!
3. JOINで書いてみる
select * from pref
join area on (pref.area_id = area.id);
+-----------+--------------+---------+----+--------------+
| pref_code | pref_name | area_id | id | area_name |
+-----------+--------------+---------+----+--------------+
| 06 | 山形県 | 2 | 2 | 東北地方 |
| 12 | 千葉県 | 3 | 3 | 関東地方 |
| 13 | 東京都 | 3 | 3 | 関東地方 |
| 27 | 大阪府 | 5 | 5 | 近畿地方 |
| 30 | 和歌山県 | 5 | 5 | 近畿地方 |
+-----------+--------------+---------+----+--------------+いえーい、JOINきました。先ほどと違って、繋いでいる感がありますね。
外部結合
JOIN には 2 種類あり、先程やったのは内部結合 ( INNER JOIN ) と呼ばれるものです。結合先のカラムにすべてデータがあった場合にできます。
- INNER JOIN
- LEFT OUTER JOIN
これからやるのは LEFT OUTER JOIN です。
これはくっつける先のテーブルのカラムにデータがない場合 (たとえば null) でもくっつける、外部結合です。
select * from pref
left outer join area on (pref.area_id = area.id);
+-----------+--------------+--------------+
| pref_code | pref_name | area_name |
+-----------+--------------+--------------+
| 06 | 山形県 | 東北地方 |
| 12 | 千葉県 | 関東地方 |
| 13 | 東京都 | 関東地方 |
| 27 | 大阪府 | 近畿地方 |
| 30 | 和歌山県 | 近畿地方 |
| 40 | 福岡県 | NULL |
+-----------+--------------+--------------+集約処理
今度は取得したデータから平均したり、合計したりなどです。
GROUP BY
以下のテーブルで体験してみます。
+----+------+--------------------------+-------+
| id | code | name | price |
+----+------+--------------------------+-------+
| 1 | 101 | みそラーメン | 800 |
| 2 | 101 | しょうゆラーメン | 850 |
| 3 | 101 | しおラーメン | 700 |
| 4 | 101 | チャーシューメン | 950 |
| 5 | 102 | チャーハン | 880 |
| 6 | 102 | 叉焼丼 | 930 |
| 7 | 103 | 味玉 | 120 |
| 8 | 103 | チャーシュー | 150 |
| 9 | 103 | 替玉 | 100 |
| 10 | 103 | のり | 100 |
+----+------+--------------------------+-------+カウントや合計などをやってみます。
select code, count(*) , sum(price) from grpsample group by code;
+------+----------+------------+
| code | count(*) | sum(price) |
+------+----------+------------+
| 101 | 4 | 3300 |
| 102 | 2 | 1810 |
| 103 | 4 | 470 |
+------+----------+------------+続けて、他にもやってみます。
select code, min(price), max(price), avg(price) from grpsample group by code;
+------+------------+------------+------------+
| code | min(price) | max(price) | avg(price) |
+------+------------+------------+------------+
| 101 | 700 | 950 | 825.0000 |
| 102 | 880 | 930 | 905.0000 |
| 103 | 100 | 150 | 117.5000 |
+------+------------+------------+------------+HAVING
group by で集約したテーブルに対して、さらに条件を指定して抽出するときに HAVING が使えます。
select code, max(price) from grpsample group by code;
+------+------------+------------+------------+
| code | min(price) | max(price) | avg(price) |
+------+------------+------------+------------+
| 101 | 700 | 950 | 825.0000 |
| 102 | 880 | 930 | 905.0000 |
| 103 | 100 | 150 | 117.5000 |
+------+------------+------------+------------+ここから 500 円以上のものを抽出します。
select code, max(price) from grpsample group by code
having max(price) >= 500;
+------+------------+
| code | max(price) |
+------+------------+
| 101 | 950 |
| 102 | 930 |
+------+------------+いろいろな関数
SUM や AVG などの他にも関数があります。
関数は SELECT 文や WHERE 句の中で使います。
以下は日付や時間を扱う関数を使って、いまの日時とそこから分と、明後日(assate) を出すSELECT文です。
select now(), minute(now()) min, date_add(now(), interval 2 day) asatte;
+---------------------+------+---------------------+
| now() | min | asatte |
+---------------------+------+---------------------+
| 2019-03-28 14:16:50 | 16 | 2019-03-30 14:16:50 |
+---------------------+------+---------------------+サブクエリ
「副問い合わせ」とも言われ、クエリの中に別のクエリを書けます。
「1000円以上の買い物をしたことがあるお客様を抽出したい」
1. sales テーブルから amount が 1000 円より高い顧客を抽出
select cust_id from sales where amount>1000;
+---------+
| cust_id |
+---------+
| 1 |
| 2 |
+---------+2. 抽出された cust_id が 1, 2 の人のレコードを表示したい
先の SQL を () に括って処理する
select * from cust where id IN (
select cust_id from sales where amount>1000
);
+----+--------------+
| id | name |
+----+--------------+
| 1 | 顧客Ａ |
| 2 | お客様Ｂ |
+----+--------------+応用練習
3 つのテーブルをくっつけてやるものでした!
この応用演習を行い、最後に坂井さんから解説いただき、このコースは修了しました。
まとめ
このコースでは簡単な SELECT 文を使ったことがある方が、トランザクション、JOIN や GROUP BY , HAVING などの SQL 文を書いてみて、脱入門する内容でした。
私を狙ったかのようなコースでした。
いままで単一テーブルで SELECT 文を 3 行ほど書くだけのレベルで、 JOIN が必要なものは極力参照しない or エンジニアに書いてもらったものをコピペ! でやってきましたが、簡単な結合から手順を追って体験できたので、とても理解が進みました!
実際やってみると、とっても便利でした!! (ちょっとNULL許容しているカラムが多いので、LEFT OUTER JOIN でしたが。。坂井さんに怒られるやつです)
DBA や開発だけでなく最近はビジネス職も BigQuery を叩く時代なので、そういった方にもとてもオススメです!!
と思ってBigQuery 見てみると、もう少し強いやつでした。。UNIONってなんだろう
標準 SQL クエリ構文 | BigQuery | Google Cloud
label SE カレッジの無料見学、資料請求などお問い合わせはこちらから!!
label SEカレッジを詳しく知りたいという方はこちらから !!
IT専門の定額制研修 月額28,000円 ～/ 1社 で IT研修 制度を導入できます。
年間 670 講座をほぼ毎日開催中!!
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。