/*
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 ;
MySQL zabbix 流量统计分析脚本(内部业务需求)
原创
©著作权归作者所有:来自51CTO博客作者JeesonHuang的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
神策SDK不支持Windows客户端全埋点,怎么实现用户统计分析?
ClkLog针对神策不支持全埋点的客户端实现用户访问基础统计分析-1
C++客户端 C#客户端 C++客户端用户分析 C#客户端用户分析 windows客户端用户分析 -
shell脚本统计分析Nginx日志
#!/bin/bash#功能描述(Description):Nginx标准日志分析脚本.#统计信息包括:#1.页面访问量PV#2.用户量UV#3.人均
shell 分析nginx日志 访问量 状态码 日志分析