更新日: 2014年5月2日
MySQL & DB

MySQLでGROUP BYとORDER BY併用時の注意

MySQL で INNER JOIN して GROUP BY して ORDER BY しようとしたら、はまりまくったので備忘録です。先日のエントリー Rails ActiveRecord で DISTINCT, JOIN, GROUP BY, ORDER BYなど | EasyRamble で扱った、ActiveRecord Reputation System のDBテーブルでの作業です。

【お知らせ】英単語を画像イメージで楽に暗記できる辞書サイトを作りました。英語学習中の方は、ぜひご利用ください！

画像付き英語辞書 Imagict | 英単語をイメージで暗記
【開発記録】
英単語を画像イメージで暗記できる英語辞書サービスを作って公開しました

— 環境 —
MySQL 5.5.28

GROUP BY と ORDER BY を併用した場合の問題

comments テーブルのコメントが「いいね」されると、「いいね」の評価が rs_evaluations にレコードとして追加されるというケース。最近コメントに「いいね」がついた投稿(posts)のID（post_id）を重複なしで取得したいと考え、まず以下のようなSQLクエリを試しました。（WHERE句の条件などは省略してます。）


SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id ORDER BY MAX(rs_evaluations.created_at) DESC LIMIT 10;

SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id ORDER BY MAX(rs_evaluations.created_at) DESC LIMIT 10;

これだと、GROUP BY で指定したカラム comments.post_id は、ORDER BY MAX(rs_evaluations.created_at) に従って最新のレコードの値が取得できますが、他のカラムが最新の値のものが取得できるとは限りません。えーーってなった。

なので、この方法はあくまでも GROUP BY で指定したカラムの値のみを利用する場合には使えますが、他のカラムの値も使う場合はちょっと使いづらい。以下のページに面白詳しく説明してあります。

MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと | 日記の間 | あかつきのお宿

とにかく GROUP BY と ORDER BY を併用すると、ORDER BY MAX(rs_evaluations.created_at) としてやっても、GROUP BY で指定したカラム以外は最新のものが取れるとは限らない。

GROUP BY と ORDER BY を同時に使わずサブクエリを使用

従って、GROUP BY と ORDER BY を使うのをやめて、IN を使ったサブクエリで解決しようとしたところ・・・


mysql> SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id WHERE rs_evaluations.created_at IN(SELECT MAX(rs_evaluations.created_at) FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id LIMIT 10) ORDER BY rs_evaluations.created_at DESC;

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

mysql> SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id WHERE rs_evaluations.created_at IN(SELECT MAX(rs_evaluations.created_at) FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id LIMIT 10) ORDER BY rs_evaluations.created_at DESC;

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

エラー発生。サブクエリの中に LIMIT を書くと、MySQL5.5ではエラーが発生するようです。

LIMIT を書かなければこの方法でOKでしたが、LIMIT を指定しないとレコード数が増えた時が問題です。外側のクエリと内側のサブクエリで2回 JOIN 使ってるのも、なんだか無駄なような気がして気持ち悪いですが、何か方法があるのだろうか…

SQLクエリを2回に分けて2つ目のクエリで IN を使う

結局クエリを2回に分割して、2つ目でINを使って解決しました。Rails でやってたので、以下は ActiveRecord::Base.connection.select を使って生SQLのクエリを発行しています。

# 1つ目
recent_rs_evaluations = ActiveRecord::Base.connection.select("SELECT MAX(rs_evaluations.id) FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id LIMIT 10").rows.flatten.join(', ')

# 2つ目
ActiveRecord::Base.connection.select("SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id WHERE rs_evaluations.id IN(#{recent_rs_evaluations}) ORDER BY rs_evaluations.id DESC")

# 1つ目

recent_rs_evaluations = ActiveRecord::Base.connection.select("SELECT MAX(rs_evaluations.id) FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id GROUP BY comments.post_id LIMIT 10").rows.flatten.join(', ')

# 2つ目

ActiveRecord::Base.connection.select("SELECT rs_evaluations.*, comments.* FROM rs_evaluations INNER JOIN comments ON comments.id = rs_evaluations.target_id WHERE rs_evaluations.id IN(#{recent_rs_evaluations}) ORDER BY rs_evaluations.id DESC")

まず1つ目のクエリで、最近コメントにいいねが付いた評価のID (rs_evaluations.id) を取得します。rs_evaluations テーブルに comments テーブルを、ON の条件で INNER JOIN して、comments.post_id で GROUP BY して、SELECT MAX(rs_evaluations.id) でグループごとの最新の id を取得。

created_at は値が同一になる可能性があるので、created_at ではなくて id を使いました。rs_evaluations は、時系列でレコードが追加されていくので新しいものほど id が大きいので、MAX(rs_evaluations.id) を SELECT の対象とした。

最後の rows.flatten.join(‘, ‘) は、ActiveRecord::Base.connection.select で取得できるオブジェクトから、2回目のクエリの IN で使用する用に整形するため。

2つ目のクエリで、recent_rs_evaluations を IN で検索して、最近コメントに「いいね」がついた投稿ID (post_id) を取得します。これでどうにか JOIN した両方のテーブルから、post_id 以外の全てのカラムにおいて目的の最新の値を取得することができました。結構重たいクエリになってそうだけど、他の解決策見つかるまでとりあえずこれで行きます。

いまいちこれがベストという確信も持てませんし、間違ってる可能性もあるので、何かお気づきの点がありましたら是非ご連絡お願いします！

現場で使えるMySQLは長くお世話になっていて、MySQL触る人には必須の本。

現場で使える MySQL (DB Magazine SELECTION)

PHP+MySQLマスターブック