時刻かつURLごとのアクセス数(ヒストグラム形式)を一気に集計するSQL

BigQueryとかでログっぽいものを集計する時のアイデアメモ。
こういうのは名前が決まってると会話しやすいんだけど、なんか必殺技名ないものかしら。

元データのイメージ

以下のようなテーブルにログ的なデータが入ってたとする。

logs

created	path	message
2017-01-01 00:00:00.0123	/foo	なんか適当な付加情報
2017-01-01 00:00:01.1234	/baa	なんか適当な付加情報
2017-01-01 00:00:02.0123	/baa	なんか適当な付加情報
...

これを時刻ごとに区切って、◯時台だと/fooに何回、/baaに何回、アクセスがあったよーみたいなのを計算したい。

どう書きますか？

私の回答

要件によるんだけど、pathのパターンが数種類だけで決まっているなら、すごく簡単な1個のSQLで作れる。
ここではBigQueryの関数を使っているけれど、時刻の関数はどのRDBMSにもついてるだろうし、たぶん似たような発想で作れると思う。

  SELECT UTC_USEC_TO_HOUR(created) AS period
       , SUM(path = '/foo') AS foo
       , SUM(path = '/baa') AS baa
       , SUM(path = '/moo') AS moo
    FROM logs
   WHERE created BETWEEN '2017-01-01' AND '2017-01-02'
GROUP BY period
ORDER BY period

こういう感じのデータが取れるはず。

hour	foo	baa	moo
2017-01-01 00	112	2523	321
2017-01-01 01	90	2121	123
...

ポイントはアクセス数を出すときにCOUNTじゃなくてSUMを使うこと。trueは1としてカウントされ、falseなら0でカウント対象にならない。