根据一张表的两个时间的字段,根据对应字段生成其它三个字段,通过表的自联接可以做得到,但是会出现些意想不到的问题,最终解决的办法---通过存储过程。
数据库表
CREATE TABLE `box_mobile_log_active_` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boxId` int(11) NOT NULL,
`channelId` int(11) NOT NULL,
`fatherChannelId` int(11) NOT NULL,
`appId` int(11) NOT NULL,
`cpId` int(11) NOT NULL,
`active_day` datetime NOT NULL,
`createTime` datetime DEFAULT NULL,
`active_num` int(11) NOT NULL,
`theDay` int(11) DEFAULT '0',
`secondDay` int(11) DEFAULT '0',
`thirdDay` int(11) DEFAULT '0',
`active_num_rate` decimal(10,4) NOT NULL DEFAULT '0.0000',
`province` varchar(80) NOT NULL,
`city` varchar(80) NOT NULL,
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=113234 DEFAULT CHARSET=utf8;
存储过程 :
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;
REPEAT
FETCH 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 = 1 END REPEAT;
CLOSE v_activeList;
COMMIT;
END