資料統計SQL備忘

標籤：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;

本文出自 “zl1030的記錄” 部落格，請務必保留此出處http://zl1030.blog.51cto.com/274507/1964561

資料統計SQL備忘