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 ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值