游戏行业实战案例 4 ：在线时长分析

【面试题】某游戏数据后台设有「登录日志」和「登出日志」两张表。

「登录日志」记录各玩家的登录时间和登录时的角色等级。 

「登出日志」记录各玩家的登出时间和登出时的角色等级。

其中，「角色id」字段唯一识别玩家。

游戏开服前两天（ 2022-08-13 至 2022-08-14 ）的角色登录和登出日志如下

一天中，玩家可以多次登录登出游戏，请使用 SQL 分析出以下业务问题：

请根据玩家登录登出的时间，统计各玩家每天总在线时长情况。

（如玩家登录后没有对应的登出日志，可以使用当天 23:59:59 作为登出时间，时间之间的计算可以考虑使用时间戳函数 unix_timestamp 。）

问题 4 ：

统计各玩家每天总在线时长分为两步：

第一步，计算各玩家每天每次登录游戏后的在线时长；

第二步，对各玩家每天每次的在线时长进行求和，得到各玩家每天的总在线时长。

1. 计算各玩家每天每次登录游戏后的在线时长

玩家每次登录后的在线时长=每次的登出时间-每次对应的登录时间，因此，我们需要对玩家的登录时间、登出时间进行一一对应。

登录时间从「登录日志」表获取，登出时间从「登出日志」表获取。那么，如何对玩家的登录时间、登出时间进行一一对应呢？

玩家每次登录后必然伴随着登出，因此玩家的登录时间顺序与登出时间顺序是一致的。对每个玩家的登录时间进行排序得到排名，再对每个玩家的登出时间进行排序得到排名，那么登录时间对应的排名必然与登出时间对应的排名一致。即：排名为1的登录时间与排名为 1 的登出时间相对应，排名为 2 的登录时间与排名为 2 的登出时间相对应……

使用排序窗口函数对每个玩家的登录登出时间进行排序（三个排序窗口函数选择其一即可，在此选择 rank() 窗口函数），由于要获取每个玩家每天的登录登出时间排名，因此以角色 id ，日期进行分组，以登录或登出时间升序排序，即 partition by 角色 id ,日期 order by 登录时间/登出时间 asc 对登录登出时间进行排序的 SQL 的书写方法：

#对每个玩家每天的登录时间进行排序
select 角色id,日期,登录时间,rank() over(partition by 角色id,日期 order by 登录时间 asc) as 登录排名
from 登录日志;
#对每个玩家每天的登出时间进行排序
select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名
from 登出日志;

查询结果如下：

对每个玩家每天的登录登出时间进行排序后，就可以将登录登出时间进行一一对应了。

如何一一对应呢？通过横向联结就可以实现，即使用 join 联结方法。

根据题意，「登录日志」表中的登录时间不存在缺失，而「登出日志」表中某个玩家的登出时间可能存在缺失，为了在联结的时候完整的保留登录登出时间，将上述查询结果1设为临时表a，查询结果 2 设为临时表 b ，并让临时表 a 左联结（ left join ）临时表 b 。

左联结时，还需要设置条件使两个临时表的角色 id 、日期和排名相等，这样才能使登录登出时间一一对应。

进行左联结的 SQL 的书写方法：

