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

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

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

其中，「角色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 aleft join(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名from 登出日志) as bon 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 aleft join(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名from 登出日志) as bon 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 每次在线时长_minfrom 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 aleft join(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名from 登出日志) as bon 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 aleft join(select 角色id,日期,登出时间,rank() over(partition by 角色id,日期 order by 登出时间 asc) as 登出排名from 登出日志) as bon 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 cgroup by 角色id,日期;

查询结果如下：

⬇️点击「阅读原文」

免费报名数据分析训练营

ITPub博客

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

最新文章