【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。
“登录日志”记录各玩家的登录时间和登录时的角色等级。
“登出日志”记录各玩家的登出时间和登出时的角色等级。
其中，“角色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,日期;
查询结果如下：
问题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,日期;
查询结果如下：
案例数据下载途径：
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
