mysql数据统计脚本_Mysql数据统计脚本模板

数据统计 对于小型业务系统可能不需要集群计算。约定计算脚本，该脚本特点：

1.动态传入时间变量。便于重算

2.扑捉计算结果状态，执行时间

3.计算结果入库

--统计脚本

CREATE DEFINER = `mc`@`10.%.%.%` PROCEDURE `NewProc`(IN v_countDate DATE)

BEGIN

-- 输入参数变量(v_beginTime:执行开始时间；v_endTime:执行结束时间;v_countDate：查询时间，默认1天前;v_date_type:时间周期)

DECLARE v_beginTime DATETIME DEFAULT NOW();

DECLARE v_endTime DATETIME;

DECLARE v_date_type VARCHAR(20) DEFAULT 'day';

DECLARE v_status int(11) DEFAULT 1;

DECLARE v_log_title VARCHAR(100) DEFAULT 'proc_stat_service_idc_ip_tcp_day';

-- 游标结果变量

DECLARE r_udate DATETIME;

DECLARE r_date_type VARCHAR(20);

DECLARE r_service VARCHAR(100);

DECLARE r_idc VARCHAR(100);

DECLARE r_ip VARCHAR(100);

DECLARE r_val_max FLOAT(20,2);

DECLARE r_val_avg FLOAT(20,2);

-- 遍历数据结束标志

DECLARE done INT DEFAULT FALSE;

-- 游标

DECLARE cur CURSOR FOR

SELECT v_countDate AS udate,v_date_type AS date_type, service, idc,ip, AVG(value_avg),MAX(value_max)

FROM view_mc_stat_trends WHERE itemname='Established connections' AND DATE(udate)=v_countDate GROUP BY ip;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_status=0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop:LOOP

FETCH cur INTO r_udate,r_date_type,r_service,r_idc,r_ip,r_val_avg,r_val_max;

IF done THEN

LEAVE read_loop;

END IF;

REPLACE INTO mc_stat_service_idc_ip_tcp(udate,date_type,service,idc,ip,val_avg,val_max) VALUES(r_udate,r_date_type,r_service,r_idc,r_ip,r_val_avg,r_val_max);

END LOOP;

CLOSE cur;

-- 结束时间

SET v_endTime = NOW();

-- 添加JOB执行日志

INSERT INTO t_joblog(beginDate,endDate,jobName,jobType,STATUS,haoshi,createDate) VALUES(v_beginTime,v_endTime,v_log_title,'PROCEDURE',v_status,v_endTime-v_beginTime,NOW());

END;

--定时执行event

CREATE DEFINER=`root`@`localhost`

EVENT `NewEvent`

ON SCHEDULE EVERY 1 DAY STARTS '2015-03-25 03:20:00'

ON COMPLETION PRESERVE

ENABLE

DO

CALL proc_stat_service_tcp_day(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'));