mysql关于用户留几日存率的统计，怎么做啊？

0 0

mysql关于用户留几日存率的统计，怎么做啊？10

本来已经做好的，但是老板要改需求。
假如说从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<date_add(curdate,interval 2 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 2 day) and loginTime<date_add(curdate,interval 3 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 3 day) and loginTime<date_add(curdate,interval 4 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 4 day) and loginTime<date_add(curdate,interval 5 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 5 day) and loginTime<date_add(curdate,interval 6 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(select ((select count(distinct playerId) from log_login where registerTime=current_date() and loginTime>=date_add(curdate(),interval 6 day) and loginTime<date_add(curdate,interval 7 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=current_date() and areaId=@AID))*100),
(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

数据结构数据库

2013年4月19日 14:44

bqmcjl
1
0 0 10

1个答案按时间排序按投票排序

0 0

采纳的答案

假如说从12号开始。
12号的次日留存率为0，三日留存为0，七日留存为0.
13号的数据一出来，12号的次日留存就改为x%。
14号的数据一出来，12号的三日留存就改为x%。
18号的数据一出来，12号的七日留存就改为x%。

直接在应用中做定时任务（如半夜三点执行对你性能造成不了多大影响）多好每天算一遍昨天的

以前我们做留存率都是分析日志完成的不读库

2013年4月19日 14:58