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