表根据对应字段生成其它三个字段
use box_oms;CREATE PROCEDURE `sp_box_mobile_active_log_test`(IN v_day int)BEGIN DECLARE v_done1 INT DEFAULT 0; DECLARE v_theDay,v_secondDay,v_thirdDay,v_active_num,v_active_num_rate INT DEFAULT 0; DECLARE v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId INT; DECLARE v_createTime,v_active_day varchar(10); DECLARE v_province,v_city varchar(20); DECLARE v_activeList CURSOR FOR SELECT a.boxId boxId,c.id channelId,c.fatherId fatherChannelId,a.appId appId,d.cpId cpId,DATE(a.installTime),DATE(a.updateTime) active_day,COUNT(a.id) active_num,COUNT(a.id) active_num,a.province province,a.city city FROM box_mobile_log_sum a,box_market.channel_box b,box_market.channel_box_chinfo c,box_market.res_app d WHERE a.appId = d.id AND a.boxId = b.boxId AND b.channelId=c.id GROUP BY DATE(a.installTime),DATE(a.updateTime),a.boxId,a.appId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1 = 1; INSERT IGNORE INTO box_mobile_log_sum SELECT * FROM box_log.box_mobile_log a where a.updateTime>DATE_SUB(CURDATE(),INTERVAL v_day DAY) AND a.updateTime<=DATE_SUB(CURDATE(),INTERVAL -1 DAY); CALL sp_filter(2);-- CALL sp_rehab_mobile_active_installday;-- 生成激活统计表TRUNCATE TABLE box_mobile_log_active_; OPEN v_activeList; REPEATFETCH v_activeList INTO v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId,v_createTime,v_active_day,v_active_num,v_active_num_rate,v_province,v_city; IF v_done1 != 1 THEN IF to_days(v_createTime)=to_days(v_active_day) THEN SET v_theDay=v_active_num; ELSEIF (to_days(v_createTime)+1)=to_days(v_active_day) THEN SET v_secondDay=v_active_num; ELSEIF (to_days(v_createTime)+2)=to_days(v_active_day) THEN SET v_thirdDay=v_active_num; END IF; INSERT INTO box_mobile_log_active_ (boxId,channelId,fatherChannelId,appId,cpId,createTime,active_day,active_num,active_num_rate,province,city,theDay,secondDay,thirdDay) VALUES(v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId,v_createTime,v_active_day,v_active_num,v_active_num_rate,v_province,v_city,v_theDay,v_secondDay,v_thirdDay); SET v_theDay=0; SET v_secondDay=0; SET v_thirdDay=0; END IF; UNTIL v_done1 = 1END REPEAT; CLOSE v_activeList; COMMIT;END
?