select a.角色id,a.日期,a.登录时间,b.登出时间
from
(select 角色id,日期,登录时间,rank() over(partition by 角色id,日期 order by 登录时间 asc) as 登录排名
from 登录日志) as a
left join
(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登录排名 = b.登出排名;

查询结果如下：

需要注意的是，根据题意：如玩家登录后没有对应的登出日志，可以使用当天 23:59:59 作为登出时间。也就是说，若玩家登录后没有对应的登出日志，则进行左联结后「登出时间」这一列会存在空值，而空值可以使用当 23:59:59 进行填充。

如何实现这一操作呢？

可以使用 case when 子句进行条件判断，当「登出时间」这一列的某个值为空值时，则使用当天 23:59:59 作为值，否则就不改变值，即：

case when 登出时间 is null then 当天23:59:59 else 登出时间 end

除了使用 case when 填充空值，还可以使用 ifnull() 函数填充空值。ifnull() 函数的语法为：

ifnull(值1,值2)

其中，若值 1 为 null ，则返回值 2 ，若值 1 不为 null ，则返回值 1 。

比如：

ifnull(null,1) ，返回值为 1 ；ifnull(0,1) ，返回值为 0 。

将其应用于本问题，则是：

ifnull(登出时间,'当天23:59:59')

即：若登出时间为 null ，则返回当天 23:59:59 ，若登出时间不为 null ，则返回登出时间。

case when 子句和 ifnull() 函数能达到同样的效果，两者选择其一即可。在此选择 case when 子句进行条件判断。

那么，如何得到当天 23:59:59 呢？

当天即为「日期」列中的值，因此我们可以将「日期」列中的值与 23:59:59 进行合并得到当天 23:59:59 。合并字符串使用 concat() 函数，合并时日期与 23:59:59 之间存在一个空格，使时间格式一致，即：

concat(日期,' 23:59:59')

这样，在左联结时，同时填充「登出时间」字段空值的 SQL 的书写方法为：

select a.角色id,a.日期,a.登录时间,(case when b.登出时间 is null then concat(a.日期,'23:59:59') else b.登出时间 end) as 登出时间              #使用ifnull()函数，则为ifnull(b.登出时间,concat(a.日期,' 23:59:59')) as 登出时间
from
(select 角色id,日期,登录时间,rank() over(partition by 角色id,日期 order by 登录时间 asc) as 登录排名
from 登录日志) as a
left join
(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登录排名 = b.登出排名;

查询结果如下：

可以看到，登录时间和登出时间已经一一对应，将登出时间减去登录时间就可以得到玩家每次登录后的在线时长。

将上述查询结果设为临时表 c ，则计算每个玩家每天每次登录后的在线时长的 SQL 的书写方法为：

select 角色id,日期,
unix_timestamp(登出时间) - unix_timestamp(登录时间) as 每次在线时长
from c;

unix_timestamp() 函数可以将日期时间格式转化成 10 位数的时间戳格式，单位为秒，因此，为了得到单位为分钟的在线时长，我们需要在登出登录时间相减后再除以 60 秒，即：

select 角色id,日期,(unix_timestamp(登出时间) - unix_timestamp(登录时间))/60 as 每次在线时长_min
from c;

利用 with…as 语句来封装临时表 c 的查询语句，则 SQL 的书写方法：

with c as
(select a.角色id,a.日期,a.登录时间,(case when b.登出时间 is null then concat(a.日期,'23:59:59') else b.登出时间 end) as 登出时间
from
(select 角色id,日期,登录时间,rank() over(partition by 角色id,日期 order by 登录时间 asc) as 登录排名
from 登录日志) as a
left join
(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登录排名 = b.登出排名
)
select 角色id,日期,
round((unix_timestamp(登出时间)- unix_timestamp(登录时间))/60,2) as 每次在线时长_min #使用round()函数保留2位小数
from c;

查询结果如下：

2. 计算各玩家每天的总在线时长

使用 group by 子句对角色 id 、日期进行分组，再使用 sum() 函数对每个玩家每天的每次在线时长进行求和，就可以得到各玩家每天的总在线时长。

 SQL 的书写方法：

with c as
(select a.角色id,a.日期,a.登录时间,(case when b.登出时间 is null then concat(a.日期,'23:59:59') else b.登出时间 end) as 登出时间
from
(select 角色id,日期,登录时间,rank() over(partition by 角色id,日期 order by 登录时间 asc) as 登录排名
from 登录日志) as a
left join
(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登录排名 = b.登出排名
)
select 角色id,日期,
sum(round((unix_timestamp(登出时间)- unix_timestamp(登录时间))/60,2)) as 总在线时长_min #使用round()函数保留2位小数
from c
group by 角色id,日期;

查询结果如下：

 ⬇️点击「阅读原文」

 免费报名 数据分析训练营