Java游戏服务器-留存/付费数据统计

游戏上线后,需要统计留存,付费等信息。留存一般关心次日留存,三日留存,周留存和月留存。付费包括平均用户付费等,平均付费用户付费等。

假设我们有以下两个表,分别是用户表(tb_player)和支付订单表(tb_billing_order):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `tb_player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
...
`create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
`last_login_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '上次登录时间',
...
PRIMARY KEY (`player_id`),
KEY `account` (`account`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_billing_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`player_id` int(11) NOT NULL DEFAULT '0' COMMENT '玩家ID',
...
`income` varchar(32) NOT NULL DEFAULT '' COMMENT '收入',
...
`complete_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '完成时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

所有统计数据都从其中产生。

首先创建一个记录统计数据的表(tb_stat_remain):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `tb_stat_remain` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`dnu` int(11) NOT NULL COMMENT '每日新增用户',
`dau` int(11) DEFAULT NULL COMMENT '每日活跃用户',
`dpr` int(11) DEFAULT NULL COMMENT '每日充值收入',
`dpu` int(11) DEFAULT NULL COMMENT '每日充值用户',
`r2` int(11) DEFAULT NULL COMMENT '次日留存',
`r3` int(11) DEFAULT NULL COMMENT '三日留存',
`r7` int(11) DEFAULT NULL COMMENT '周留存',
`r30` int(11) DEFAULT NULL COMMENT '月留存',
`stat_time` bigint(20) DEFAULT '0' COMMENT '统计时间',
`op_time` bigint(20) DEFAULT '0' COMMENT '操作时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

然后创建一个存储过程,用来实际统计数据:

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
CREATE PROCEDURE `proc_stat_remain`()
BEGIN
DECLARE tuts BIGINT DEFAULT UNIX_TIMESTAMP() * 1000;
DECLARE today DATE DEFAULT func_ctz(tuts);
DECLARE yuts BIGINT DEFAULT tuts - 86400000 * 1;
DECLARE yesterday DATE DEFAULT func_ctz(yuts);

DECLARE d2 INT DEFAULT 1;
DECLARE d3 INT DEFAULT 2;
DECLARE d7 INT DEFAULT 6;
DECLARE d30 INT DEFAULT 29;

SET @dnu = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = 0);
SET @dau = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(last_login_time)) = 0);
SET @dpr = (SELECT SUM(income) FROM tb_billing_order WHERE STATUS = 3 AND DATEDIFF(yesterday, func_ctz(complete_time)) = 0);
SET @dpu = (SELECT COUNT(DISTINCT player_id) FROM tb_billing_order WHERE STATUS = 3 AND DATEDIFF(yesterday, func_ctz(complete_time)) = 0);
SET @r2 = func_stat_remain(yesterday, d2);
SET @r3 = func_stat_remain(yesterday, d3);
SET @r7 = func_stat_remain(yesterday, d7);
SET @r30 = func_stat_remain(yesterday, d30);

#SELECT @dnu, @dau, @dpr, @dpu, @r2, @r3, @r7, @r30;

INSERT INTO tb_stat_remain(dnu, dau, dpr, dpu, stat_time, op_time) VALUES(@dnu, @dau, @dpr, @dpu, yuts, tuts);

UPDATE tb_stat_remain SET r2 = @r2 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d2;
UPDATE tb_stat_remain SET r3 = @r3 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d3;
UPDATE tb_stat_remain SET r7 = @r7 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d7;
UPDATE tb_stat_remain SET r30 = @r30 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d30;
END

计算并插入昨天的付费相关的数据,同时计算并更新与昨天相关的存留相关的数据。其中调用了两个函数:

1
2
3
4
5
6
7
8
CREATE FUNCTION `func_stat_remain`(`yesterday` date, `days`  int) RETURNS double
BEGIN
SET @number = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = days AND DATEDIFF(yesterday, func_ctz(last_login_time)) = 0);
SET @total = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = days);
SET @percent = @number / @total * 100;

RETURN @percent;
END

func_stat_remain 计算与指定日期相隔 days 天的留存率。

1
2
3
4
5
6
7
8
9
10
11
-- ----------------------------
-- Function structure for func_ctz
-- ----------------------------
DROP FUNCTION IF EXISTS `func_ctz`;
DELIMITER ;;
CREATE DEFINER=`catstudiolc2`@`%` FUNCTION `func_ctz`(`milliseconds` bigint) RETURNS datetime
BEGIN
SET @dt = CONVERT_TZ(FROM_UNIXTIME(milliseconds /1000), "+0:00", "+8:00");

RETURN @dt;
END

func_ctz 转换毫秒时间为当前时区日期,时区问题在上篇文章阐述过。

最后创建一个事件:

1
CREATE EVENT `event_stat_remain` ON SCHEDULE EVERY 1 DAY STARTS '2015-10-01 16:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL proc_stat_remain()

每天零点执行。因为数据库所在时区是UTC(零时区),所以使用 16:00:00,正好是GMT+8(东八区)的零点。也可以在服务器程序里在每天零点调用上面的主存储过程,就不需要这个事件了。

每天产生存留和付费的基础数据,保存在 tb_stat_remain 表中,具体关心的每项数据从中获取计算即可。