mysql统计流量,MySQL zabbix 流量统计分析脚本（内部业务需求）

/*

zabbix 流量统计:统计某段日期中,每天9~21点每小时最大的5个流量值的平均值

CALL getFlow(startdate,enddate,isdetail);

CALL getFlow('2016-01-25','2016-02-01',1);

DROP PROCEDURE IF EXISTS getFlow;

*/

DELIMITER //

CREATE PROCEDURE getFlow(IN startdate DATE,IN enddate DATE,IN isdetail BIT(1))

BEGIN

DECLARE nowdate DATE;

DECLARE nowhour INT;

SET nowdate = startdate;

CREATE TEMPORARY TABLE tmp_history_uint(

clock INT(11),

value BIGINT(20) unsigned

);

WHILE nowdate<= enddate DO

SET nowhour = 9;

WHILE nowhour<= 21 DO

INSERT INTO tmp_history_uint(clock,value)

SELECT clock,value

FROM history_uint

WHERE itemid=23661

AND clock>=UNIX_TIMESTAMP(nowdate)

AND clock

AND HOUR(from_unixtime(clock))=nowhour

ORDER BY value DESC LIMIT 5;

SET nowhour = nowhour + 1;

END WHILE;

SET nowdate=DATE_ADD(nowdate,INTERVAL 1 DAY);

END WHILE;

IF isdetail = 1 THEN

SELECT

date_format(from_unixtime(clock),'%Y-%m-%d') "datetime"

,HOUR(from_unixtime(clock)) "hour"

,AVG(value) "avgvalue"

FROM tmp_history_uint

GROUP BY date_format(from_unixtime(clock),'%Y-%m-%d'),HOUR(from_unixtime(clock));

ELSE

SELECT SUM(avgvalue)/COUNT(avgvalue) AS "avgvalue"

FROM(

SELECT

date_format(from_unixtime(clock),'%Y-%m-%d') "datetime"

,HOUR(from_unixtime(clock)) "hour"

,AVG(value) "avgvalue"

FROM tmp_history_uint

GROUP BY date_format(from_unixtime(clock),'%Y-%m-%d'),HOUR(from_unixtime(clock))

) AS T;

END IF;

DROP TEMPORARY TABLE IF EXISTS tmp_history_uint;

END;//

DELIMITER ;