【mysql】group byで30分刻みで集計

Posted by on 2013年2月18日

例えば、1分刻みで1レコードを保持するテーブルがあったとする。
まあ要件にもよるけど、1分刻みのレコードを1時間単位で集計する場合なんかは普通にgroup by使うよね。
date_format()とか使うよね。

ただ、最近こんな要件がありました。
「30分刻みで集計を取りたい」

はて、、、、
date_formatにそんなフォーマット文字あったっけ、、、？
まあ、ある訳ないっつう話ですよね。

プログラムでグルグル回ってSQL投げまくるか！？
はたまた集計テーブル用意すっか！？

とか、かなり嫌な予感が頭を駆け巡りましたが、
どっかのガイジンさんがいいやり方を教えてくれました。
http://www.sitepoint.com/forums/showthread.php?594274-How-do-I-return-a-group-count-for-every-15-minutes-of-the-hour-on-my-Table

SELECT FROM_UNIXTIME(
CEILING(UNIX_TIMESTAMP(`timestamp`)/1800)*1800
) AS timeslice
, COUNT(*) AS mycount
FROM adsense_log
WHERE `timestamp` >= '2009-01-13'
AND `timestamp` < '2009-01-14'
GROUP 
BY timeslice

ありがとう、どっかの外人。

ただし、コイツ、一部まともに動かん。
10:00のレコードを09:30として扱って集計しよった。
おしい！おしいよ外人！

で、理屈はようわからんけど-29分してやったらなおった。

select 
	from_unixtime(ceiling(unix_timestamp(date_add(【日付カラム】, interval -29 minute)) / 1800)*1800) as timeslice
	, sum(【集計したいカラム】)
from
	xxxxx
group by 
	timeslice

ちなみにコイツは「1分刻み」のレコードの場合なんで、1秒とか1ミリ秒とかの場合は完全アウトですので注意。
てか1秒刻みとかのログっぽいテーブルの場合だったら、そもそも集計テーブル用意するから関係ないよね普通。

こんなSQLじゃパフォーマンス出るか心配だけど
whereでしっかりレコード数絞ってから集計かけてやれば案外大した負荷にはならないような気もする。
その辺は集計の要件次第で。

以上、THE・力技でした。

MySQL

Comments are closed.