日登陆用户量
select count(sm_accountName) from tal_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-09"
and from_unixtime(sm_lastLoginTime)<"2009-02-10"
日用户职业选择比例
select sm_occupation,count(sm_occupation) from tbl_Player
where from_unixtime(sm_createDate) >"2009-02-09"
group by sm_occupation
日流失用户
select count(id) from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-09"
and from_unixtime(sm_lastLoginTime)<"2009-02-10"
and id not in (select id from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-10"
and from_unixtime(sm_lastLoginTime)<"2009-02-11")
日流失用户职业比例
select sm_value,count(sm_value) from tbl_Account_occupation
where parentID in (
select id from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-09"
and from_unixtime(sm_lastLoginTime)<"2009-02-10"
and id not in (select id from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-10"
and from_unixtime(sm_lastLoginTime)<"2009-02-11"))
group by sm_value
日流失用户等级比例
select sm_value,count(sm_value) from tbl_Account_level
where parentID in (
select id from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-09"
and from_unixtime(sm_lastLoginTime)<"2009-02-10"
and id not in (select id from tbl_Account
where from_unixtime(sm_lastLoginTime)>"2009-02-10"
and from_unixtime(sm_lastLoginTime)<"2009-02-11"))
group by sm_value
日用户等级分布
select a.sm_value,b.sm_value,count(a.sm_value) from tbl_Account_occupation a,tbl_Account_level b
group by a.sm_value,b.sm_value
begin
declare pid int;
declare acid int;
declare tmpid varchar(20) default '' ;
declare cur1 CURSOR FOR select distinct parentID from tbl_Account_dbId;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpid = null;
OPEN cur1;
FETCH cur1 INTO acid;
WHILE (tmpid is not null) DO
select tbl_Player.id into pid from tbl_Player,tbl_Account_dbId where tbl_Account_dbId.parentID=acid and tbl_Player.id=tbl_Account_dbId.sm_value and tbl_Player.sm_isOnline=0 order BY tbl_Player.sm_level desc limit 1;
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[21,10,0,64,5,0.0000,0,''8-21-101233591540689'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[21,20,0,64,5,0.0000,0,''8-21-20-1233591634529'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,30,0,64,30,0.0000,1,''8-8-30-1233591649743'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,50,0,64,30,0.0000,2,''8-8-50-1233591669218'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,60,0,64,30,0.0000,3,''8-8-60-1233591674828'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,10,0,64,30,0.0000,0,''8-8-10-1233592291655'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,20,0,64,30,0.0000,1,''8-8-20-1233592296278'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
insert into tbl_Player_Mails (parentID,sm_content,sm_free,sm_item,sm_money,sm_readflag,sm_title,sm_sendtime,sm_sendName,sm_system) values (pid,' ','1','[8,40,0,64,30,0.0000,2,''8-8-40-1233592299826'']',' ',0,'',UNIX_TIMESTAMP(sysdate()),'system','1');
FETCH cur1 INTO acid;
END WHILE;
CLOSE cur1;
end
