后台周期汇总统计

源于大量在线/付费统计的情况, 很多后台开发的时候会赶时间直接连表查询出来, 前期如果数据少且条件少的情况可能没什么问题, 但是后续会出现大量问题.

以下下面简单架构玩家和订单表为例:

CREATE TABLE IF NOT EXISTS `user_info`
(
    `id`
    BIGINT
    UNSIGNED
    NOT
    NULL
    AUTO_INCREMENT,
    `username`
    VARCHAR
(
    64
) NOT NULL,
    `create_time` INT NOT NULL,
    PRIMARY KEY
(
    `id`
)
    ) COMMENT='玩家信息表' COLLATE ='utf8mb4_unicode_ci';


CREATE TABLE IF NOT EXISTS `order_info`
(
    `order_id`
    BIGINT
    UNSIGNED
    NOT
    NULL
    AUTO_INCREMENT
    COMMENT
    '订单ID',
    `username`
    VARCHAR
(
    64
) NOT NULL COMMENT '这里姑且采用账号名做唯一标识,实际这里采用uid做bigint索引',
    `create_time` INT UNSIGNED NOT NULL COMMENT '支付时间',
    `amount` BIGINT UNSIGNED NOT NULL COMMENT '充值金额',
    PRIMARY KEY
(
    `order_id`
)
    ) COMMENT='订单信息' COLLATE ='utf8mb4_unicode_ci'

这里说下几个常见数据统计概念:

付费转化率: 指定某个时间段(比如2023年11月11日) 的注册|登录玩家成功购买游戏内道具那部分玩家( 目标: 统计大R玩家/买量玩家付费率 )
在线活跃度: 指定某个时间段, 在这个时间段注册|登录的玩家往后 第1/3/5/8/15/30/90天后 登录在线数( 目标: 统计玩家留存/计算玩家流失 )
持续付费跟踪: 指定某个时间段, 在这个时间充值的玩家付费人数, 后续指定 第1/3/5/8/15/30/90天后 是否还存在付费( 目标: 统计玩家付费热衷/计算玩家消费梯度 )

需要注意: 根据策划/运营需求来变动概念, 以上概念不是固定不变的, 比如 付费转化率 有时候策划需要统计 并非注册玩家而是登录过的玩家 的复杂回流统计情况, 这种时候就需要另外处理.

很多这种查询直接寻求 DB 方案的时候网上能够看到很多方案, 类似于:

#
网上能够可见的日活计算
# user_id: 玩家唯一标识
# create_ts: 玩家在线登录时间
SELECT COUNT(DISTINCT user_id) AS user_cnt, DATE (create_ts) AS view_day
FROM
    user_trace
GROUP BY DATE (create_ts);



#
如果 2020.11.10 ~ 2020.11.16 这段时间的日活
# 利用 WITH recursive 递归计算
WITH recursive full_day (bizdate) AS 
(SELECT 
  '2020-11-10' AS bizdate 
UNION
ALL 
SELECT 
  DATE_ADD(bizdate, INTERVAL 1 DAY) 
FROM
  full_day 
WHERE bizdate < '2020-11-16')
SELECT bizdate,
       (SELECT COUNT(DISTINCT user_id)
        FROM user_trace b
        WHERE b.create_ts >= DATE_SUB(
                bizdate, INTERVAL 6 DAY)
          AND b.create_ts < DATE_ADD(
                bizdate, INTERVAL 1 DAY)) AS user_cnt
FROM full_day a # 最后生成结构:
# bizdate     user_cnt  
# ----------  ----------
# 2020-11-10           2
# 2020-11-11           3
# 2020-11-12           3
# 2020-11-13           3
# 2020-11-14           3
# 2020-11-15           3
# 2020-11-16           3

可以查看这些查询数据, 性能方面基本上不奢求太多基本上查询点击触发会导致临时表创建并且撑爆 CPU 和内存占用.

注: 虽然功能实现了, 但是随着正式项目玩家数据增长疯涨的时候, 每次执行生成临时表和筛选过程将使得数据库卡顿非常严重.

处理方案

后续衍生出来定时脚本入库到另外的表, 通过 crontab 定时脚本提取出数据重新入库成到新表上, 类似于以下表:

tbl_log_login_day: 表名, 当天登录玩家
date: 20231112, 类似年月日做标识记录
total: 20, 当天登录的玩家数量

通过后台定时不断入库, 然后就能得知每日登录的人数情况; 但是这是有问题的, 因为根本无法解决 用户粘性 问题.

如果按照这个需求处理: 选定玩家注册某个年月日(买量玩家)之后, 按照这一天开始后每1/3/6/8/15/30/90天在线登录人数, 从而统计玩家平台买量效果; 这个需求怎么做? 需要明确知道玩家样本, 然后区间去取对应天数玩家样本的统计.

这种情况绞尽脑汁只依赖 MYSQL 之类的数据库就有点太过为难了, 毕竟数据库大部分工作还是数据落地保存而非统计运算, 哪怕是基于定时脚本落地也没办法这样来做处理.

注意: 这种需求甚至要求精细到个人方面, 方便后台导出玩家然后玩家数量对账

其实主要这个处理方式主要是 求差集/交集 的过程, 比如查出 20231112 等登录玩家id作为玩家样本之后求 20231113 的登录玩家并且 合并求交集 就能计算出 20231112 的玩家在 20231113 的活跃人数.

这里就需要 Redis 来做处理了, 在 redis 之中有种 set/zset(无序集合/有序集合, 这里主要取 set ) 结构, 这里需要说明下相关命令:

# SADD 将一个或多个值 value 加入到集合中; 如果元素已经存在，则不会添加, 返回值返回添加的数量值
SADD key value...
# 比如添加 20231112 登录玩家集合 -> SADD login:20231112 11123 11124 11124


