本来已经做好的,但是老板要改需求。
假如说从12号开始。
12号的次日留存率为0,三日留存为0,七日留存为0.
13号的数据一出来,12号的次日留存就改为x%。
14号的数据一出来,12号的三日留存就改为x%。
18号的数据一出来,12号的七日留存就改为x%。
我用的mysql数据库。这存储过程该怎么写啊?头疼。目前只写出来插入的了,也不知道对不对,修改的不知道怎么说,求大侠拔刀相助啊!!
BEGIN
declare numareaId int(10);
declare currentareaId int(10);
if !exists(select TABLE_NAME
from INFORMATION_SCHEMA
.TABLES
where TABLE_SCHEMA
='sgsi' and TABLE_NAME
='statistics_day_player') then
CREATE TABLE statistics_day_report
(
idx INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (idx),
areaId INT NOT NULL default 0,
newPlayer INT NOT NULL default 0,
createTime DATETIME,
oneDayPlayer double NOT NULL default 0,
twoDayPlayer double NOT NULL default 0,
threeDayPlayer double NOT NULL default 0,
fourDayPlayer double NOT NULL default 0,
fiveDayPlayer double NOT NULL default 0,
sixDayPlayer double NOT NULL default 0,
sevenDayPlayer double NOT NULL default 0
);
end if;
SELECT COUNT(areaId),MIN(areaId) INTO @a,@b FROM option_area_info;
SET numareaId=@a;
SET currentareaId=@b;
loop1:WHILE numareaId>0 DO
SET @AID = currentareaId;
SELECT count(distinct playerId) INTO @newplayer FROM log_login where registerTime=current_date() and areaId=@AID;
INSERT INTO statistics_day_player (areaId,newPlayer,createTime,oneDayPlayer,twoDayPlayer,threeDayPlayer,fourDayPlayer,fiveDayPlayer,sixDayPlayer,sevenDayPlayer) VALUES
(currentareaId,
@newplayer,
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 1 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 2 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 3 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 4 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 5 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 6 day) and loginTime
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 7 day) and loginTime<date_add(curdate,interval 8 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100)
);
SET numareaId=numareaId-1;
SET currentareaId=currentareaId+1;
END WHILE loop1;
END