SQL数据分析实战（三）：应用join语句计算玩家在线时长
接上篇，继续介绍Join语句。
在游戏行业，有一个通用的方法可以计算游戏用户的平均在线时长，即：日平均在线人数*24小时/日活跃用户数=日平均在线时长。
该方法有一定的参考意义，因为在线人数的计算单位是精确到秒或者分钟，日平均在线人数是每秒的平均在线人数，乘以24小时相当于一天内按小时计算的累计登陆人数，再除以日活跃用户数，接近于所有用户的平均在线时长。
用该方法计算出来的平均在线时长跟其他游戏横向对比时，有很重要的参考意义。但是，和实际计算出结果存在一定误差，尤其是当我们要对不同类型的用户（比如付费和免费用户）进行计算时，该公式就不能应用。
我们在埋点的时候，会跟开发人员沟通，直接通过程序来计算用户在线时长，但很多时候，开发因为排期等原因，总是把该埋点数据遗漏，并且，通常情况下，登入表和登出表为两张表。因此，我们只能通过玩家登入、登出的时间来计算在线时长。
本次就以某款游戏的登入、登出表数据为例，应用join语句来计算玩家平均在线时长数据。
1、案例表
- 登入表：temp.sample_login，有data_date(登入日期)、character_id（角色ID）、login_time(登入时间)、row_id（同角色按时间排列的序号）
- 登出表：temp.sample_logout，有data_date(登入日期)、character_id（角色ID）、logout_time(登出时间)、row_id（同角色按时间排列的序号）
2、思路
1）先计算每个帐号每天的总在线时长
i. 在登入临时表中增加每次登入的下一次时间
ii. 将登入时间和登出时间整合在一张表
iii. 检查异常值，并剔除异常值
iv. 将登出时间字段-登入时间字段，并汇总统计每天每个帐号的平均在线时长
2）计算所有帐号每天的平均在线时长
3、计算步骤
a) 在登入临时表中增加每次登入的下一次时间
b) 将登入时间和登出时间整合在一张表
c) 检查数据，我们来看某个帐号的登入、登出记录，发现该帐号出现两次相同的登入记录，且相邻登出记录的时间为5分钟，和开发人员确认，是由于进入游戏后5分钟之内没有任何操作，且没有退出游戏的情况下，程序会自动写入一条登出记录。
d) 剔除异常值，将程序自动写入的登出记录剔除（时间差为300秒的记录）
e) 将登出时间字段-登入时间字段，并汇总统计每天每个帐号的平均在线时长
f) 在每天每个帐号的平均在线时长的基础上，计算所有帐号每天的平均在线时长。
假如我们没有剔除异常值，直接将登出时间-登入时间并汇总统计，玩家每天的平均在线时长为4.3小时，而剔除异常值以后的人均在线时长为3.1小时，相差了1.2小时。
使用Join语句对行和列的灵活转换，您掌握了吗？
如果你认为我写的对你有帮助，不妨关注微信公众号 “数据驱动游戏”。
