GROUP BY

例えば、以下のようなテーブルがあるとする。

• users
  • id
• accesses
  • id
  • code
  • user_id
  • created_at

この時、accessesをカウントしたい。
要件は以下のような感じ。

• accesses.codeはとある一つを指定する
• 1日毎に集計
• 1日にあった総アクセスを確認したい
• 同じように、同一ユーザによる再アクセスをカウントしないアクセス数を確認したい
• 一度に見るのは一ヶ月分

とりあえず、うだうだ書いているうちに、以下のようなSQLができた。

SELECT
  day,
  count(code) AS access,
  count(distinct user_id) AS once_day
FROM (
  SELECT
    day(created_at) AS day,
    code,
    user_id
  FROM accesses
  WHERE code = 'c'
    AND created_at >= '2007-05-01 00:00:00'
    AND created_at ≷ '2007-06-01 00:00:00'
  ) AS a
GROUP BY day

+------+--------+----------+
| day  | access | once_day |
+------+--------+----------+
|    1 |    *** |      *** |
|    2 |    *** |      *** |
|    3 |    *** |      *** |
|    4 |    *** |      *** |
|    5 |    *** |      *** |
|    6 |    *** |      *** |
|    7 |    *** |      *** |
|    9 |    *** |      *** |
+------+--------+----------+
8 rows in set (0.15 sec)

うん、良い感じ。ちゃんと意図した結果が出てる。
それじゃあ、もうちょっとSQLをすっきりさせよう、と思って、以下のような感じにしてみた。

SELECT
  day(created_at) AS day,
  count(id) AS access,
  count(distinct user_id) AS once_day
FROM accesses
WHERE code = 'c'
  AND created_at >= '2007-05-01 00:00:00'
  AND created_at < '2007-06-01 00:00:00'
GROUP BY day

+------+--------+----------+
| day  | access | once_day |
+------+--------+----------+
|    1 |    *** |      *** |
|    2 |    *** |      *** |
|    3 |    *** |      *** |
|    4 |    *** |      *** |
|    5 |    *** |      *** |
|    6 |    *** |      *** |
|    7 |    *** |      *** |
|    9 |    *** |      *** |
+------+--------+----------+
8 rows in set (0.20 sec)

この時気付いたのですが、前の方が速い……。
キャッシュの問題とかあるのかと思って、何度も試したんですが、何度やっても最初のSQLの方が速い。
なんでだー。

実際のところ、0.05程度の差なんで、仕事としては気にしなくてもOKそうなんですが、なんか気になる。
というか、これがわからなくてはいけない気がする。

前々から、group by（というか、集計関数）使うともっさりするなーという印象はあったんですが、その辺りの問題かなー、とか、つらつら考えてみました。
絞り込んでからgroup by使ったほうが速いのかと考えると、そんな気もするけど……。

SQLの仕組みをもっと体で感じるレベルで理解しないと駄目だなーと思いました。
うん、頭で考えてわかるとかじゃなくて、SQL見たら、データベースの中で何が行われるか、自然とわかっちゃうレベルになれたら、きっと格好良い（仕事でそれを求められてるのか、というのとはまた別の話だったり……）。