最近在做关于监控的项目,其中关于按照一定时间(小时,天、周)实时统计并显示默写特定值的数据量。通过sql语句查询出现以下问题:
例子:(由于自己的表结构比较复杂,此处例子应用网上)
例子中的数据结构和数据:
一般使用如下查询语句:
SELECT date(downtime) AS dday, count(*) AS num FROM re_device GROUP BY dday
得到的结果是,如果那天没有数据,就没有那天的记录,有时我们希望没有数据的那天也有记录,只是数值为0.
希望结果:
如何解决:
如果按照天统计数据量,如上面的例子,读者可以查看一下博客http://blog.csdn.net/jie11447416/article/details/50887888
我的需求是按照小时来统计数据量,希望得到的统计结果:
解决办法:
1、建立一张24小时的表
CREATE TABLE num_minute (i int);
delimiter //
drop procedure if exists wk;
create procedure wk()
begin
declare num int;
set num = 1;
while num < 1440 do
insert into num_minute values (num);
set num = num +1;
end while;
end
//
call wk();
基于以上表,创建24小时表
CREATE TABLE if not exists calendar_hour(datelist varchar(10));
INSERT INTO calendar_hour(datelist) SELECT
DATE_FORMAT(
adddate(
"2017-01-01 00:00",
INTERVAL 1 MINUTE
),'%H:%i') AS `date`
FROM
(
SELECT
n1.i AS id
FROM
num_minute n1
) AS numlist;
2、联合查询
select
time_hour_minute,
COUNT(*) - 1 AS 'count'
from
(
select
DATE_FORMAT(from_unixtime(floor(UNIX_TIMESTAMP(ts)/60)*60),'%H:%i') as time_hour_minute
from
zookeeper
where DATE_SUB(NOW(), INTERVAL 1 hour) <= ts&&ts<=NOW() and typeDescription like '%FIN%'
UNION ALL
SELECT
datelist
FROM
calendar_hour
where TIME_TO_SEC(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 hour),'%H:%i'))<TIME_TO_SEC(datelist)&&TIME_TO_SEC(datelist)<=TIME_TO_SEC(CURTIME())
) a
group by time_hour_minute;
统计最近一小时内,每分钟typeDescription为FIN的数据量,并且按照时间排序。
关于sql时间相关的操作查看:http://blog.csdn.net/yuebao1991/article/details/73113381
有什么问题可以留言,大家相互学习。