数据统计SQL备忘

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

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、按天分组，查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;