- 更新日: 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テーブルでの作業です。
— 環境 —
MySQL 5.5.28
GROUP BY と ORDER BY を併用した場合の問題
comments テーブルのコメントが「いいね」されると、「いいね」の評価が rs_evaluations にレコードとして追加されるというケース。最近コメントに「いいね」がついた投稿(posts)のID(post_id)を重複なしで取得したいと考え、まず以下のようなSQLクエリを試しました。(WHERE句の条件などは省略してます。)
1 2 3 |
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 を使ったサブクエリで解決しようとしたところ・・・
1 2 3 4 5 |
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 2 3 4 5 |
# 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 & DB の関連記事
- MySQLでBLOB/TEXT型のカラムにはデフォルト値を設定できない
- SQLite3でDBテーブルをdumpバックアップ
- Warning: mysql_connect() PHPからMySQL接続でsocketエラー
- phpMyAdminでMySQLをバックアップ(エクスポート)
- MySQLが起動しないエラー(The server quit without updating PID file)
- phpMyAdminで#2002 Cannot log in to the MySQL serverエラー
- libaio.so.1, openssl-devel がなくて mysql-server, mysql-devel をインストールできないエラー
- MySQLオプション(/etc/my.cnf)の設定例
- my.cnf の innodb_data_file_path, innodb_log_file_size 設定で MySQL が起動しなくなる場合の対処
- my.cnfで設定するMySQLオプションで重要そうなのまとめ
Leave Your Message!