MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと

この記事はだいぶ前に書かれたものなので情報が古いかもしれません
今さらな話ではあるんですが、前にも一度同じ過ちを犯したことがあるので、ここでちゃんと自分を懲らしめておきたいと思います。



前提条件

例えば、データベースにこんなデータが入っているとする。

+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  3 |     1     | title3 | text | 2012-10-03 12:00:00 | 
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  5 |     3     | title5 | text | 2012-10-05 12:00:00 | 
|  6 |     2     | title6 | text | 2012-10-06 12:00:00 | 
|  7 |     3     | title7 | text | 2012-10-07 12:00:00 | 
|  8 |     1     | title8 | text | 2012-10-08 12:00:00 | 
|  9 |     1     | title9 | text | 2012-10-09 12:00:00 | 
+----+-----------+--------+------+---------------------+

id・・・ユニークのID
author_id・・・投稿者のID
title・・・記事タイトル
text・・・記事本文
created・・・投稿日時

これは、3人の投稿者が適当に交替しながらブログの記事を毎日12時に更新したデータ……みたいな感じです。



では今ここで、このデータから各投稿者の最新の記事を1件ずつ抽出したいと思ったとき、どんなSQL文を書けば良いでしょーかってのが、今回のお話です。

得られる結果としてはこうなればオッケー。

+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  9 |     1     | title9 | text | 2012-10-09 12:00:00 | 
|  6 |     2     | title6 | text | 2012-10-06 12:00:00 | 
|  7 |     3     | title7 | text | 2012-10-07 12:00:00 | 
+----+-----------+--------+------+---------------------+

別にキレイにauthor_idが数の小さい方から順に並んでる必要はないです。要はこの3件のデータがちゃんと取得できればオッケーってことね。



GROUP BY句とORDER BY句の組み合わせでは実は取得できない

MySQLにはデータをグループ化して取得してくれるGROUP BYってのと、データを任意にソートして取得してくれるORDER BYってのがあります。

SELECT * FROM table GROUP BY author_id

こう書けば投稿者のIDでグループ化したデータを取って来てくれるし、

SELECT * FROM table ORDER BY created DESC

こう書けば投稿日時の新しい順にデータを取って来てくれる。ちなみに「ORDER BY created ASC」だったら投稿日時の古い順にデータを取って来てくれる。



なるほど、そういうことなら話は早いじゃん。

このGROUP BYとORDER BYを組み合わせて使えば一発で解決じゃん。

何わざわざ記事にしてんだよ限りあるデータ容量を無駄遣いすんなよバカバカバーカ。

豆腐の角に頭ぶつけてそれを見ていた豆腐屋の親父に「食い物を粗末にすんじゃねー!!」って殴られて死んじまえ。



……とか思ったそこの君! そこの君だそこの君!

いやー、それがそうは問屋が卸さないんですよ。豆腐屋の親父も豆腐なら卸せるがそっちは卸せねえって言ってます。

SQLには命令文を処理する順番ってのがありまして、結論だけ一言で言うと「ORDER BYはGROUP BYの後で処理される」ようにできているんですわ。

つまり、こんなSQL文を書いたとしても、

SELECT * FROM table GROUP BY author_id ORDER BY created DESC

各投稿者の最新の記事を都合良く1件ずつ取って来てはくれないんですね。

実際にやってみると、GROUP BYの方が先に処理されるので、投稿者のIDでグループ化された後に、投稿日時の新しい順にソートされたデータが返ってきます。

+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
+----+-----------+--------+------+---------------------+

こんな感じになる。各人とも最新の記事ではないですね、これは。むしろ各人とも一番古い記事を取って来ちゃいました。

これはSQL文の書く順番がどうってわけではないので、先にORDER BYを書いたからって結果が変わるわけじゃない。っていうか、ORDER BY => GROUP BYの順番でSQL文を書くと、エラーになるはず。

//実行すると何とかかんとかsyntaxエラーみたいなこと言われる
SELECT * FROM table ORDER BY created DESC GROUP BY author_id

これは困った。さあ、どーしましょう。



MAXとGROUP BYを試してみる

MySQLにはカラムの最大値を取ってくるMAXって関数があります。

SELECT MAX(id) FROM table

これだとidの一番大きい値のデータが取れる。今回の例で言うと、idが9のデータですね。

