MySQLで時間毎のデータを集計する

ちょっとした分析レポートを作る機会があったのですが、「時間ごとに集計するSQLってどうやって書けばいいんだろう」と思って色々悩んでいました。

データ作成時刻があれば、DATE_FORMAT関数を用いて作成時刻を整形し、GROUP BYすればSQL一文で集計可能と判明。

これ便利ですね。

今後もまた使う機会があるかもしれないのでメモ。

• サンプルテーブル構造

CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`user_id`)
);

• 時間毎の新規登録ユーザ数を集計

select count(user_id), DATE_FORMAT(created_at, '%Y/%m/%d %H') from user group by DATE_FORMAT(created_at, '%Y%m%d %H');

• 日毎の新規登録ユーザ数を集計

select count(user_id), DATE_FORMAT(created_at, '%Y/%m/%d') from user group by DATE_FORMAT(created_at, '%Y%m%d');

参考URL

• http://6rats.blog62.fc2.com/blog-entry-109.html