在りし日のGROUP BYとORDER BY、そしてHAVINGについて

この記事はだいぶ前に書かれたものなので情報が古いかもしれません
MySQL

この記事を三行にまとめると

君たちにとってはついさっきの出来事かもしれんが……
「HAVING句」を使ったらどうだっていうご意見があったんですよ
上手くいくどころか一件のデータも取れなかった……
あれはもう五年……いや、六年ほど前になるのか。君たちにとってはついさっきの出来事かもしれんが……。

という感じでですね、もうかなーり前の話になるのですが、MySQLでGROUP BYとORDER BYを併用する際にはちょっと注意が必要だぞ〜ってな話をしたことがありました。

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

簡単におさらいすると、テーブルにこんな感じ(↓)のデータが入ってた場合に、投稿者(author_id)ごとに最新のデータを取得するためにGROUP BYとORDER BYを併用しても、欲しいデータは取れないぞっていう内容です。

//実行するSQL
SELECT * FROM table GROUP BY author_id ORDER BY created DESC

//テーブルの中身
+----+-----------+--------+------+---------------------+
| 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 | 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 | 
+----+-----------+--------+------+---------------------+

//実際に取れるデータ
+----+-----------+--------+------+---------------------+
| 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 | 
+----+-----------+--------+------+---------------------+

前回の記事ではサブクエリとかを使わなきゃ無理だっていう結論に至ったのですが、いくつかコメントをいただいた中で「HAVING句」を使ったらどうだっていうご意見があったんですよ。

SELECT * FROM table GROUP BY author_id HAVING created = MAX(created)

こうすれば投稿者ごとの最新データを取れるぞと。当時の僕はHAVING句という存在自体知らなかったので、「こいつはすげえ! さっそく使ってみよう!」ってな感じでやってみたんですよ。

で、たぶんなんですが、当時自分で試した時は上手く取れたと思ったんですよね。もう何年も前のことなのでうろ覚えなのですが、確か「これからはHAVING句の時代だぜ!」的な結論が自分の中にあったはず。

でもつい先日、今度はHAVING句を使っても上手くいかねーぞっていうコメントをいただきまして……改めて自分でも検証してみたんですが、確かに上手くいきませんでした。

実際テーブルに上記と同じデータを突っ込んでHAVING句を使ってphpMyAdminでSQLを実行してみたんですが「返り値が空でした (行数 0)」という結果が出ました。上手くいくどころか一件のデータも取れなかった……。



HAVING句というのはグループ化したデータに対して条件を指定できる機能です。条件を指定するってところはWHERE句と似ていますが、グループ化したデータに対して効くってところがミソです。

例えば上記のデータに対して、以下のようなHAVING句を実行する。

SELECT * FROM table GROUP BY author_id HAVING id > MAX(author_id)

これは「autor_idでグループ化したレコードのうち、idがMAX(author_id)よりも大きいものだけを取得する」という意味になります。今回の場合、author_idは「1,2,3」の三つなので、MAX(author_id)は3ってことなりますね。つまりグループ化したデータのうち、idが3より大きいデータだけが取得されることになります。

先ほどauthor_idでグループ化した時に取れるデータはidが「1,2,4」の三つでした。このうちidが3より大きいデータはidが4の一つだけなので、結果はこうなります。

//SELECT * FROM table GROUP BY author_id HAVING id > MAX(author_id)
+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
+----+-----------+--------+------+---------------------+

ここで気をつけたいのは、「MAX(author_id)」というのはグループ化されたデータの中で最大という意味ではなく、全レコードの中の最大値であるということです。

ってーことは「HAVING created = MAX(created)」とした場合、MAX(created)は「10月09日(2012-10-09 12:00:00)」となる。グループ化した時のデータ(idが1,2,4のデータ)のcreatedはそれぞれ「10月01日」「10月02日」「10月04日」なので、10月09日に該当するデータはありません。だから取得結果は0件になっちゃうわけですね。



何であの時は上手くいったと僕が判断したのか……真相は闇の中ですが、考えられるのは、phpMyAdminってデータが一件も取得できなかった場合、一つ前に取得した状態が画面に残ってるんですよね。バージョンによるかもしれませんが。

