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<UNIX_TIMESTAMP(DATE_ADD(nowdate,INTERVAL 1 DAY))
  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 ;