1、统计9月注册角色首次充值时的游戏时长分布（分钟,人数），单位：分钟
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
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月每日付费转化率(日期,活跃用户数,付费用户数)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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;
|
