【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。
“登录日志”记录各玩家的登录时间和登录时的角色等级。
“登出日志”记录各玩家的登出时间和登出时的角色等级。
其中,“角色id”字段唯一识别玩家。
游戏开服前两天(2022-08-13至2022-08-14)的角色登录和登出日志如下
一天中,玩家可以多次登录登出游戏,请使用SQL分析出以下业务问题:
玩家在开服首日(2022-08-13)的等级停滞率。
(等级停滞率=停留于该等级角色数/达到过该等级总人数;如玩家没有登出日志,则使用登录日志的等级信息。)
【解题思路】
等级停滞率=停留于该等级角色数/达到过该等级总人数。
各停留等级的角色数在问题2中我们已经计算得出,现在我们来计算达到各等级的总人数。
因为题中表明:如玩家没有登出日志,则使用登录日志的等级信息,因此,我们依然对“登出日志”和“登录日志”纵向联结。
联结后的表记录着玩家全部的角色等级信息,则达到过停留等级的信息必然在该表中,因此我们先计算这个表中达到各个等级的总人数,再筛选出达到停留等级的总人数。
计算达到各个等级的总人数也分为两步:
第一步,对各等级进行分组;
第二步,分组后,计算各等级的角色数。
可以看到,这依然是一个分组汇总问题,使用group by子句组合count()函数进行计算。
需要注意的是:若一个玩家登录登出信息在“登录日志”和“登出日志”都有完整记录。
那么“登出日志”中一次登出时间下的等级,会与“登录日志”中紧随该登出时间后的登录时间下的等级一致,因为登出时的等级会是下一次登录的等级。
这样,联结后的表中同一个玩家不同时间下会存在重复的等级,利用count()函数计算该等级下的人数时就会多算一次该玩家。
这种情况下我们需要对角色id进行去重(使用distinct关键字去重),以免重复计算人数:
count(distinct 角色id)
完整SQL的书写方法:
select 角色等级,count(distinct 角色id) as 总角色数
from
(select 日期,角色id,登录时间 as 时间,角色等级
from 登录日志
where 日期 = '2022-08-13'
union all
select 日期,角色id,登出时间 as 时间,角色等级
from 登出日志
where 日期 = '2022-08-13') as a
group by 角色等级;
查询结果如下:
可以看到,开服首日(2022-08-13)达到各停留等级的总人数均在上述结果中。
现在,我们来筛选达到各停留等级下的总人数。
停留等级来自于问题2计算出的结果中,即下表:
我们需要根据这个表中的停留等级进行筛选,如何筛选呢?
可以使用left join联结进行筛选。
left join用于横向联结两个表,联结时以左表为主表,返回左表的所有行,即使右表中没有匹配。
在这里,我们将上述停留等级分布的表(设为临时表c)设为左表,包含所有角色等级下的总角色数的表(设为临时表d)设为右表。
左、右表以等级进行匹配,那么使用left join纵向联结左、右表后,只会保留左表中存在的角色等级,而剔除掉左表中不存在的角色等级。
这样,我们就能将表d中的属于停留等级的总角色数筛选出来。
实现上述联结的SQL的书写方法:
select c.*,d.*
from c
left join d
on c.停留等级 = d.角色等级;
可以看到,使用left join联结后的表已经同时存在停留于该等级的角色数、达到过该等级的总人数(总角色数),可以直接算出等级停滞率(角色数/总角色数即为等级停滞率)。
那么,我们在用left join联结后,直接取表c中的停留等级,并取表c中的角色数,表d中的总角色数相除即可,无需将表c和表d中所有列都取出来。
SQL的书写方法:
select c.停留等级,c.角色数/d.总角色数 as 等级停滞率
from c
left join d
on c.停留等级 = d.角色等级;
现在我们来带入临时表c、临时表d的内容。为了使SQL语句更加易读,我们使用with…as语句来定义临时表c、临时表d(with…as语句可以将一组完整SQL语句的子查询封装起来,使语句更加清晰易懂)。
临时表c为问题2的查询结果,临时表d为计算达到各等级的总人数的查询结果,则有:
with
c as(
select 角色等级 as 停留等级,count(角色id) as 角色数
from
(select *,rank() over(partition by 角色id order by 时间 desc ) as 排名
from
(select 日期,角色id,登录时间 as 时间,角色等级
from 登录日志
where 日期 = '2022-08-13'
union all
select 日期,角色id,登出时间 as 时间,角色等级
from 登出日志
where 日期 = '2022-08-13') as a
) as b
where 排名 = 1
group by 角色等级
order by 角色等级 asc), #临时表c的具体内容
d as(
select 角色等级,count(distinct 角色id) as 总角色数
from
(select 日期,角色id,登录时间 as 时间,角色等级
from 登录日志
where 日期 = '2022-08-13'
union all
select 日期,角色id,登出时间 as 时间,角色等级
from 登出日志
where 日期 = '2022-08-13') as a
group by 角色等级) #临时表d的具体内容
将其带入left join联结的语句中,则计算各停留等级的停滞率的完整SQL的书写方法为:
with
c as(
select 角色等级 as 停留等级,count(角色id) as 角色数
from
(select *,rank() over(partition by 角色id order by 时间 desc ) as 排名
from
(select 日期,角色id,登录时间 as 时间,角色等级
from 登录日志
where 日期 = '2022-08-13'
union all
select 日期,角色id,登出时间 as 时间,角色等级
from 登出日志
where 日期 = '2022-08-13') as a
) as b
where 排名 = 1
group by 角色等级
order by 角色等级 asc), #临时表c的具体内容
d as(
select 角色等级,count(distinct 角色id) as 总角色数
from
(select 日期,角色id,登录时间 as 时间,角色等级
from 登录日志
where 日期 = '2022-08-13'
union all
select 日期,角色id,登出时间 as 时间,角色等级
from 登出日志
where 日期 = '2022-08-13') as a
group by 角色等级) #临时表d的具体内容
select c.停留等级,c.角色数/d.总角色数 as 等级停滞率
from c
left join d
on c.停留等级 = d.角色等级;
查询结果如下: