分时间段统计数据，空值自动填充

Question

本人在开发一个项目，要求统计出每5分钟的数据流量
表结构如下
DROP TABLE IF EXISTS `traffic`.`datas`;
CREATE TABLE  `traffic`.`datas` (
  `data_id` int(10) unsigned NOT NULL auto_increment,
  `total` bigint(20) unsigned NOT NULL,
  `rx` bigint(20) unsigned NOT NULL,
  `tx` bigint(20) unsigned NOT NULL,
  `device_id` int(10) unsigned default NULL,
  `createtime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `protocol` varchar(45) NOT NULL,
  PRIMARY KEY  (`data_id`),
 ) 现在语句倒是写好了
select sum(da.total) as total , FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(da.createtime)/300)*300) as timeNearTo
from datas da where da.createtime between '20090629' and '20090630' and da.device_id='158'
group by timeNearTo
结果如下
total timeNearTo
400    20090629 00:00:00
200    20090629 00:05:00
1100   20090629 00:10:00
280    20090629 00:20:00但是有个问题出现了，如果某个时间段内没有数据，就不会有记录，比如上面的返回列表就少了一个20090629 00:15:00的统计记录，我这边需要自动插入空数据比如
0  20090629 00:15:00哪位朋友可以帮忙看看，不胜感激阿！！！

Answer 1

是不是需要存储过程，mysql的不太熟悉，比较难写。哪位帮忙下拉

Answer 2

如果每天，甚至每小时的话，你可以另加一个日期时间表 calendar(dt) 然后其中放上所有的时间。或者直接用 select union来做个所有日期的列表。
但如果是到5分钟组则这种方法就不推荐了。一般细到分钟级则建议用程序来实现，或者用存储过程。

Answer 3

俺就是不太熟悉mysql的存储过程，楼上的高手，帮忙写一个嘛！！散分，散分

Answer 4

简单示例：
DELIMITER $$CREATE PROCEDURE `test`.`gtime`()
   
    BEGIN
declare i datetime default '2009-7-10 00:00:00';
while i<='2009-7-10 00:10:00' do
insert into testtime values (i);
set i=DATE_ADD(i,INTERVAL 5 MINUTE);
end while;
    END$$DELIMITER ;

Answer 5

mysql> select * from t_zjcdxx2000;
+---------+---------------------+-------+-----------+
| data_id | createtime          | total | device_id |
+---------+---------------------+-------+-----------+
|     101 | 2009-06-29 10:24:43 |    10 | 158       |
|     102 | 2009-06-29 09:38:57 |    10 | 158       |
|     103 | 2009-06-29 10:25:07 |    10 | 158       |
|     104 | 2009-06-29 09:35:09 |    10 | 158       |
|     105 | 2009-06-29 09:30:35 |    10 | 158       |
...
|     199 | 2009-06-29 10:04:59 |    10 | 158       |
|     200 | 2009-06-29 09:57:34 |    10 | 158       |
+---------+---------------------+-------+-----------+
100 rows in set (0.00 sec)mysql> select CAST(FLOOR(createtime/500)*500 AS datetime) ,sum(total),count(*)
    -> from t_zjcdxx2000
    -> group by CAST(FLOOR(createtime/500)*500 AS datetime) ;
+---------------------------------------------+------------+----------+
| CAST(FLOOR(createtime/500)*500 AS datetime) | sum(total) | count(*) |
+---------------------------------------------+------------+----------+
| 2009-06-29 09:30:00                         |        130 |       13 |
| 2009-06-29 09:35:00                         |        110 |       11 |
| 2009-06-29 09:40:00                         |         80 |        8 |
| 2009-06-29 09:45:00                         |         60 |        6 |
| 2009-06-29 09:50:00                         |         70 |        7 |
| 2009-06-29 09:55:00                         |         60 |        6 |
| 2009-06-29 10:00:00                         |         80 |        8 |
| 2009-06-29 10:05:00                         |         80 |        8 |
| 2009-06-29 10:10:00                         |         40 |        4 |
| 2009-06-29 10:15:00                         |         80 |        8 |
| 2009-06-29 10:20:00                         |         90 |        9 |
| 2009-06-29 10:25:00                         |        120 |       12 |
+---------------------------------------------+------------+----------+
12 rows in set (0.00 sec)mysql>
mysql> delimiter //
mysql> CREATE PROCEDURE p_zjcdxx2000(t1 datetime,t2 datetime)
    -> BEGIN
    ->   DECLARE done INT DEFAULT 0;
    ->   DECLARE a datetime;
    ->   DECLARE b,c int;
    ->   DECLARE t datetime;
    ->   DECLARE cur1 CURSOR FOR
    ->          select CAST(FLOOR(createtime/500)*500 AS datetime) as dt,
    ->                  sum(total) as f1,count(*) as f2
    ->  from t_zjcdxx2000
    ->  group by dt;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->   OPEN cur1;
    ->   set t=t1;
    ->   create  TEMPORARY table IF NOT EXISTS tmp(createtime datetime,st int,cnt int);
    ->   delete from tmp;
    ->
    ->   REPEAT
    ->     FETCH cur1 INTO a, b,c;
    ->     IF NOT done THEN
    ->          WHILE t<a DO
    ->                  insert into tmp values (t,0,0);
    ->                  set t=t + interval 5 minute;
    ->          END WHILE;
    ->          insert into tmp values (a,b,c);
    ->          set t=t + interval 5 minute;
    ->     END IF;
    ->   UNTIL done END REPEAT;
    ->
    ->  WHILE t<t2 DO
    ->          insert into tmp values (t,0,0);
    ->          set t=t + interval 5 minute;
    ->  END WHILE;
    ->
    ->   CLOSE cur1;
    ->
    ->   select * from tmp;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call p_zjcdxx2000('2009-06-29','2009-06-30');
+---------------------+------+------+
| createtime          | st   | cnt  |
+---------------------+------+------+
| 2009-06-29 00:00:00 |    0 |    0 |
| 2009-06-29 00:05:00 |    0 |    0 |
| 2009-06-29 00:10:00 |    0 |    0 |
....
| 2009-06-29 09:20:00 |    0 |    0 |
| 2009-06-29 09:25:00 |    0 |    0 |
| 2009-06-29 09:30:00 |  130 |   13 |
| 2009-06-29 09:35:00 |  110 |   11 |
| 2009-06-29 09:40:00 |   80 |    8 |
| 2009-06-29 09:45:00 |   60 |    6 |
| 2009-06-29 09:50:00 |   70 |    7 |
| 2009-06-29 09:55:00 |   60 |    6 |
| 2009-06-29 10:00:00 |   80 |    8 |
| 2009-06-29 10:05:00 |   80 |    8 |
| 2009-06-29 10:10:00 |   40 |    4 |
| 2009-06-29 10:15:00 |   80 |    8 |
| 2009-06-29 10:20:00 |   90 |    9 |
| 2009-06-29 10:25:00 |  120 |   12 |
| 2009-06-29 10:30:00 |    0 |    0 |
....
| 2009-06-29 23:40:00 |    0 |    0 |
| 2009-06-29 23:45:00 |    0 |    0 |
| 2009-06-29 23:50:00 |    0 |    0 |
| 2009-06-29 23:55:00 |    0 |    0 |
+---------------------+------+------+
288 rows in set (4.13 sec)Query OK, 0 rows affected, 1 warning (4.41 sec)mysql>