GROUP BY句
今回はテーブルをいくつかのグループに切り分けて集約してみる。これはつまり、「categoryごと」や「attributeごと」に集約するということである。
このとき使用する新しい道具が、GROUP BY句である。。
例えば、categoryごとの行数（=超人数）を数えてみる。
[sql] SELECT category, COUNT(*) FROM chojin GROUP BY category; [/sql]
結果
[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+----------+ [/sql]
GROUP BY句を使っていなかったときは、1行だけだった結果が、今度は複数行に増えた。GROUP BY句なしの場合はテーブル全体を1つのグループとみなしていたのに対し、GROUP BY句を使うことで複数のグループに切り分けられることになったためである。
GROUP BY句に指定する列のことを集約キーやグループ化列と呼ぶ。テーブルをどう切り分けるかを指定するための、非常に重要な列である。もちろん、SELECT句と同様に、GROUP BY句にも複数の列をカンマ区切りで指定することができる。
なお、GROUP BY句の位置にも厳密なルールがあって、必ずFROM句の後ろ（WHERE句がある場合はさらに後ろ）におく必要がある。この句の順番を無視すると、SQLは絶対に正しく動作せず、エラーになってしまう。
集約キーにNULLが含まれていた場合
それでは、属性（attribute）をキーとしてテーブルをカットしてみる。
[sql] SELECT attribute, COUNT(*) FROM chojin GROUP BY attribute; [/sql]
結果
[sql] +-----------------------+----------+ | attribute | COUNT(*) | +-----------------------+----------+ | NULL | 2 | | 7人の悪魔超人 | 7 | | アイドル超人 | 6 | | 完璧無量大数軍 | 12 | | 完璧超人始祖 | 10 | | 悪魔六騎士 | 6 | | 残虐超人 | 1 | +-----------------------+----------+ [/sql]
問題はattributeがNULLのグループである。この結果から分かるように、集約キーにNULLが含まれる場合、それらは一括して「NULL」という1つのグループに分類されるようになっている。
この場合のNULLは「不明」を意味すると考えればよい。
WHERE句を使った場合のGROUP BYの動作
GROUP BY句を使うSELECT文でも、WHERE句は問題なく併用できる。WHERE句をつけた集約を行う場合、WHERE句で指定した条件で先にレコードが絞り込まれてから集約が行われる。
[sql] SELECT attribute, COUNT(*) FROM chojin WHERE category = '悪魔超人' GROUP BY attribute; [/sql]
結果
[sql] +---------------------+----------+ | attribute | COUNT(*) | +---------------------+----------+ | 7人の悪魔超人 | 7 | | 悪魔六騎士 | 6 | +---------------------+----------+ [/sql]
GROUP BY句とWHERE句を併用したときのSELECT文の実行順序は次のようになっている。
FROM → WHERE → GROUP BY → SELECT
SQLでは、見た目の句の並び順とDBMSの実行順序が一致しない。これがSQLの理解を難しくしている一因でもある。
集約関数とGROUP BY句にまつわるよくある間違い
集約関数と、GROUP BY句について、SQLを書く際に間違えやすい点や注意しておくべき点。
よくある間違いその1－SELECT句に余計な列を書いてしまう
COUNTのような集約関数を使った場合、SELECT句に書くことができる要素が非常に限定される。実のところ、集約関数を使うときは次の3つしかSELECT句に書くことができない。
- 定数
- 集約関数
- GROUP BY句で指定した列名（つまり集約キー）
ここで割とよくやってしまう間違いが、集約キー以外の列名をSELECT句に書いてしまうというもの。
例えば、以下のようなSELECT文はMySQL以外ではエラーになり実行できない。MySQLだけは、この構文を認めているため、エラーにならず実行できる。
[sql] SELECT name, category, COUNT(*) FROM chojin GROUP BY category; [/sql]
結果
[sql] +-----------------------------+--------------+----------+ | name | category | COUNT(*) | +-----------------------------+--------------+----------+ | マックス・ラジアル | 完璧超人 | 22 | | ステカセキング | 悪魔超人 | 13 | | キン肉マン | 正義超人 | 9 | +-----------------------------+--------------+----------+ [/sql]
nameという列名は、GROUP BY句にはない。したがってこれをSELECT句に書くことはできない。
なぜこの構文が許されないという理由は、何らかのキーでグループ化したということは、結果に出てくる1行あたりの単位もそのグループになっている、ということである。たとえば、categoryでグループ化すれば、1行につき1つのcategoryが現れる。問題はこの集約キーとnameが必ずしも一対一対応しないという点にある。
そのようなわけで、集約キーに対して複数の値が存在するSELECT句に含めることは論理的に不可能なのである。
よくある間違いその2－GROUP BY句に列の別名を書いてしまう
SELECT句に含めた項目には、「AS」というキーワードを使うことで、表示用の別名をつけることができた。しかし、GROUP BY句でこの別名を使うことはできない。以下はエラーになる構文。
[sql] SELECT category AS cate, COUNT(*) FROM chojin GROUP BY cate; [/sql]
結果
[sql] +--------------+----------+ | cate | COUNT(*) | +--------------+----------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+----------+ [/sql]
MySQLでは問題なく結果が表示されるが、この書き方はほかのDBMSでは通じないため使わないほうがよい。
なぜこの構文がいけないのか、という理由は、DBMS内部でSQL文が実行される順序において、SELECT句がGROUP BY句よりも後に実行されるからである。GROUP BY句の時点ではSELECT句でつけた別名を、DBMSはまだ知らないのである。
よくある間違いその3－GROUP BY句は結果の順序をソートする？
GROUP BY句を使って結果を検索したとき、表示される結果は、たいていの場合、複数行が含まれている。ときに何百、何千といった行数になるかもしれない。さて、その結果は、いったいどういう順番で並んでいるのか。
その答えはランダム。
結果のレコードがどんな規則に従っているかは、全く分からない。もしかすると、一見、行数の降順とか集約キーの昇順らしく見えることもあるかもしれないが、それらはすべてただの偶然である。次にSELECT文を実行したときは、全然違う並び順で表示されるかもしれない。
一般に、SELECT文の結果として表示される行の並び順は、ランダム。この並び順をソートするためには、そのための指定をきっちりとSELECT文でしておく必要がある。
よくある間違いその4－WHERE句に集約関数を書いてしまう
具体例で見てみる。category列でグルーピングして行数を数えるSQLを見てみる。
[sql] SELECT category, COUNT(*) FROM chojin GROUP BY category; [/sql]
結果
[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+----------+ [/sql]
この結果を見て、今度は「この数えた行が、ちょうど9行のグループだけ選択したい」と思ったら、どのようになるか。この場合、正義超人の行が相当する。
[sql] SELECT category, COUNT(<em>) FROM chojin WHERE COUNT(</em>) = 9 GROUP BY category; [/sql]
残念ながら、このSELECT文はエラーになる。
[sql] ERROR 1111 (HY000): Invalid use of group function [/sql]
実は、COUNTなど集約関数を書くことができる場所は、SELECT句とHAVING句だけなのである。そしてこの新しく登場したHAVING句こそが、「9行のグループだけ選択する」というようなグループに対する条件を指定するための便利な道具なのである。
DISTINCTとGROUP BY
DISTINCTとGROUP BY句は、どちらも、その後に続く列について重複を排除するという点で同じ動作をする。
例えば、以下のDISTINCTとGROUP BYはどちらも同じ動作をする。
[sql] SELECT DISTINCT category FROM chojin; [/sql]
結果
[sql] +--------------+ | category | +--------------+ | 正義超人 | | 悪魔超人 | | 完璧超人 | +--------------+ [/sql]
[sql] select category FROM chojin GROUP BY category; [/sql]
結果
[sql] +--------------+ | category | +--------------+ | 完璧超人 | | 悪魔超人 | | 正義超人 | +--------------+ [/sql]
他にも、NULLをひとまとめにするという点で同じで、複数列を使う場合の結果も全く同じである。さらに、結果の内容が同じというだけでなく、実行速度もほぼ同じなので、どちらを使えばよいか迷うかもしれない。
しかしこの疑問は、本当は本末転倒な話で、基本的にはむしろ、そのSELECT文の意味が要件に合致しているかどうか考えるべきである。「選択結果から重複を排除したい」という要件に対してはDISTINCTを使い、「集約した結果を求めたい」という要件に対しいてはGROUP BY句を使う、というのが筋の通った使い方である。
そう考えると、COUNTなどの集約関数を使わずにGROUP BY句だけを使う、という上記で紹介したようなSELECT文は、意味的におかしいことがわかる。だったらいったい、何のためにグループ化したのか、不明だからである。
SQL文は、せっかく英語によく似た構文を持っていて、人間にも意味が分かりやすいという利点を備えているのだから、その長所をわざわざ殺すような書き方をしてはもったいない、という話である。
