Mysql數據統計腳本模板

CREATE DEFINER = `mc`@`10.%.%.%` PROCEDURE `NewProc`(IN v_countDate DATE)
BEGIN
/* =======================================================================
	 * 系統名 : **數據平臺
	 * 子系統名 : 報告統計-獨立IP-TCP連接數(天)
	 * PROC名 : proc_stat_service_idc_ip_tcp_day
	 * 概要 : 每天從mc_stat_trends 按服務器IP統計獨立IP TCP連接數的平均值和最大值指標。
	 * 改版履歷 :
	 * 版本 日期 作者名 備注
	 * 1.0.1 2015.03.19 #### 初次作成
	 * ======================================================================= */
-- 輸入參數變量(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;

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'));