Mysql Data Statistics Script Template

Source: Internet
Author: User
Data Statistics may not require Cluster Computing for small business systems. Agreed computing script. Features: 1. dynamically pass in time variables. Easy to recalculate 2. Capture the computing result status, execution time 3. Import the calculation result to the database MySQLCREATEDEFINER 'mc '@ '10. %' PROCEDURE 'newproc' (INv_countDateDATE) BEGIN *

Data Statistics may not require Cluster Computing for small business systems. Agreed computing script. Features: 1. dynamically pass in time variables. Easy to recalculate 2. capture the computing result status. execution time: 3. mySQL create definer = 'mc '@ '10. %. %. % 'Procedure 'newproc' (IN v_countDate DATE) BEGIN/* ==========

Data Statistics may not require Cluster Computing for small business systems. Agreed computing script, which has the following features:
1. dynamically input time variables. Easy to recalculate
2. Capture the computing result status and execution time
3. input the computing result to MySQL
Create definer = 'mc '@ '10. %. %. % 'Procedure 'newproc' (IN v_countDate DATE) BEGIN/* ================================================= ========================================= * system name: ** Data Platform * subsystem name: Report statistics-number of independent IP-TCP connections (days) * PROC name: proc_stat_service_idc_ip_tcp_day * Summary: the average value and maximum value of the number of TCP connections of an independent IP address are counted from mc_stat_trends each day by Server IP address. * Revised resume: * version Date Author name remarks * 1.0.1 2015.03.19 #### initial result * ====================== ========================================================== =======*/-- input parameter variable (v_beginTime: execution start time; v_endTime: Execution end time; v_countDate: Query time, 1 day by DEFAULT; v_date_type: time period) 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 '; -- The cursor result variable 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 distinct FLOAT (20, 2); -- DECLARE done int default false; -- cursor declare cur cursor forselect 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; -- bind the end sign to the cursor declare continue handler for sqlexception set v_status = 0; declare continue handler for not found set done = TRUE; OPEN cur; read_loop: LOOPFETCH cur INTO r_udate, r_date_type, r_service, r_idc, r_ip, r_val_avg, r_val_max; IF done THENLEAVE read_loop; end if; replace into values (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; -- END time SET v_endTime = NOW (); -- add the insert into t_joblog (beginDate, endDate, jobName, jobType, STATUS, haoshi, createDate) VALUES (v_beginTime, v_endTime, v_log_title, 'processed', 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 PRESERVEENABLEDOCALL proc_stat_service_tcp_day(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'));

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.