mysql event 入门

 

delimiter |
CREATE 
    EVENT statistics_event 
    ON SCHEDULE 
        EVERY 1 DAY 
        STARTS CONCAT(CURRENT_DATE(), ' 00:00:00') 
        #STARTS '2016-01-22 17:42:00'
    ON COMPLETION PRESERVE
    ENABLE 
    COMMENT '统计数据' 
    DO
        BEGIN
        DECLARE l_is_initialize INT(11);

        DECLARE l_yesterday VARCHAR(10);

        DECLARE l_yesterday_off_net_num INT(11);
        DECLARE l_yesterday_registered_num INT(11);
        DECLARE l_yesterday_con INT(11);
        DECLARE l_yesterday_active_user INT(11);
        DECLARE l_yesterday_per_con INT(11);

        DECLARE l_total_off_net_num INT(11);
        DECLARE l_total_registered_num INT(11);
        DECLARE l_total_con INT(11);
        DECLARE l_total_active_user INT(11);
        DECLARE l_total_per_con INT(11);

        DECLARE l_last_channel_id INT(11);
        DECLARE l_last_status_id INT(11);

        select AUTO_INCREMENT into l_last_channel_id from INFORMATION_SCHEMA.TABLES where TABLE_NAME='stat_channel';
        select AUTO_INCREMENT into l_last_status_id from INFORMATION_SCHEMA.TABLES where TABLE_NAME='stat_exe_status';

        INSERT INTO stat_exe_status(id,event_scheduler,start_time,status,create_time,update_time) 
         VALUES (l_last_status_id,'统计event，每天零点执行',CURRENT_TIME(),1,CURRENT_TIME(),CURRENT_TIME());

        # 首次时 昨日统计数 means 截止昨日统计数
        SELECT count(1) INTO l_is_initialize from stat_channel;
        # 获取昨天日期字符串 2016-01-01
        SELECT CURRENT_DATE () - INTERVAL 1 DAY INTO l_yesterday ;

        IF l_is_initialize > 0 THEN 
            # 昨日离网人数
            SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) = l_yesterday;
            # 昨天新注册用户数
            SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) = l_yesterday;
            # 昨天咨询数
            SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) = l_yesterday;
            # 昨日活跃数
            SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) = l_yesterday  GROUP BY PATIENT_ID  ) t;
            # 昨日转换人数
            SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) = l_yesterday and EXISTS (SELECT * from crm_order t2 where t1.patientID = t2.patientid and SUBSTR(t2.updatetime, 1, 10) = l_yesterday and t2.status = 2);
        ELSE 
        # 首次：截止昨日

            # 截止昨日离网人数
            SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) <= l_yesterday;
            # 截止昨天新注册用户数
            SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) <= l_yesterday;
            # 截止昨天咨询数
            SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) <= l_yesterday;
            # 截止昨日活跃数
            SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) <= l_yesterday  GROUP BY PATIENT_ID  ) t;
            # 截止昨日转换人数
            SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) <= l_yesterday and EXISTS (SELECT * from crm_order t2 where t1.patientID = t2.patientid and SUBSTR(t2.updatetime, 1, 10) <= l_yesterday and t2.status = 2);
        END IF;



        INSERT INTO stat_channel (
            id,
            yesterday_off_net_num,
            yesterday_registered_num,
            yesterday_con,
            yesterday_active_user,
            yesterday_per_con,
            day
        ) VALUES(
            l_last_channel_id,
            l_yesterday_off_net_num,
            l_yesterday_registered_num,
            l_yesterday_con,
            l_yesterday_active_user,
            l_yesterday_per_con,
            CURRENT_TIME()
        );

             
        SELECT 
            SUM(yesterday_off_net_num),
            SUM(yesterday_registered_num),
            SUM(yesterday_con),
            SUM(yesterday_active_user),
            SUM(yesterday_per_con)
        INTO 
            l_total_off_net_num,
            l_total_registered_num,
            l_total_con,
            l_total_active_user,
            l_total_per_con
        FROM stat_channel;



        UPDATE stat_channel SET
            total_off_net_num = l_total_off_net_num,
            total_registered_num = l_total_registered_num,
            total_con = l_total_con,
            total_active_user = l_total_active_user,
            total_per_con = l_total_per_con
        where id = l_last_channel_id;

        UPDATE stat_exe_status SET
            end_time = CURRENT_TIME(),
            `status` = 2,
            update_time = CURRENT_TIME()
        WHERE id = l_last_status_id;

        END|
delimiter ;