MySQLでGROUP BYとORDER BYを同時に使用して困ったこと

2014年2月27日

結構以前の話になるのですが、MySQLで困った事があってできずに苦しんだ事があったので、今回はそのときのお話

ブログ記事でユーザー毎の最新記事を表示させたい！！

DBの中身

id = 記事番号
user_id = 投稿者
title = ブログタイトル
contens = ブログ本文
created = 投稿日時

３人の投稿者がランダムに交代してブログを毎日12時に更新したデータだと思ってください。

これらのデータから各投稿者の最新記事を１件ずつ抽出したいというよくある話で、
このときのSQL文を書けば良いかっていうのが今回のネタです。

要件を満たす結果

満たしたい要件は【ブログ記事でユーザー毎の最新記事を表示させたい！！】

これをDBより抽出した場合の結果は以下になればOKです。

パッと見た時に簡単やん。

手順として

1:ORDER BY（ソート）して新しい記事の順に並び替えて
2:GROUP BY（グループ化）して同一のユーザーをグループ化する

簡単ですよねー！！　こんなもん屁でもないわ！！できるできる！！
とか思っていた時期が僕にもありました。

ですが・・・！

実はそうは行かなかったんです。それでいろいろ試してみました。

検証１　GROUP BY で各投稿者の記事を１件抽出する

[javascript]SELECT * from blogs GROUP BY user_id;

これは簡単なことですが、当然最新記事になっとらんとです。
これではダメです。

検証２　ORDER BY で最新記事を取得する

[javascript]SELECT * from blogs ORDER BY created DESC;

これも簡単なことですが、ユーザーが絞り込めてませんやんか。
これもダメです。

検証3　GROUP BY　と　ORDER BY　を組み合わせてみる

[javascript]SELECT * from blogs GROUP BY user_id ORDER BY created DESC;

お！ユーザーも絞り込めてるし。投稿日時も新しい順になっとるやんか。

騙されてはいけません。
shigeoの最新の記事ではありませんし、
eiyanの最新の記事でもありません。
maruoも最新の記事でもありません。

むしろ最初の記事やん！？
これもダメです。

この状況は GROUP BY　を行った後に　ORDER BYを行っているからです。
ユーザー毎にグループ化した結果を新しい順に並べてもそうなるわなー。って話です。

検証4　ORDER BY　を先にやって、GROUP BY　を組み合わせてみる

[javascript]SELECT * from blogs ORDER BY created DESC GROUP BY user_id;[/javascript]

んとね、これはエラーなんですわ。syntax エラー！とMySQLさんに怒られるんです。
SQL文の書く順番を変えようが、結果は変わりませんが、この書き方はあきまへんで！って怒られる。

これはダメ。絶対！

んー簡単だと思っていた事がこんなに悩まされるのか！
上記の方法でできればちゃちゃっとできるやん。

検証5　MAX()関数　と GROUP BY　を組み合わせてみる

MAX() はフィールドの最大値を取ってきてくれる　＝　最新投稿日時の最大値を取得する

[javascript]
SELECT MAX(created) from blogs GROUP BY user_id;

blogs
+———————+
| MAX(created) |
+———————+
| 2014-02-25 12:00:00 |
| 2014-02-24 12:00:00 |
| 2014-02-23 12:00:00 |
+———————+
[/javascript]

まずこれで投稿日時の最新のものが３人分抽出できてます。
これでいけるんやん。

ということで

[javascript]
SELECT id, user_id, title, contents, MAX(created) FROM blog2 GROUP BY user_id

よし。ユーザー毎の最新更新日時を取得できた！！！
と思ったら大間違い！！

記事番号がおかしいし、タイトルも本文もおかしいやん。
結局これもダメ。

検証6 サブクエリを使う。

[javascript]
SELECT * from blogs WHERE created IN(SELECT MAX(created) FROM blogs GROUP BY user_id);

おっしゃ！これで抽出できた！！これでいいんです。完璧だと思うんです。

ただ、サブクエリを使うと重くなるって話がよくあります。
今回の10件程度の情報量だと全然問題ないでしょうけど、情報量が多くなると処理の遅さにイラっとくる事になるかもです。

サブクエリ使うとどれだけ重くなるの？　教えて！　いわもん！
という疑問が出てきます。

検証7 検証でもなんでもないけど組み合わせるのをやめる

単純に ORDER BYだけの抽出を行い、プログラムで重複していないユーザーだけ出力する。

[javascript]
SELECT * from blogs ORDER BY created DESC;

とりあえず、この抽出を行って、あとはプログラムで重複しないユーザーだけ出力する。

[javascript]
<?php
$users =array();
foreach( $blogs as $data ){
if(!isset($users[$data[‘user_id’]])){
echo ‘| ‘.$data[‘id’].’ | ‘.$data[‘user_id’].’ | ‘.$data[‘title’].’ | ‘.$data[‘contents’].’ | ‘.$data[‘created’].’ |’;
$users[$data[‘user_id’]] = $data[‘user_id’];
}
}
?>
[/javascript]

雑ですが、これで要件を満たす出力ができるはず。
投稿日時の新しい順に全データ取得して、プログラムのループで１度取得したユーザーは２度目以降出力しない。

一応は要件通りの結果は出せます。
全データの状況やテーブルの中身次第ですけど。

検証結果

結局のところ、重くなるのを覚悟するかごり押しか諦めるかという結論に至りました。

今回のブログで覚えておかないと困るなーって内容は
グループ化してソートするのはしてもいいけど
ソートしたのとグループ化したいなら、サブクエリでやってね。ということです。

もっと良い方法知っていれば、ぜひぜひ教えてください。