ORDER BYを使わない代わりに、これとGROUP BYを組み合わせる。

SELECT MAX(created) FROM table GROUP BY author_id

これで、各投稿者の一番新しい記事の投稿日時が取得できます。



おお、良かった良かった。これで万事解決。さすがMAX。安室ちゃんの後ろにいる頃からできる子たちだと思ってたよ、俺は。



……とか思ったそこの君! そこの君だそこの君!

うーん、まあダメってわけではないんだけど、これは必ずしも万事オッケーってわけでもない。ちょっとだけ欠点がある。

あ、もちろん安室ちゃんの後ろにいた方のMAXは欠点なんてないよ? うん、ないない。俺も好きだったよ。Minaが特に。

ラーイドンナーイ♪ ラーイドンナウラーイドンターイム♪



実際に取れるデータを見てみる

さっきの例では投稿日時しか取得しないような書き方でしたが、今度はidとか他のカラムのデータも取得してみましょう。

するとどうなるか。

SELECT id, author_id, title, text, MAX(created) FROM table GROUP BY author_id

+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |     MAX(created)    |
+----+-----------+--------+----------------------------+
|  1 |     1     | title1 | text | 2012-10-09 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-06 12:00:00 | 
|  4 |     3     | title4 | text | 2012-10-07 12:00:00 | 
+----+-----------+--------+------+---------------------+

こうなります。投稿日時は確かに一番新しいのが正しく取れてるんだけど、他はORDER BYと組み合わせたときと同じデータしか取れません。

だから、最新の投稿日時だけを取得するような場合はこれでも良いと思いますが、もしレコード全体を取って来たいのなら……どうしよう。



サブクエリを使ってみる

僕はサブクエリってほとんど使ったことないんですけど、これならいける。

SELECT * FROM table WHERE created IN(SELECT MAX(created) FROM table GROUP BY author_id)

+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  9 |     1     | title9 | text | 2012-10-09 12:00:00 | 
|  7 |     3     | title7 | text | 2012-10-07 12:00:00 | 
|  6 |     2     | title6 | text | 2012-10-06 12:00:00 | 
+----+-----------+--------+------+---------------------+

理想型としてはこれですね。

ただねぇ……サブクエリはとにかく遅いって話をちょいちょい耳にします。僕がサブクエリを使わないのもみんながそういうからってのもあるもので(使い慣れてないから使わないってのはここでは内緒にしておく)

そんなわけで、これでも結果はオーライなんですが、データの量が膨大になってきたりするとクエリの処理の遅さってのは際立ってきますからね。のんびりサブクエリなんて発行していたらデートに遅刻してしまうこともあるかもしれない。



組み合わせるのを止める

最終的に、僕に出せる結論は、とりあえずあきらめることです。人生あきらめって大事。一回のサブクエリより一回のデートの方が大事。燃える心を届ける方が大事。

あ、そりゃMAXじゃなくて山下達郎の方のRIDE ON TIMEか……。



ってことで、ORDER BYの方だけ指定して一旦必要なデータを全部取得してきて、あとはプログラム側で何とかするってのも、ありだと思う。

//発行するSQL
SELECT * FROM table ORDER BY created DESC

//プログラム側の処理
$authors = array();
foreach($data as $val) {
  if(!isset($authors[$val['author_id']])) {
    $results[] = $val;
    $authors[$val['author_id']] = $val['author_id'];
  }
}

//ここでは$dataに以下のようなデータが入っていると仮定
$data => array(
  [0] => array(
    [id] => 9
    [author_id] => 1
    [title] => title9
    [text] => text
    [created] => 2012-10-09 12:00:00
  )

  [1] => array(
    [id] => 8
    [author_id] => 1
    [title] => title8
    [text] => text
    [created] => 2012-10-08 12:00:00
  )

  //以下略
)

すっげー乱暴に書くと、こんなような処理を行う。

書き方は何でも良いんだけど、とにかく投稿日時の新しい順に全データを取得して、ループ処理ですでに該当の投稿者の記事が配列に入っていたらそれ以上は入れないようにするってことですね。

こうすれば、一応望む通りの3件は取れる。

テーブルの中身次第だと思いますけど、サブクエリほどじゃなくてもGROUP BYも結構処理が重くなるって聞いたことあるし、それならば全部取得してプログラム側で処理した方が全体的な処理時間は短くなる……こともあるかもしれない。






