mysql分単位での統計情報

質問

以下のようなデータがあって、分単位での統計情報を出すにはどうすればいいのでようか。

time（Unix_time)　 　count_1 count_2 count_3
-----------------------------------------
2012-02-23 13:01 　　0 　 2 　 0
2012-02-23 13:01 　　1 　1 　 0
2012-02-23 13:01 　　2 　2 　 3
2012-02-23 13:01 　　0 　2 　 0
2012-02-23 13:01 　　1 　5 　 2
2012-02-23 13:02 　　1 　2 　 2
2012-02-23 13:02 　 　0 　1 　 2
2012-02-23 13:02 　　1 　1 　 0
↓　↓　↓
以下のように出力させたい
time（Unix_time)　 　count_1 count_2 count_3
-----------------------------------------
2012-02-23 13:01 　　4 　 12 　 5
2012-02-23 13:02 　　2 　 4 　 4

よろしくお願いします。

回答No.1

yambejp · ベストアンサー

単純にtimeでgroup byすればよいのでは？

create table hoge (id int,time_a datetime,count_1 int,count_2 int, count_3 int);
insert into hoge values(1,'2012-02-23 13:01',0,2,0),(2,'2012-02-23 13:01',1,1,0),(3,'2012-02-23 13:01',2,2,3),(4,'2012-02-23 13:01',0,2,0),(5,'2012-02-23 13:01',1,5,2),(6,'2012-02-23 13:02',1,2,2),(7,'2012-02-23 13:02',0,1,2),(8,'2012-02-23 13:02',1,1,0);
select time_a,sum(count_1) as sum_1,sum(count_2) as sum_2,sum(count_3) as sum_3 from hoge group by time_a;

※ただし、datetime型で持つ場合、秒まで入力されると集計がバラけます。
秒は常に0とするか、文字列なら問題ないです。

お礼

ありがおとうございます。
timeはint型（int(10) unsigned）でUNIX TIMEになっています。。。
この場合の出し方ってありますでしょうか。

補足

floorを使って何とかUNIXタイムを秒単位まで揃えましたが、出力はだめでした。

select floor(time / 60) * 60 as time,sum(count_1) as count_1,sum(count_2) as count_2,sum(count_3) as count_3 from hoge group by time;

出力結果：
time count_1 count_2 count_3
-----------------------------------------
1330082280 1 0 0
1330082280 0 2 3
1330082280 0 2 0
1330082340 1 2 3
1330082340 2 1 1
1330082340 2 1 0

回答No.2

yambejp

条件定義があいまいなので正確に答えられないのですが、
元データは秒までもっているということですね？

別名にフィールド名を利用してしまうとgroup byするときに
フィールド名が優先されているだけじゃない？
別名にユニークなものを利用してください。

create table hoge (id int,time_a int unsigned,count_1 int,count_2 int, count_3 int);
insert into hoge values(1,unix_timestamp('2012-02-23 13:01'),0,2,0)
,(2,unix_timestamp('2012-02-23 13:01:01'),1,1,0)
,(3,unix_timestamp('2012-02-23 13:01:02'),2,2,3)
,(4,unix_timestamp('2012-02-23 13:01:03'),0,2,0)
,(5,unix_timestamp('2012-02-23 13:01:04'),1,5,2)
,(6,unix_timestamp('2012-02-23 13:02:01'),1,2,2)
,(7,unix_timestamp('2012-02-23 13:02:02'),0,1,2)
,(8,unix_timestamp('2012-02-23 13:02:03'),1,1,0);
select FROM_UNIXTIME(time_a - time_a % 60) as time_b,sum(count_1) as sum_1,sum(count_2) as sum_2,sum(count_3) as sum_3
from hoge group by time_b;

余談ですが、集計を前提としているDBなら、insert するときに各レコードに
分までの集計用カラムを埋め込んでおくのが効率的ですね

お礼

insert するときに集計用カラムを埋め込むことにします。
ありがとうございます。