だからたぶん、最初に「SELECT * FROM table WHERE created IN(SELECT MAX(created) FROM table GROUP BY author_id)」とかで正しくデータを取得した後、HAVING句を使ったら結果は0件だったのに、「返り値が空でした」っていうメッセージを見落としてて「あれ? できてんじゃん」って判断したのかもしれません。

たぶんこういうことだったんだと思う

実際やってみるとこんな感じ。一瞬、あたかもデータが正しく取れてるっぽく見えちゃいますよね。当時の僕はphpMyAdminもあまり使いこなせてないひよっこでしたから、きっとこんなミスをしていたのでしょう。うん、きっとそうだ。

いずれにせよHAVING句を使っても望み通りの結果が得られるとは限らないようなので、やっぱりサブクエリを使うなりする必要があるという結論に変わりはないみたいです。残念無念。
 もしかしたら何か関連しているかも? 
 みんなからのコメント 
匿名 2019年11月22日 15:31:54
最近MySQLの勉強初めて、HAVING句の後にORDER BY句使ってもだめなの?なんでぇ?ってなったのでこの記事にたどり着きました。やっぱり工夫するしかないみたいですね...
匿名 2019年11月22日 16:02:08
あ、さっきコメントしたものですが、GROUP BYした結果をORDER BYで並べ替えるときに、列名を番号で指定するとうまくいきました。
まっち~(管理人) 2019年11月22日 23:56:56
僕も自分なりにいろいろやってはみたんですがなかなか上手くいかず……列名を番号で指定っていうのは、カラムの名前を数字にしたみたいなことですかね……? それで上手くいくなら僕も試してみたいな。
匿名 2019年11月24日 00:45:25
返信ありがとうございます。
素人なのでよくわかっておらず、説明するのが下手くそなんで、例を書かせていただきますね。
SELECT 列名 FROM テーブル名 GROUP BY 列名 ORDER BY 1←この数字はそれぞれSELECTの後ろで列挙した列名に自動的に割り当てられた番号です。(補足:割り当てられる番号は1始まりのようです) なので今回の場合は、最初に記述した列名を基準に並び替えを行います。
私は最初、明示的に列名を指定して並び替えを行おうとしましたが、エラーになっちゃいました。どうしたものかな~と悩んでいるうちに、そういえばこんな指定の方法もあったよなぁと思い、あまり期待せずにやってみたら成功したので、こちらにコメントさせていただきました。思い付きでたまたま成功しただけなので、何が良かったのか全く分かっておりませんが、参考になれば幸いです。
匿名 2019年11月24日 00:54:57
あれ…あんなに長々と説明したのに申し訳ありません。
いま試しにわざと、番号で指定していたところを今一度列名で指定したのですが、行けちゃいました。前は確かにエラーメッセージが出ていたというのに…なぜ???
匿名 2019年11月24日 02:01:25
何度もコメント申し訳ありません…結局私に起きたエラーの原因はわかりませんでした。
それと、こちらのほうで同じようなテーブルを作って、欲しい結果が得られるように書いてみました。
SELECT MAX(id) AS id,author_id,title,text,MAX(created) AS date FROM table GROUP BY author_id ORDER BY author_id,date
グループ化する前にMAX関数を使ってみました。参考程度にどうぞ
まっち~(管理人) 2019年11月24日 11:32:26
いえいえ、こちらこそまた来ていただいて恐縮です。

なるほど。列名に番号が割り振られているというのは知りませんでした。情報ありがとうございます。それを上手く使えれば今までとは違うアプローチができるかもですね。ちょっと見てみます。

それからMAX関数を使ったやり方なのですが、この書き方だとMAXを使っている列(今回の場合はidやcreated)は欲しいデータが取れるんですけど、それ以外のところ(titleやtext)は欲しいデータが返って来ない場合があるんですよ。各投稿者(author_id)の最新の投稿時間だけが欲しいという場合はこれでも行けるんですが……この辺がなかなか悩ましいところです。

一応この記事の元になった方でもMAXと組み合わせたらこんな結果になったぞーというのを紹介してるので、もし良かったら。
MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと
匿名 2019年11月24日 13:18:17
なるほど、かなり力技でしたので、やっぱり不具合出てきちゃいますか…
元記事も見てきますね