最終的にあきらめるしかないって結論になってしまったのが悲しいですが、今日の中で大事なことは、グループ化したデータをソートするならともかく、ソートしたデータをグループ化するのはサブクエリなどを使わないとできないらしいよってことです。

まあ、僕の知る限りではという話なので、もしかしたら何かそれを解決するもっと良い方法があるかもしれないですけどね。

いつも通り、それについては誰かがコメントくれるのを気長に待つか、ググレカスってもらうってことで、今日のところはこれにて閉店。



追記

「HAVINGを使うのはどうだ?」というコメントをいただいたのですが、実際に検証してみたところ、残念ながらHAVINGを使っても望み通りの結果は得られませんでした。データの持ち方によっては上手くいくのかもしれませんが、今回のケースではダメでした。

検証結果についてはこちら。
在りし日のGROUP BYとORDER BY、そしてHAVINGについて
 もしかしたら何か関連しているかも? 
 みんなからのコメント 
2014年07月02日 23:19:49
scan
私なら以下のように書き、(author_id,created)の複合インデックスを張ります。

SELECT T.*

FROM (
SELECT author_id, MAX(created) AS MAX_TIME
FROM `table`
GROUP BY author_id
) TM

LEFT JOIN `table` T
ON T.author_id = TM.author_id
AND T.created = TM.MAX_TIME
2014年07月03日 19:34:54
まっち~(管理人)
>scanさん
なるほど…こんなやり方もあるんですね。勉強になります。
僕も一度試してみたいと思います。ありがとうございました。
2014年07月29日 08:36:35
ひよ
こういうのはどうでしょうか。

SELECT *
FROM table
GROUP BY author_id
HAVING created = MAX(created);
2014年07月29日 20:36:44
まっち~(管理人)
>ひよさん
おお、なるほど。こういうのもありましたか。
HAVING句って実は使ったことがないもんで、使ってみるという発想がありませんでした。自分、まだまだ勉強不足っすね。
ご意見ありがとうございました。ぜひ試してみます。
2015年02月23日 13:14:13
匿名
せっかく記事の内容は良いのに、ページを開くと後ろの星のせい?でノートパソコンのファンが回りまくります。
できれば、低スペックPC、にやさしくしてもらえませんか?
2015年02月25日 00:14:28
まっち~(管理人)
>匿名さん
うおお、マジですか。気づかずにすみませんでした。
あれを動かしてるjsは切ったので、もし次に訪れる機会があれば、そのときは大丈夫だと思います。
他のところで負荷が高くなってしまっていたらすみません。ちょうど良い機会なので、一通りサイト全体の処理をチェックしてみますね。
ご意見いただきありがとうございました。
2016年09月10日 15:16:15
nori
とっても助かりました。ありがとうございます。
2016年09月11日 16:13:19
まっち~(管理人)
>noriさん
いえいえ〜、こんな記事でもお役に立てたのなら何よりです。
2017年08月19日 15:28:14
nao
ひよさんの解答スマートですね。
勉強になります。
最近、組込系からWeb系にJOBチェンジしたのですが、会社のコードにはHAVING句が皆無なので存在自体を忘れていました。笑
2017年08月21日 10:23:02
まっち~(管理人)
>naoさん
僕もこの記事を書いた時点ではHAVING句の存在自体知らなくて、とても勉強になりました。こういうコメントをいただけるのはすごくありがたいです。
2018年01月31日 10:01:04
[…] 参考:MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと | 日記の間 | あかつきのお宿 […]
2018年01月31日 20:21:28
匿名
having は group by の後に処理されるので、うまく行かないようです。(実際に検証してみました)

HAVING created = MAX(created) では、created の値が既に絞り込まれているので、それに一致するMAX(created)が無い可能性があって、その場合データが抽出できない場合があるという結果になるようです。

そのため、やっぱりscanさんが書いているような形にする必要があるように思います。
2018年02月01日 16:09:41
まっち~(管理人)
むむむ、マジですか。自分的には上手くいっていたような気がしていたのですが、たまたま条件に一致するデータが取れてただけだったのかもですね。
情報ありがとうございます。そして検証不足ですみません。もっかい試してみます。
2018年12月01日 15:51:10
[…] MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと https://norm-nois.com/blog/archives/1293 […]