Chinaunix首页 | 论坛 | 博客
  • 博客访问: 487945
  • 博文数量: 279
  • 博客积分: 4467
  • 博客等级: 上校
  • 技术积分: 2830
  • 用 户 组: 普通用户
  • 注册时间: 2007-04-03 14:43
文章分类

全部博文(279)

文章存档

2013年(1)

2012年(39)

2011年(35)

2009年(29)

2008年(131)

2007年(44)

分类:

2009-07-08 10:22:59

 
日登陆用户量
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
 
阅读(366) | 评论(0) | 转发(0) |
0

上一篇:History

下一篇:更新服务器版本步骤

给主人留下些什么吧!~~