MySQL--存储过程和event

1、统计单个接口， 某小时的数据量

DELIMITER $$USE `shbaobiao`$$DROP PROCEDURE IF EXISTS `uar4_hc_asdmpbak_proc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `uar4_hc_asdmpbak_proc`(IN it VARCHAR(30), IN dateStr VARCHAR(30), IN h VARCHAR(10))   BEGIN     INSERT INTO tmp_sum_http  SELECT DATE, HOUR, NetworkType, InterfaceType, SUM(filecount), SUM(recordcount), SUM(filesize)FROM uar4_hc_asdmpbak    WHERE  InterfaceType=it AND DATE=dateStr AND HOUR=h GROUP BY DATE, HOUR, NetworkType, InterfaceType;    END$$DELIMITER ;

1.2、调用存储过程

CALL uar4_hc_asdmpbak_proc('LTEHTTP', '20171102', '00');

1.3、使用event，定时执行,指定时间实行存储过程，统计一个小时的数据情况。

DELIMITER $$USE shbaobiao$$DROP EVENT IF EXISTS `e_test`$$CREATE  EVENT `e_test`ON SCHEDULE AT '2017-11-15 22:22:05' ON COMPLETION PRESERVE DO    BEGIN         CALL uar4_hc_asdmpbak_proc('LTEHTTP', '20171102', '01');    END$$DELIMITER ;

1.4、指定时间统计历史数据

DELIMITER $$USE shbaobiao$$DROP EVENT IF EXISTS `e_test`$$CREATE  EVENT `e_test`ON SCHEDULE AT '2017-11-15 22:39:05' ON COMPLETION PRESERVE DO    BEGIN         CALL uar4_hc_asdmpbak_proc('LTEHTTP', DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 13 DAY), '%Y%c%d'), DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 13 DAY), '%H'));    END$$DELIMITER ;

1.5、从指定时间开始，每小时进行一次统计

DELIMITER $$USE shbaobiao$$DROP EVENT IF EXISTS `e_test`$$CREATE  EVENT `e_test`-- 从现在开始每小时执行一次定时任务ON SCHEDULE EVERY 1 HOUR  STARTS CURRENT_TIMESTAMPON COMPLETION PRESERVE DO    BEGIN         CALL uar4_hc_asdmpbak_proc('LTEHTTP', DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 14 DAY), '%Y%c%d'), DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 14 DAY), '%H'));    END$$DELIMITER ;

案例2、 统计小时粒度的数据量趋势

查询的语句

SELECT Date, Hour, NetworkType, InterfaceType, SUM(filecount), SUM(recordcount), SUM(filesize)FROM uar4_hc_asdmpbak WHERE Date='20171101' AND InterfaceType='LTEHTTP'GROUP BY Date, Hour, NetworkType, InterfaceType

2.1、存储过程

DELIMITER $$USE `shbaobiao`$$DROP PROCEDURE IF EXISTS uar4_hc_asdmpbak_proc$$CREATE    /*[DEFINER = { user | CURRENT_USER }]*/    PROCEDURE `uar4_hc_asdmpbak_proc`(IN dateStr VARCHAR(30))    /*LANGUAGE SQL    | [NOT] DETERMINISTIC    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }    | SQL SECURITY { DEFINER | INVOKER }    | COMMENT 'string'*/    BEGIN    INSERT INTO tmp_sum_http  SELECT DATE, HOUR, NetworkType, InterfaceType, SUM(filecount), SUM(recordcount), SUM(filesize)FROM uar4_hc_asdmpbak     WHERE DATE=dateStr AND InterfaceType='LTEHTTP' GROUP BY DATE, HOUR, NetworkType, InterfaceType;    END$$DELIMITER ;

2.2、调用

CALL uar4_hc_asdmpbak_proc('20171104');