今さらな話ではあるんですが、前にも一度同じ過ちを犯したことがあるので、ここでちゃんと自分を懲らしめておきたいと思います。



前提条件

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


+----+-----------+--------+------+---------------------+
| 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・・・投稿日時

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



では今ここで、このデータから各投稿者の最新の記事を１件ずつ抽出したいと思ったとき、どんな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が数の小さい方から順に並んでる必要はないです。要はこの３件のデータがちゃんと取得できればオッケーってことね。



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

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

実際にやってみると、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が９のデータですね。

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

//プログラム側の処理
<?php
$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
  )

  //以下略
)

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

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

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

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






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

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

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