1、统计9月注册角色首次充值时的游戏时长分布(分钟,人数),单位:分钟
SELECT sub.minutes, Count(roleId) AS count FROM (SELECT pr.roleId, Timestampdiff(MINUTE, Max(player_login.logTime), pr.logTime) + Ifnull(Max(player_logout.totalOnlineMins), 0) AS minutes FROM (SELECT logTime, roleId FROM player_recharge WHERE createTime >= '2017-09-01' AND createTime < '2017-10-01' AND rechargeTimes = 1) AS pr LEFT JOIN player_logout ON player_logout.roleId = pr.roleId LEFT JOIN player_login ON player_login.roleId = pr.roleId WHERE player_logout.logTime < pr.logTime AND player_login.logTime < pr.logTime GROUP BY pr.roleId) AS sub WHERE sub.minutes > 0 GROUP BY sub.minutes;
2、按天分组,查9月每天付费前10排行(日期,付费金额,排名,角色ID)
SELECT date AS 'date', pay AS 'pay', rank, roleId FROM ( SELECT zl_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.date FROM ( SELECT roleId, SUM(cash) AS 'pay', DATE_FORMAT(logTime, '%Y-%m-%d') AS 'date' FROM player_recharge WHERE logTime >= '2017-09-01' AND logTime < '2017-10-01' GROUP BY date, roleId ORDER BY date, pay DESC ) zl_tmp, ( SELECT @rownum := 0, @date := NULL, @rank := 0 ) a ) result HAVING rank <= 10;
3、统计9月每日付费转化率(日期,活跃用户数,付费用户数)
SELECT pl.date, pl.plCount AS activeAccoCount, COALESCE(pr.prCount, 0) AS payAccoCount FROM ( SELECT Date_format(logTime, '%Y-%m-%d') AS date, Count(DISTINCT roleId) AS plCount FROM player_login WHERE logTime >= '2017-09-01' AND logTime < '2017-10-01' GROUP BY date ) AS pl LEFT JOIN ( SELECT Date_format(logTime, '%Y-%m-%d') AS date, Count(DISTINCT roleId) AS prCount FROM player_recharge WHERE logTime >= '2017-09-01' AND logTime < '2017-10-01' GROUP BY date ) AS pr ON pl.date = pr.date;
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。