皆さんはSQLのサブクエリ書いてますか？
最近は分析目的でSQLを書く機会が増えていますが、意外と生のSQLを書く機会がなかったので意外と初歩的なところでつまづいたりしました。特に、SQLのサブクエリについての理解がかなり曖昧だったので、この機会にしっかりと向き合ってみようと思い基礎から学び直してみました！
本稿の対応
- ビュー
- サブクエリ
- 相関サブクエリ
環境設定
docker上で真っさらなmysqlを立ち上げ、下記のテーブルを作成しました。
検証環境はgithubにも載せてます。
-- テーブル生成
CREATE TABLE foods
(
id BIGINT NOT NULL,
name VARCHAR(100) NOT NULL,
type VARCHAR(32) NOT NULL,
sales_price INTEGER ,
purchase_price INTEGER ,
created_at DATETIME ,
updated_at DATETIME ,
PRIMARY KEY (id)
);
一旦サンプルとして下記のデータを流し込んでいます。
-- データ生成
INSERT INTO `foods` (`id`, `name`, `type`, `sales_price`, `purchase_price`, `created_at`, `updated_at`)
VALUES
(1, 'うどん', '麺類', 400, 50, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(2, 'そば', '麺類', 500, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(3, 'ラーメン', '麺類', 800, 300, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(4, 'ごはん', 'ご飯類', 200, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(5, 'おにぎり', 'ご飯類', 250, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(6, 'チャーハン', 'ご飯類', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(7, '餃子', 'おかず', 300, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(8, '卵焼き', 'おかず', 400, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(9, 'お茶', '飲み物', 200, 60, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(10, 'ジンジャエール', '飲み物', 250, 80, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
(11, 'ビール', '飲み物', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52');
ビュー
ビューを一言で言うと「仮想テーブル」です。テーブルが実データを保持しているのに対して、ビューが保持しているのはSELECT文そのものを保持しています。
利点としては、データ領域を使う必要がないので節約ができることや、頻繁に使うSELECT文を使い回すことができる点です。例えば、毎回複雑な検索条件で絞った結果を、複数の場所で取り扱うような場合はビューの出番です。汎用的に使うSELECT文をビューにしておくことで、メリットを享受することができます。
ビューの定義方法
今回は、foodsテーブルの種別ごとに個数を表示するビューを作成していきます。
CREATE VIEW sum_foods (type, count_foods)
AS
SELECT type, COUNT(*)
FROM foods
GROUP BY type;
DBを実際に見てみるとViewというテーブルが作られています。
mysql> show full tables;
+-----------------------------+------------+
| Tables_in_development_mysql | Table_type |
+-----------------------------+------------+
| foods | BASE TABLE |
| sum_foods | VIEW |
+-----------------------------+------------+
2 rows in set (0.00 sec)
ただ、データを格納しているわけではなく、foodsテーブルを参照するSELECT文なのでfoodsテーブルの実データが追加されたり更新されると結果も自動的に変更されます。
mysql> select *
-> from sum_foods;
+-----------+-------------+
| type | count_foods |
+-----------+-------------+
| おかず | 2 |
| ご飯類 | 3 |
| 飲み物 | 3 |
| 麺類 | 3 |
+-----------+-------------+
4 rows in set (0.00 sec)
サブクエリ
本題のサブクエリです。
これを一言でいうと「使い捨てのビュー」です。ビューのように仮想テーブルをDBのなかに作るのではなく、一時的に使うために使うビューのことです。先ほどビューを定義するときのAS以降の文章をそのままFROM句に使うと結果を得ることができます。(FROM句内で使うサブクエリをインラインビューと呼んだりもします）
SELECT type, count_product
FROM (SELECT type, COUNT(*)
FROM foods
GROUP BY type) AS foods_sum;
実行結果は下記のようになります。
mysql> SELECT type, count_foods
-> FROM (SELECT type, COUNT(*) as count_foods
-> FROM foods
-> GROUP BY type) AS foods_sum;
+-----------+-------------+
| type | count_foods |
+-----------+-------------+
| おかず | 2 |
| ご飯類 | 3 |
| 飲み物 | 3 |
| 麺類 | 3 |
+-----------+-------------+
4 rows in set (0.00 sec)
スカラサブクエリ
スカラサブクエリは1行しか返さないサブクエリのことです。これを利用して比較演算子の入力値として利用する際に活用することがよくあります。以下は金額の平均値を取得して平均より高い食べ物のみを取得しています。
SELECT *
FROM foods
WHERE sales_price > (SELECT AVG(foods.sales_price)
FROM foods);
スカラサブクエリのルールとして、単一の結果を返さないサブクエリを書いてしまうとエラーとなるので注意が必要です。
相関サブクエリ
上記のように平均より金額よりも高い食べ物を抽出することはサブクエリでできました。今度はさらに**「種別ごとに平均金額より高い食べ物を抽出する」**として見たときに使えるのが相関サブクエリです。まずはSQLを書いてみます。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
FROM foods as T2
WHERE T1.type = T2.type
GROUP BY type);
結果はこうなります。
mysql> SELECT type, name, sales_price
-> FROM foods as T1
-> WHERE sales_price > (SELECT AVG(sales_price)
-> FROM foods as T2
-> WHERE T1.type = T2.type
-> GROUP BY type);
+-----------+-----------------+-------------+
| type | name | sales_price |
+-----------+-----------------+-------------+
| 麺類 | ラーメン | 800 |
| ご飯類 | チャーハン | 400 |
| おかず | 卵焼き | 400 |
| 飲み物 | ビール | 400 |
+-----------+-----------------+-------------+
4 rows in set (0.00 sec)
なんとなく結果が取れていそうですね！
ただ、一見わかりづらいので少し分解して解説していきます。
まず、WHERE文なしのサブクエリをみていきます。
mysql> SELECT AVG(sales_price)
-> FROM foods
-> GROUP BY type;
+------------------+
| AVG(sales_price) |
+------------------+
| 350.0000 |
| 283.3333 |
| 283.3333 |
| 566.6667 |
+------------------+
4 rows in set (0.00 sec)
ふむ、、、種別ごとの平均金額が取れてそうです。
ただこのまま実行してしまうと比較演算子の入力値に複数の値を返すサブクエリとなってしまいます。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (350.0000, 283.3333, 283.3333, 566.6667);
-- これではエラーになってしまう。。。
これを解決するため、WHERE文を追加します。
このWHERE文によって、**「金額と平均金額の比較を、同じ種別ごとに行う」**ようにしています。また、テーブルに別名をつけているのはサブクエリ内部のfoodsテーブルと外側のfoodsテーブルを区別するために付与しています。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
FROM foods as T2
WHERE T1.type = T2.type -- <--これを追加する
GROUP BY type);
一見内部動作が見えづらいですが、実際は商品種別ごとに分割し、それぞれの平均値と比較しているだけです。
また、このWHERE文をサブクエリの外側に書くとエラーになってしまいます。それは、サブクエリの外側からサブクエリ内部のテーブルの別名を参照できないからです。
SELECT type, name, sales_price
FROM foods as T1
WHERE T1.type = T2.type --T2が参照できずエラーになる
AND sales_price > (SELECT AVG(sales_price)
FROM foods as T2
GROUP BY type);
サブクエリはパフォーマンスが悪いのか？
結論、確かにサブクエリを使うとパフォーマンスは良いとはいえないようです。
ビューのところでも説明しましたが、サブクエリはいわば一時的にビューのような仮想テーブルを生成することになります。さらにいうと、このビューにはインデックスも貼られていないため、パフォーマンス自体は通常のクエリと比べると悪いといえます。
そのため、なんでもかんでもサブクエリにするというよりも、集計した結果を条件にしたいなど、サブクエリでなければ実現できない場合時の手段として捉えた上で、使うべきかどうかを考えるようにすると良さそうです。
まとめ
かなり初歩的な内容になりましたが、曖昧になっていた部分が個人的に解消できました。同じくサブクエリをなんとなく使っていた人がいたら、少しでも力になれば幸いです。
最近はパフォーマンス改善を行う機会も多いので、サブクエリのことを意識しつつ知見が溜まったらまた何か書きたいと思います！それではまた！