1、统计9月注册角色首次充值时的游戏时长分布（分钟,人数），单位：分钟

SELECTsub.minutes,Count(roleId)AScountFROM(SELECTpr.roleId,Timestampdiff(MINUTE,Max(player_login.logTime),pr.logTime)+Ifnull(Max(player_logout.totalOnlineMins),0)ASminutesFROM(SELECTlogTime,roleIdFROMplayer_rechargeWHEREcreateTime>='2017-09-01'ANDcreateTime<'2017-10-01'ANDrechargeTimes=1)ASprLEFTJOINplayer_logoutONplayer_logout.roleId=pr.roleIdLEFTJOINplayer_loginONplayer_login.roleId=pr.roleIdWHEREplayer_logout.logTime<pr.logTimeANDplayer_login.logTime<pr.logTimeGROUPBYpr.roleId)ASsubWHEREsub.minutes>0GROUPBYsub.minutes;

2、按天分组，查9月每天付费前10排行(日期,付费金额,排名,角色ID)

SELECTdateAS'date',payAS'pay',rank,roleIdFROM(SELECTzl_tmp.roleId,zl_tmp.date,zl_tmp.pay,@rownum:=@rownum+1,IF(@date=zl_tmp.date,@rank:=@rank+1,@rank:=1)AS'rank',@date:=zl_tmp.dateFROM(SELECTroleId,SUM(cash)AS'pay',DATE_FORMAT(logTime,'%Y-%m-%d')AS'date'FROMplayer_rechargeWHERElogTime>='2017-09-01'ANDlogTime<'2017-10-01'GROUPBYdate,roleIdORDERBYdate,payDESC)zl_tmp,(SELECT@rownum:=0,@date:=NULL,@rank:=0)a)resultHAVINGrank<=10;

3、统计9月每日付费转化率(日期,活跃用户数,付费用户数)

SELECTpl.date,pl.plCountASactiveAccoCount,COALESCE(pr.prCount,0)ASpayAccoCountFROM(SELECTDate_format(logTime,'%Y-%m-%d')ASdate,Count(DISTINCTroleId)ASplCountFROMplayer_loginWHERElogTime>='2017-09-01'ANDlogTime<'2017-10-01'GROUPBYdate)ASplLEFTJOIN(SELECTDate_format(logTime,'%Y-%m-%d')ASdate,Count(DISTINCTroleId)ASprCountFROMplayer_rechargeWHERElogTime>='2017-09-01'ANDlogTime<'2017-10-01'GROUPBYdate)ASprONpl.date=pr.date;