自然でない相関サブクエリの解消
1. はじめに
MySQLの勉強をしているときの1つの壁として、相関サブクエリが紹介されることがあります。私も最初に相関サブクエリを見たときは何をしているのかよくわかりませんでした。
しかし、それは紹介されていたクエリが自然な発想で考え付く相関サブクエリではなかったからであり、それが明示されていなかったからだと後から思いました。
今回の記事では、自然でない相関サブクエリとは何かを紹介して、それを避けるためにはどうすれば良いのかという話をしていきます。
2. 自然でない相関サブクエリ
説明するにあたって、サンプルデータがあった方が良いので、最初にサンプルデータを生成していきます。
手を動かして確認したい人は、サンプルデータ生成のクエリも書いておくので、そちらをご利用ください。
2.1. サンプルデータ生成
次のサンプルデータを元にクエリを考えていきます。
table: users
| id | name | money | favorite_color |
|---|---|---|---|
| 1 | sakana | 100000 | green |
| 2 | fish | 400 | red |
| 3 | sake | 2000 | green |
| 4 | maguro | 10 | red |
| 5 | katsuo | 20 | green |
| 6 | tara | 3 | red |
以下のクエリを実行することでusersテーブルを作成し、先ほどの表のデータを入力できます。
create table users
(id int, name varchar(10), money int, favorite_color varchar(10));
INSERT INTO users (id, name, money, favorite_color)
VALUES
(1, 'sakana', 100000, 'green'),
(2, 'fish', 400, 'red'),
(3, 'sake', 2000, 'green'),
(4, 'maguro', 10, 'red'),
(5, 'katsuo', 20, 'green'),
(6, 'tara', 3, 'red');
これで準備は整いました。
2.2. 〇〇毎に集計して、集計したカラム以外の情報を得たい
今回は「favorite_color毎に、moneyが最も多い人の名前を取得したい」というのが目的だとします。
個人の主観としては、こういった〇〇毎に集計して、集計したカラム以外の情報を得たいときに使うクエリの例として、相関サブクエリが登場することが多いなと感じました。
そして、そういった場合によく書かれているクエリが以下になります。
SELECT favorite_color, money, name
FROM users AS t1
WHERE t1.money =
(SELECT MAX(money)
FROM users AS t2
WHERE t1.favorite_color = t2.favorite_color);
+----------------+--------+--------+
| favorite_color | money | name |
+----------------+--------+--------+
| green | 100000 | sakana |
| red | 400 | fish |
+----------------+--------+--------+
これで確かに目的のデータは手に入ります。
しかし、クエリの流れを追ってみると奇妙な点に気づきます。
この相関サブクエリの意味としては
SELECT favorite_color, money, name
FROM users AS t1
WHERE t1.money = [favorite_colorがt1と等しいテーブルでのmoneyの最大値];
となります。一見おかしなところはありません。
しかし、これではクエリが1行実行されるごとにサブクエリの全体が実行されてしまいます。
すなわち、同じfavorite_colorのmax値を無駄に何回も求めてしまっているということです。
例えば、favorite_colorがgreenの時のmoneyの最大値は3回求めていることになりますが、当然ながら最大値は何回計算しても変わらないので、無駄な計算がたくさん発生していることになってしまいます。
こういった無駄な計算を含んだ方法を自然に思いつく人はおそらく少数派です。そのため、相関サブクエリの例としてこのようなコードを見ると、最初理解するのに時間がかかってしまうのではないかと思います。
3. INNER JOINを使った工夫
ところで、今回のような例で何故相関サブクエリがよく使用されているのかというと、おそらく集計したカラム以外のカラムを取得するのが面倒だからなのでしょう。しかし、それは別の工夫でもどうにかなります。
今回紹介する方法ではWITH句を使用します。WITH句は、カラムの集まりに名前を付けて、一時的なテーブルとして作成できる機能です。
WITH句とINNER JOINを使うことで、次のようなクエリが考えられます。
WITH max_money_table AS
(SELECT users.favorite_color, MAX(money) AS max_money
FROM users
GROUP BY users.favorite_color)
SELECT t1.favorite_color, max_money, name
FROM max_money_table AS t1
INNER JOIN users AS t2
ON t1.max_money = t2.money
AND t1.favorite_color = t2.favorite_color;
+----------------+-----------+--------+
| favorite_color | max_money | name |
+----------------+-----------+--------+
| green | 100000 | sakana |
| red | 400 | fish |
+----------------+-----------+--------+
最初にmax_moneyを求めるmax_money_tableを作成し、favorite_colorとセットでキーにすることで、INNER JOINしています。
ここで大事なのは、集約関数は集約したカラム以外のカラムを取得するのが難しいですが、favorite_colorとmax_moenyは取得できるということです。
これなら、favorite_colorに対してmoneyの最大値をとる人の名前をとってくるという行為を、{favorite_color, max_moeny}をキーとしたINNER JOINを使って実現できます。
4. MAXの値を一つだけ取得する
さて、これで多少は自然なクエリができたと思いますが、MAXの値をとる人が複数人いた場合、誰か1人だけの名前を取得したい場合はこれではだめです。サンプルを変更して確認してみましょう。
4.1. サンプルデータ変更
idが7の行だけ追加すると、次の表になります。
| id | name | money | favorite_color |
|---|---|---|---|
| 1 | sakana | 100000 | green |
| 2 | fish | 400 | red |
| 3 | sake | 2000 | green |
| 4 | maguro | 40 | red |
| 5 | katsuo | 20 | green |
| 6 | tara | 3 | red |
| 7 | sanma | 100000 | green |
では、早速idが7の行を追加してみましょう。
INSERT INTO users (id, name, money, favorite_color)
VALUES
(7, 'sanma', 100000, 'green');
4.2. MAXの値を一つだけ取得する
この状態で先ほどの2. 冗長な相関サブクエリと3. INNER JOINを使った工夫の2つのクエリを実行すると、次の結果が手に入ります。
+----------------+-----------+--------+
| favorite_color | max_money | name |
+----------------+-----------+--------+
| green | 100000 | sakana |
| red | 400 | fish |
| green | 100000 | sanma |
+----------------+-----------+--------+
お察しの通り、favorite_colorがgreenのときのmax値を持つ人が2人でてきてしまい、結果も複数人でてきてしまいます。
しかしこれでは、どちらか1人だけを出力してほしいという時はどうすれば良いでしょうか。
これを解決するには、次のようなクエリが考えられます。
WITH max_money_table AS
(SELECT users.favorite_color, MAX(money) AS max_money
FROM users
GROUP BY users.favorite_color)
, id_table AS
(SELECT
(SELECT t2.id
FROM users AS t2
WHERE t1.favorite_color = t2.favorite_color
AND t1.max_money = t2.money
LIMIT 1) AS max_id
FROM max_money_table AS t1)
SELECT t2.favorite_color, t2.name, t2.money
FROM id_table AS t1
INNER JOIN users AS t2
ON t1.max_id = t2.id;
+----------------+--------+--------+
| favorite_color | name | money |
+----------------+--------+--------+
| green | sakana | 100000 |
| red | fish | 400 |
+----------------+--------+--------+
最初にWITH句で作成したmax_money_tableは先ほどと同じです。
2つ目に作成したid_tableは、相関サブクエリを使って各favorite_color毎にmoneyの値がmax_money_tableの値と一致したらidを1行分だけ返すというものです。
ここで1行のみを返すことで、各favorite_color毎に1人というのを実現しています。
5. まとめ
- 「〇〇毎に集計して、集計したカラム以外の情報を得たい」というときに自然でない相関サブクエリが使われる例がままある
- それをもう少し自然に書くには、相関サブクエリを使わずにINNER JOINを使うと良い
6. 最後に
相関サブクエリで最初詰まったときはなんでこんなに理解しづらいのかと思いましたが、手を動かして色々コードを実行していくことで、自分がどこに苦手意識を感じていたのかが見えてきました。
この記事が少しでも相関サブクエリで詰まっている方の助けとなれば幸いです。
Discussion