SQL游戏行业实战案例4：在线时长分析（时间函数，排序函数，concat、表连接）

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

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

其中，“角色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,日期;