# SISMEMBER 识别出当中元素是否在集合当中; 返回值为存在时候返回1否则返回0
SISMEMBER key value...
# 比如判断玩家 11125 是否在 20231112 登陆过 -> SISMEMBER login:20231112 11125


# SCARD 获取当前集合的总数, 返回的数量
SCARD key
# 比如获取 20231112 当天玩家登录的数量 -> SCARD login:20231112


# SMEMBERS 获取集合当中所有的元素
SMEMBERS key
# 比如直接想查询出 20231112 登录过的所有玩家 id 导出报表 -> SMEMBERS login:20231112


# SREM 删除指定集合当中的值, 删除一般少所以不做具体示例
SREM key value


# SINTER 关键函数, 请求两个集合当中的交集, 十分核心的求交集功能, 返回值为交集集合元素
SINTER key...
# 比如求 20231112 当天登录的玩家在 20231113 至今还活跃的玩家 -> SINTER login:20231112 login:20231113
# 当然可以多重迭代获取出在 20231112, 20231113, 20231114 三天共同活跃玩家 -> SINTER login:20231112 login:20231113 login:20231114


# SINTERSTORE 关键函数, 和 SINTER 类似但将结果重新保存到 new_key 临时表之中, 老版本 redis 不支持 SINTER 指令需要先保存临时表
SINTERSTORE new_key key...
# 比如求 20231112 当天登录的玩家在 20231113 至今还活跃的玩家并保存到新的结果 -> SINTERSTORE login:20231112_20231113 login:20231112 login:20231113


# SDIFF 关键函数, 请求首位的集合和其他集合的差集差异, 如果 key 不存在视为空集, 注意这里差集对比对象只是与首位 key 做对比
SDIFF key...
# 比如求出 20231112 登录玩家在 20231113 之后流失对象 -> SDIFF login:20231112 login:20231113


# SDIFFSTORE 关键函数, 和 SDIFF 类似但是将给定查询的集合的差集并存储在 new_key 集合中, 老版本 redis 不支持 SDIFF 需要先用 SDIFFSTORE 临时表做处理
SDIFFSTORE new_key key...
# 比如求出 20231112 登录玩家在 20231113 之后流失对象同时保存到另外集合结果 -> SDIFFSTORE login:20231112_20231113 login:20231112 login:20231113


# SUNION 关键函数, 求出两个集合当中的全部元素并集, 不存在的集合 key 被视为空集
SUNION key...
# 比如求出 20231112-20231115 这几天所有登录玩家 -> SUNION login:20231112 login:20231113 login:20231114 login:20231115


# SUNIONSTORE 关键函数, 和 SUNION 类似也是因为老版本 redis 不支持 SUNION 的临时表版本
SUNIONSTORE new_key key...
# 比如求出 20231112-20231115 这几天所有登录玩家保存到新的集合 -> SUNIONSTORE login:20231112_20231115 login:20231112 login:20231113 login:20231114 login:20231115

上面就是日常 redis 做数据统计可能用到的汇总方法, 通过后台脚本不断运转然后统计每天玩家相关数据写入到 redis 中; 得益于内存型 nosql 从而最快速处理大数据汇总统计, 而且汇总基本都是采用玩家唯一id占用也不多(数据留存也仅仅90天而已, 过期会清空数据).

在我正式工作时间段, 基本上购买 4G内存云服务商Redis服务 就能应对 1~200w 的后台数据查询统计( 某个日期玩家在1~90天内的流失情况 )在 2~3s 内直接统计查询, 而且看起来还没达到瓶颈上线.

回到之前的问题, 查出 20231112 等登录玩家id作为玩家样本之后求 20231113 的登录玩家并且 合并求交集 就能计算出 20231112 的玩家在 20231113 的活跃人数, 现在看起来这个需求太过简单了, 可以扩展下需求: 查出 20231112 等登录玩家id作为玩家样本, 之后 统计出第2/3/8/16/30/90天 的留存情况:

# 这里直接在编程语言中划分统计挡位即可, 之后遍历查询出相关的交集数据
# 1. 首先查询出 20231112 玩家数量
SCARD login:20231112

# 2. 请求第2天的交集登录玩家
SINTER login:20231112 login:20231113

# 3. 请求第3天的交集登录玩家
SINTER login:20231112 login:20231113 login:20231114

# 4. 请求这周(第8天)交集登录玩家
SINTER login:20231112 login:20231113 login:20231114 login:20231115 login:20231116 login:20231117 login:20231118 login:20231119

# 5. 请求半个月(第16天)交集登录玩家
# 6. 请求一个月(第30天)交集登录玩家
# 7. 请求季度(第90天)交集登录玩家

以此类推直接汇总出所需要的登录留存统计, 并且得益于 Redis 的纯内存特性查询和导出极快, 相同的需求可以考虑如果传统数据库需要怎么做.

上面仅仅演示了怎么求玩家交集, 还有求玩家流失情况(差集得出某个周期玩家流失状况)等高阶统计, 实际上更多类似数学概念中的求交和求差, 所以数学思维做统计时候确实能够起到很多辅助作用.

这些统计数据基本上超过90天就很少去再次查询( 最长一年需要做年度汇总 ), 后续这些数据可以考虑后台脚本清除保证 redis 容量不会爆满.

如果后续游戏成为爆款没办法通过 redis 来做统计的情况, 可以考虑转变方案在 Elasticsearch 寻求方案来做高阶统计学, 因为这已经涉及到更加复杂的统计学概念了.

MeteorCat / 后台周期汇总统计

后台周期汇总统计

处理方案