SQL 入門 3 ~ サブクエリ(副問合せ) / CASE 式 / Window 関数 を使う 研修コースに参加してみた
今回参加したコースは SQL 入門 3 ~ サブクエリ(副問合せ) / CASE 式 / Window 関数 を使う です。
SEカレッジでは、体系的に SQL がマスターできるよう、「 SQL 入門 1 」「 SQL 入門 2 」とステップを踏んで、より高度な SQL を学びます。
この「 SQL 入門 3 」では、「知っておくと便利な SQL 」をテーマに、わたしの人生で初めて聞いた Window 関数を中心に、その前のステップとなる サブクエリ (副問合せ) 、また、これも初めて知った CASE 式について学びました。実際、便利でした!
また、このコースでは、その SQL が必要になる背景 -> 構文のサンプル -> 練習問題と進むのですが、 「わかる」と「できる」の違いを思い知ることになりました。
「何か一つできるようになっても、またすぐ目の前に分厚い壁があるんだ」の前にも、大きな壁があるんです(鬼滅のセリフを言いたかっただけ)。
では、どのようなコースだったのかレポートします!
もくじ
コース情報
想定している受講者 |
|
---|---|
受講目標 | CASE 式や Window 関数などの、より便利な SQL について理解し、使えるようになる |
講師紹介
このレポートでは久しぶりのご登場、データベース大好きな 坂井 恵さん が登壇されました。
データベースをこよなく愛する講師。日本 MySQL ユーザ会副代表。
MySQL も バージョン 8 になり、執筆された新刊が発売されたとのことでした。
また、もう 2 年前ですが、坂井さんが登場された Web メディアの記事は今でもブックマークする方が多く、はてな ブックマーク数は 1293 ( 2020 年 11 月現在) 。すごい。。
ということで、今日メインで学ぶ Window 関数の事前知識をチャットでアンケートして、コースがスタートしました。
これまでの SQL 入門
繰り返しになりますが、このコースは「 SQL 入門 1 」「 SQL 入門 2 」までを受講された、もしくはそれ相当の知識が前提でコースが進みます。
ということで、これまでに学んだことをザッと振り返ります。
-
これまで学んだことで出来ること
- テーブル A にレコードが何件あるか調べて
- テーブル A の全件を表示してみて
- テーブル A から、 x, y, z のカラムを表示して。ただし z は金額なので、単位を 100 万にして小数第一位まで
- テーブル A から、カラム x ごとに金額 z の合計額を算出して
- テーブル A にカテゴリコードのカラムがあるから、カテゴリテーブルと結合して、カテゴリ名の入った一覧を作っておいて
皆さん、パッと思いつきますか? わたしは 3. まではだいたいわかって、 4. はこれを使うんだろうな、と思いながら、ちょっとググってみたりしながら、、、はい、入門 2 から出直します。
/* 1. の答え */
SELECT COUNT(*) FROM tblA;
/* 2. の答え */
SELECT * FROM tblA;
/* 3. の答え */
SELECT x,y,ROUND(z,2) z FROM tblA;
/* 4. の答え */
SELECT x, SUM(z) sum_z FROM tblA GROUP BY x;
/* 5. の答え */
SELECT a.xx, a.yy, c.nameFROM tblAa LEFT OUTER JOIN tblCatc ON (a.cat_cd=c.cat_cd);
ということで、坂井さんから改めて、これまで学んだ SELECT 文を紹介いただきました。
SELECT [抽出カラム群]
FROM [テーブル名1]
LEFT OUTER JOIN [テーブル名2]
WHERE [抽出条件]
GROUP BY [集約条件]
HAVING [結果に対する抽出条件]
ORDER BY [ソート順]
サブクエリ(副問合せ)
Window 関数に入る前に、関連するサブクエリを解説いただきました。
- クエリの中に、別のクエリを書くこと
- 日本語では 副問合せ と呼ぶ
例題でやってみます。
1000 円以上の買い物をしたことがある cust を抽出してください。
- まずはサブクエリを使わないやり方
- 使うテーブル
mysql> select * from cust; +----+--------------+ | id | name | +----+--------------+ | 1 | 顧客A | | 2 | お客様B | | 3 | 取引先C | +----+--------------+ 3 rows in set (0.00 sec) mysql> select * from sales; +-----+---------+------------+--------+ | id | cust_id | dt | amount | +-----+---------+------------+--------+ | 101 | 1 | 2018-09-10 | 1500 | | 102 | 2 | 2018-09-10 | 1800 | | 103 | 1 | 2018-09-15 | 900 | | 104 | 2 | 2018-09-23 | 2500 | | 105 | 2 | 2018-09-25 | 900 | | 106 | 3 | 2018-09-25 | 600 | +-----+---------+------------+--------+ 6 rows in set (0.01 sec)
- sales テーブルから 1000 円以上の買い物をしたことがある cust_id を抽出
mysql> select distinct cust_id from sales where amount>=1000; +---------+ | cust_id | +---------+ | 1 | | 2 | +---------+ 2 rows in set (0.00 sec)
- cust テーブルから該当の cust_id から情報を出す
mysql> select * from cust where id in (1,2); +----+--------------+ | id | name | +----+--------------+ | 1 | 顧客A | | 2 | お客様B | +----+--------------+ 2 rows in set (0.00 sec)
- 使うテーブル
- この
(1, 2)
に値が入ってほしいので、この () 内に最初に実行したクエリを入れるmysql> select * from cust where id in -> (select distinct cust_id from sales where amount>=1000); +----+--------------+ | id | name | +----+--------------+ | 1 | 顧客A | | 2 | お客様B | +----+--------------+ 2 rows in set (0.01 sec)
ただし、サブクエリを埋め込むときは、サブクエリがどんな値を返すのか、しっかり意識するように補足頂きました。
/* [ ] 内にサブクエリが入る */
SELECT xxx, [(単一カラムの)複数レコード]
FROM [テーブル]
WHERE xxx = [単一の値]
AND xxxx IN [複数レコード]
where id=(単一の値)
のようなものwhere id in (複数レコード)
from (テーブル)
ちなみに SQL の戻りはすべてテーブルで返ってくるので、 FROM (テーブル) ができます。
と、コース中に SQL を見たり書いていると、どうも書く順番がわかりにくかったので調べてみたところ、いいブログ記事を見つけました。
SQLの論理形式 – ミックのブログ より引用
SQLの実行順序は、
FROM → WHERE → GROUP BY → HAVING → SELECT (→ ORDER BY)
です。
「なるほど!」と唸って、書いた方がミックさんだったので、またまた「なるほど!!」とうなりました。
ちなみに、なんで ORDER BY は蚊帳の外なんだろう。
サブクエリの注意点
- 入れ子だらけになるので、読みにくい
- パフォーマンスも悪くなる
- JOIN で書けるか考えてみる
なるほど、先程の例題も JOIN でも書けますものね。
Window 関数(ウインドウ関数)
続いて、本題の Window 関数です。
どういうものなのか坂井さんが解説されたのですが、「一行で書くと、何を言っているのかわからないと思います」とのこと。
- 集約しないグルーピング関数のこと
- え ???
- 他のレコードとの関係を持ち込んだもの (????)
ちょ、おま、なに言って (ry
というのはさておき、、、順を追って説明いただきました。
その前に GROUP_BY
まずは集約といえば GROUP BY 。まずはこれをサッと復習します。
- 集約した結果を 1 レコードにまとめる
mysql> select region_name, sum(total_land_area) tla -> from pref_info -> group by region_name; +-----------------+-------+ | region_name | tla | +-----------------+-------+ | 北海道地方 | 83424 | | 東北地方 | 66948 | | 関東地方 | 32430 | | 中部地方 | 66806 | | 近畿地方 | 33125 | | 中国地方 | 31920 | | 四国地方 | 18804 | | 九州地方 | 44512 | +-----------------+-------+ 8 rows in set (0.00 sec)
ふむふむ。
本題の Window 関数
- GROUP BY のように 1 レコードにしないのが Window 関数
なるほど!確かに GROUP BY は 1 レコードにしてました。
先の sales テーブルの右側にその日ごとの合計金額を出してください。
- まずは追加するカラムをどこに書くか考える
mysql> select id, cust_id , dt, amount, "追加カラム" from sales; +-----+---------+------------+--------+-----------------------+ | id | cust_id | dt | amount | 追加カラム | +-----+---------+------------+--------+-----------------------+ | 101 | 1 | 2018-09-10 | 1500 | 追加カラム | | 102 | 2 | 2018-09-10 | 1800 | 追加カラム | | 103 | 1 | 2018-09-15 | 900 | 追加カラム | | 104 | 2 | 2018-09-23 | 2500 | 追加カラム | | 105 | 2 | 2018-09-25 | 900 | 追加カラム | | 106 | 3 | 2018-09-25 | 600 | 追加カラム | +-----+---------+------------+--------+-----------------------+ 6 rows in set (0.00 sec)
- この追加カラムに sum を入れたいので、ここに window 関数を入れる
mysql> select id, cust_id , dt, amount, -> sum(amount) over (partition by dt) sum_am -> from sales; +-----+---------+------------+--------+--------+ | id | cust_id | dt | amount | sum_am | +-----+---------+------------+--------+--------+ | 101 | 1 | 2018-09-10 | 1500 | 3300 | | 102 | 2 | 2018-09-10 | 1800 | 3300 | | 103 | 1 | 2018-09-15 | 900 | 900 | | 104 | 2 | 2018-09-23 | 2500 | 2500 | | 105 | 2 | 2018-09-25 | 900 | 1500 | | 106 | 3 | 2018-09-25 | 600 | 1500 | +-----+---------+------------+--------+--------+ 6 rows in set (0.00 sec)
- OVER が出れば Window 関数だと思えばよい
- partition: パーティション。区切りのこと
PARTITION BY [集計する対象(カラム)]
: 値でグループ分け
なるほど、これなら別テーブルを作ったり、 VIEW を作ったりせず、 SQL だけで出来るので、このあと分析したりすることもできそうです。便利!
さらに応用として、 PARTITION BY のようにグルーピングした 1 つ 1 つをフレームと言いますが、それを PARTITION BY ではなく ROWS や RANGE で区切るやり方も教えて頂きました。
(が、私にはちょっと難しすぎて、説明ができないので) 知りたい方はぜひコースを受講してください!
CASE 式
知っておくと便利な SQL 。続いては、 CASE 式です。
- SQL の if 文や swith 文のようなもの
- アプリケーション側でデータを取得して比較するより、データベース側でやったほうが速い
- パフォーマンスは悪くない
- 坂井さんのご経験では数百万レコードでも変わらない
- なお、一致した条件があれば、以降は処理しない
- 構文
CASE WHEN 条件式1 THEN 値 WHEN 条件式2 THEN 値 /* 複数条件 */ ELSE 値 END CASE (式) WHEN 値1 THEN 値 WHEN 値2 THEN 値 /* 複数条件 */ ELSE 値 END
2000 円以上の売上なら 高、 1000 円以上なら 中、 1000 円より下なら 低とランクを出す表を出力してください。
mysql> select id, cust_id, dt, amount,
-> case
-> when amount>=2000 then "高"
-> when amount>=1000 then "中"
-> else "低"
-> end sales_rank
-> from sales;
+-----+---------+------------+--------+------------+
| id | cust_id | dt | amount | sales_rank |
+-----+---------+------------+--------+------------+
| 101 | 1 | 2018-09-10 | 1500 | 中 |
| 102 | 2 | 2018-09-10 | 1800 | 中 |
| 103 | 1 | 2018-09-15 | 900 | 低 |
| 104 | 2 | 2018-09-23 | 2500 | 高 |
| 105 | 2 | 2018-09-25 | 900 | 低 |
| 106 | 3 | 2018-09-25 | 600 | 低 |
+-----+---------+------------+--------+------------+
6 rows in set (0.00 sec)
ちなみにこのランクを使って集計することも可能です。
mysql> select case when amount>=2000 then "高"
-> when amount>=1000 then "中"
-> else "低"
-> end sales_rank,
-> count(*) cnt
-> from sales;
-> group by sales_rank
+-----------+-----+
| kakakutai | cnt |
+-----------+-----+
| 中 | 2 |
| 低 | 3 |
| 高 | 1 |
+-----------+-----+
3 row in set (0.00 sec)
ただし、別名 (ここでは sales_rank ) を使って GROUP BY できる DBMS とそうでないものがあるので、注意が必要です。
もし別名が使えない場合は、 CASE ~ END をコピーして、 GROUP BY のあとにペーストすると同じことができるとのことでした。
SQL 、何でも出来る感がすごい。。ORM でいいじゃんと思っていた私が阿呆でした。。
このあと、また演習問題に取り組み、「理解できた」と「出来る」の差を痛感したのでした。。
NULL の問題
続いて、最後に、知っておきたいこととして NULL のお話です。
- NULL とは状態のこと
- 値ではない
- 値ではないので演算できない
NULL を論理演算してみる
mysql> select (true and null) TandN, (false and null) FandN;
+-------+-------+
| TandN | FandN |
+-------+-------+
| NULL | 0 |
+-------+-------+
1 row in set (0.00 sec)
mysql> select (true or null) TorN, (false or null) ForN;
+------+------+
| TorN | ForN |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> select (not true) nT, (not false) nF, (not null) nN;
+----+----+------+
| nT | nF | nN |
+----+----+------+
| 0 | 1 | NULL |
+----+----+------+
1 row in set (0.00 sec)
法則性がわかりにくく、これはちょっと摩訶不思議な結果です。。
また、実際に結果が狂うことがある SQL を紹介いただきました。
mysql> SELECT * FROM item_list WHERE item_cd NOT IN (SELECT used_item_cd FROM used_items);
+------+---------+-----------+
| id | item_cd | item_name |
+------+---------+-----------+
| 12 | 36 | item36 |
+------+---------+-----------+
1 row in set (0.00 sec)
続いて、 NULL のあるレコードを追加して、また同じ SQL を実行してみます。
mysql> INSERT INTO used_items VALUES (4, '2020/04/04', NULL, 4);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM item_list WHERE item_cd NOT IN (SELECT used_item_cd FROM used_items);
Empty set (0.01 sec)
ナント、データが無くなってしまった。。
また、その他にも
WHERE 句で NOT (id=15 AND NULL) のように指定すると結果がおかしくなったり、
NULL を含むカラムで ORDER BY すると、 NULL があるレコードが上位( RDBMS によっては最後)になったり、
NULL の挙動を色々と紹介いただきました。
この NULL トラブルの回避策を解説いただきました。
はい、気軽に NULL 許容して、と開発者に言わないようにします。
最後は今日学んだ知識を使って、応用問題に取り組みました。
もうホント、「出来ない」です。
演習を終えて、坂井さんから「今日は範囲外だった CTE ( Common Table Expressions 共通式) という便利機能もあるので、これもぜひ調べてみてください」と教えて頂き、コースは修了しました。
そして、調べて理解はしました!(できるとは言っ (ry )
MySQL8.0 の共通テーブル式(CTE)を使ってみよう | スマートスタイル TECH BLOG|データベース&クラウドの最新技術情報を配信
まとめ
SQL を体系的に学ぶシリーズの 3 つめとして、「知っておくと便利な SQL 」をテーマに、サブクエリ、 Window 関数、 CASE 式、 NULL の問題と様々に学びました。
確かに、とてもそれぞれ便利そうでした!
また、それだけでなく、このコース内で色々行った演習問題がとても貴重でした。
やはり、研修を受講すると、知った気になってしまうので、演習して「わかった」けど「できない」ことが多く、それで気付けることが山ほどありました。
こういった「できる」かどうかがわかるスキル認定コースのようなものもありなんだろうなぁ、と思います。
「データベース、チョットできる」と言えるようになりたいアプリケーション開発者、データベースエンジニアにはとてもオススメのコースです!!
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。