1、统计9月注册角色首次充值时的游戏时长分布（分钟,人数），单位：分钟
10年积累的
网站建设、网站制作经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先
做网站设计后付款的网站建设流程，更有
玛沁免费网站建设让你可以放心的选择与我们合作。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;
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
文章题目：数据统计SQL备忘-创新互联
浏览路径：
http://cdkjz.cn/article/eohoc.html
