mysql 在不同数据库中创建定时同步任务，将每分钟数据转每5分钟统计

SHOW EVENTS ;
ALTER EVENT e_tdw_realtime_bid_win_ratio_tax ON COMPLETION PRESERVE DISABLE;


-- 创建一个存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS p_tdw_realtime_bid_win_ratio_tax//
CREATE PROCEDURE p_tdw_realtime_bid_win_ratio_tax() 
BEGIN
REPLACE INTO data1.tdw_realtime_bid
    select 
     case when substring(c.c_time,11,2) >= '00' and substring(c.c_time,11,2) < '05' then CONCAT(SUBSTR(c.c_time,1,10),'05')
     when substring(c.c_time,11,2) >= '05' and substring(c.c_time,11,2) < '10' then CONCAT(SUBSTR(c.c_time,1,10),'10') 
     when substring(c.c_time,11,2) >= '10' and substring(c.c_time,11,2) < '15' then CONCAT(SUBSTR(c.c_time,1,10),'15') 
     when substring(c.c_time,11,2) >= '15' and substring(c.c_time,11,2) < '20' then CONCAT(SUBSTR(c.c_time,1,10),'20') 
     when substring(c.c_time,11,2) >= '20' and substring(c.c_time,11,2) < '25' then CONCAT(SUBSTR(c.c_time,1,10),'25')
     when substring(c.c_time,11,2) >= '25' and substring(c.c_time,11,2) < '30' then CONCAT(SUBSTR(c.c_time,1,10),'30')
     when substring(c.c_time,11,2) >= '30' and substring(c.c_time,11,2) < '35' then CONCAT(SUBSTR(c.c_time,1,10),'35') 
     when substring(c.c_time,11,2) >= '35' and substring(c.c_time,11,2) < '40' then CONCAT(SUBSTR(c.c_time,1,10),'40') 
     when substring(c.c_time,11,2) >= '40' and substring(c.c_time,11,2) < '45' then CONCAT(SUBSTR(c.c_time,1,10),'45') 
     when substring(c.c_time,11,2) >= '45' and substring(c.c_time,11,2) < '50' then CONCAT(SUBSTR(c.c_time,1,10),'50')
     when substring(c.c_time,11,2) >= '50' and substring(c.c_time,11,2) < '55' then CONCAT(SUBSTR(c.c_time,1,10),'55')
     when substring(c.c_time,11,2) >= '55' and substring(c.c_time,11,2) <= '59' then 
     DATE_FORMAT( DATE_ADD(concat(SUBSTR(c.c_time,1,4),'-',SUBSTR(c.c_time,5,2),'-',SUBSTR(c.c_time,7,2),' ',SUBSTR(c.c_time,9,2)),INTERVAL 1 Hour) ,'%Y%m%d%H%i' )
     end    c_time
     ,'3' as c_platform_id
     , round(avg(ratio),2) as c_bid_win_ratio 
     ,now() as c_sync_time 
    from (
        select 
a.c_time 
, cast(truncate(a.c_cnt / b.c_cnt * 100,2) as char) as ratio 
from data2.today_exposure_detail_order a 
, (
select c_time as c_time 
, sum(c_cnt) as c_cnt 
from wead_dsp_stat.stat_min_order_bid_resp 
where concat(SUBSTRING(c_time,1,4),'-',SUBSTRING(c_time,5,2) ,'-',SUBSTRING(c_time,7,2)) between SUBSTR(DATE_ADD(now(),INTERVAL -2 day) ,1,10)  and SUBSTR(now() ,1,10)
and c_platform_id = 3 
group by c_time
) b 
where a.c_platform_id = 3 
and a.c_time = b.c_time 
group by a.c_time 
     ) c 
     -- and concat(SUBSTRING(c.c_time,1,4),'-',SUBSTRING(c.c_time,5,2) ,'-',SUBSTRING(c.c_time,7,2)) between SUBSTR(DATE_ADD(now(),INTERVAL -2 day) ,1,10)  and SUBSTR(now() ,1,10)
     GROUP BY 
     case when substring(c.c_time,11,2) >= '00' and substring(c.c_time,11,2) < '05' then CONCAT(SUBSTR(c.c_time,1,10),'05')
     when substring(c.c_time,11,2) >= '05' and substring(c.c_time,11,2) < '10' then CONCAT(SUBSTR(c.c_time,1,10),'10') 
     when substring(c.c_time,11,2) >= '10' and substring(c.c_time,11,2) < '15' then CONCAT(SUBSTR(c.c_time,1,10),'15') 
     when substring(c.c_time,11,2) >= '15' and substring(c.c_time,11,2) < '20' then CONCAT(SUBSTR(c.c_time,1,10),'20') 
     when substring(c.c_time,11,2) >= '20' and substring(c.c_time,11,2) < '25' then CONCAT(SUBSTR(c.c_time,1,10),'25')
     when substring(c.c_time,11,2) >= '25' and substring(c.c_time,11,2) < '30' then CONCAT(SUBSTR(c.c_time,1,10),'30')
     when substring(c.c_time,11,2) >= '30' and substring(c.c_time,11,2) < '35' then CONCAT(SUBSTR(c.c_time,1,10),'35') 
     when substring(c.c_time,11,2) >= '35' and substring(c.c_time,11,2) < '40' then CONCAT(SUBSTR(c.c_time,1,10),'40') 
     when substring(c.c_time,11,2) >= '40' and substring(c.c_time,11,2) < '45' then CONCAT(SUBSTR(c.c_time,1,10),'45') 
     when substring(c.c_time,11,2) >= '45' and substring(c.c_time,11,2) < '50' then CONCAT(SUBSTR(c.c_time,1,10),'50')
     when substring(c.c_time,11,2) >= '50' and substring(c.c_time,11,2) < '55' then CONCAT(SUBSTR(c.c_time,1,10),'55')
     when substring(c.c_time,11,2) >= '55' and substring(c.c_time,11,2) <= '59' then 
     DATE_FORMAT( DATE_ADD(concat(SUBSTR(c.c_time,1,4),'-',SUBSTR(c.c_time,5,2),'-',SUBSTR(c.c_time,7,2),' ',SUBSTR(c.c_time,9,2)),INTERVAL 1 Hour) ,'%Y%m%d%H%i' )
end 
;
END//




-- 设置定时任务调用这个存储过程（从2017-11-30 08:15:00 点每81秒执行一次）
DELIMITER //
DROP EVENT IF EXISTS e_tdw_realtime_bid_win_ratio_tax//
CREATE EVENT e_tdw_realtime_bid_win_ratio_tax
ON SCHEDULE EVERY 81 second STARTS TIMESTAMP '2017-11-30 08:15:00'
ON COMPLETION PRESERVE
DO
BEGIN
CALL p_tdw_realtime_bid_win_ratio_tax();
END//