SQL memo on data statistics

1. Statistics on the distribution of game duration (minutes, number of people) when registered characters are recharged for the first time in September. Unit: minutes.

SELECT sub.minutes, Count (roleId) AS countFROM (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 subWHERE sub.minutes >

0GROUP BY sub.minutes

2. Group by day, check the top 10 ranks of daily payment in September (date, payment amount, ranking, role 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,'% Ymuri% MMI% 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 = '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