SQL: SELECT文におけるWHERE句とHAVING句
September 3, 2010 – 6:33 pm随分まえになるが、「Webアクセス解析ツール、ひとまず完成」などアクセス解析ツール作成にかかわり一連のエントリを書き、このブログへのアクセス情報をデータベースとする方法について検討した。
加えて、本データベースを活用し、本ブログアクセスについて、Webページごとのアクセス数を集計する簡単なツールを作成していたところであったが、最近になって、このツールに不具合があり、その原因が、SELECT文のWEHERE句とHAVING句の使い方について正しく理解していないことに気づいた。
気づいた点について、備忘録としてメモしておいた。
ブログアクセス情報DBの構成： まず、本題に入る前に、このブログへのアクセス情報を記録しているデータベースの構造について触れておく。
このアクセス情報DBは二つのテーブル、accessとmypageの二つのテーブルから構成される。それぞれのテーブルの内容は以下のようなもの：
テーブルaccess:
mysql> describe access; +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+-----------------------------+ | id | int(8) | NO | PRI | NULL | auto_increment | | time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | visitor | varchar(128) | NO | | NULL | | | ip_address | varchar(16) | NO | | NULL | | | uniqueness | varchar(16) | NO | | NULL | | | reff_uri | varchar(512) | NO | | NULL | | | refferer | varchar(128) | NO | | NULL | | | search_engine | varchar(32) | NO | | NULL | | | keywords | varchar(128) | NO | | NULL | | | page_id | int(8) | YES | MUL | NULL | | | user_agent | varchar(128) | NO | | NULL | | +---------------+--------------+------+-----+-------------------+-----------------------------+ 11 rows in set (0.00 sec)
テーブル mypage:
mysql> describe mypage; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(8) | NO | PRI | NULL | auto_increment | | page_visited | varchar(128) | NO | | NULL | | | title | varchar(256) | NO | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
テーブルmypage のカラムpage_visited はテーブルaccessのカラムpage_idとリンクしている（Table作成時にしようしたSQL文access.sqlは「PHPによる自前のWebアクセス解析ツール作成を検討」に書いている）。
Webページごとのアクセス数を集計することがツールの目的。これを行うため、当初、次のようなSQL文を作成。これにより、テーブルaccessから１日間に各々のWebページへのアクセス数を集計しようとしている。
mysql> SELECT time, page_id, COUNT(*) AS num_access
-> FROM access
-> GROUP BY page_id
-> HAVING time > SUBDATE( NOW(), INTERVAL 1 DAY )
-> ORDER BY num_access DESC;
このSQL文の結果は、以下：
+---------------------+---------+------------+ | time | page_id | num_access | +---------------------+---------+------------+ | 2010-09-03 11:10:01 | 937 | 1 | +---------------------+---------+------------+ 1 row in set (0.25 sec)
page_idが937のグループひとつのみが抽出されており、意図した結果ではないことは明瞭。ここで、
HAVING time > SUBDATE( NOW(), INTERVAL １DAY )
の部分はpage_id をキーとして抽出されたグループに対する抽出条件になっていることに気づいた。
本来、access のレコードそれぞれについて、アクセスタイムについて（１日間にアクセスされたレコードという適合条件を）適用し、そののちWeb ページの種類毎にグループ化する必要がある。この点を考慮して修正し、次のようなSQL文を作成した：
mysql> SELECT time, page_id, COUNT(*) AS num_access
-> FROM access
-> WHERE time > SUBDATE( NOW(), INTERVAL 1 DAY )
-> GROUP BY page_id
-> ORDER BY num_access DESC;
この結果は、
+---------------------+---------+------------+ | time | page_id | num_access | +---------------------+---------+------------+ | 2010-09-02 11:39:05 | 17 | 57 | | 2010-09-02 11:40:25 | 34 | 22 | | 2010-09-02 13:00:02 | 325 | 22 | | 2010-09-02 11:41:11 | 7 | 19 | | 2010-09-02 11:57:48 | 29 | 17 | | 2010-09-02 11:37:13 | 619 | 16 | (中 略） | 2010-09-02 18:54:23 | 715 | 1 | | 2010-09-02 11:35:11 | 26 | 1 | | 2010-09-02 16:41:14 | 298 | 1 | | 2010-09-03 10:25:19 | 843 | 1 | +---------------------+---------+------------+ 159 rows in set (0.24 sec)
ここに見られるように、Web Pageごとのアクセス数(num_access)が求められており、ここで目的としたいるデータが抽出されている。当初のSQL文との違いはHAVING句に代え、WHERE句をもちいたところ、そしてその位置（順序）だ。
WHERE句とHAVING句の違い： SELECT文でフィルタとして用いているWHERE句とHAVING句の違いを明確にしておかないと、うえに示した最初のSELECT文のような間違いをすることになる。SQLの入門書「一晩で学ぶSQL」（おすすめの入門書、私は重宝している）の記述について、重要と思われる部分を以下に抜粋しておく：
・・・WHERE句は特定の行についての検索をすることができますが、特定のグループについての検索はできないからです。WHERE句はグループを認識できないのです。
では、WHERE句の代わりに何を使用すればよいのでしょうか。SQLはこの目的のために別の句として、HAVING句を用意しています。・・・WHEREは行に対して働き、HAVINGはグループに対して働く・・。（p.74)
また、より一般的な話として、SELECT文の句について、
（1） SELECT
（2） FROM
（3） WHERE
（4） GROUP BY
（5） HAVING
（6） ORDER BY
の順番で使う必要があることも知っておくことも大切。
なんとも、まとまりのないメモになってしまったが、ま、備忘録としては役立つだろう。
1 Trackback(s)