【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。
「登录日志」记录各玩家的登录时间和登录时的角色等级。
「登出日志」记录各玩家的登出时间和登出时的角色等级。
其中,「角色 id 」字段唯一识别玩家。
游戏开服前两天( 2022-08-13 至 2022-08-14 )的角色登录和登出日志如下
一天中,玩家可以多次登录登出游戏,请使用 SQL 分析出以下业务问题:
请根据玩家登录登出的时间,统计在开服首日各玩家在线时长分布。
(如玩家登录后没有对应的登出日志,可以使用当天 23:59:59 作为登出时间,时间之间的计算可以考虑使用时间戳函数 unix_timestamp 。【区分在线时间段:0-30min ,30min-1h ,1-2h ,2-3h ,3-5h ,5h 以上;区间为左闭右开】)
问题 5 :
统计在开服首日各玩家在线时长分布,其中区分在线时间段:0-30min ,30min-1h ,1-2h ,2-3h ,3-5h ,5h 以上,区间为左闭右开,解释为大白话即为:统计2022-08-13,在线时间段在 0-30min 、30min-1h 、1-2h 、 2-3h 、3-5h 、5h 以上的玩家各有多少人。
统计人数使用 count() 函数,而玩家的在线时间段可以用 case when 子句进行条件判断,即使用 case when 子句判断各玩家的总在线时长在哪个在线时间段内:
case when 总在线时长_min>=0 and 总在线时长_min<30 then '0-30min'
when 总在线时长_min>=30 and 总在线时长_min<60 then '30min-1h'
when 总在线时长_min>=60 and 总在线时长_min<120 then '1-2h'
when 总在线时长_min>=120 and 总在线时长_min<180 then '2-3h'
when 总在线时长_min>=180 and 总在线时长_min<300 then '3-5h'
else '5h以上' end
将问题 4 中统计各玩家每天的总在线时长的查询结果设为临时表 d ,则判断开服首日,各玩家的总在线时长在哪个在线时间段内的 SQL 的书写方法:
select 角色id,
(case when 总在线时长_min>=0 and 总在线时长_min<30 then '0-30min'
when 总在线时长_min>=30 and 总在线时长_min<60 then '30min-1h'
when 总在线时长_min>=60 and 总在线时长_min<120 then '1-2h'
when 总在线时长_min>=120 and 总在线时长_min<180 then '2-3h'
when 总在线时长_min>=180 and 总在线时长_min<300 then '3-5h'
else '5h以上' end) as 在线时间段
from d
where 日期 = '2022-08-13';
利用 with…as 语句来封装临时表 d 的查询语句,则 SQL 的书写方法:
with d as
(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
from c
group by 角色id,日期
)
select 角色id,
(case when 总在线时长_min>=0 and 总在线时长_min<30 then '0-30min'
when 总在线时长_min>=30 and 总在线时长_min<60 then '30min-1h'
when 总在线时长_min>=60 and 总在线时长_min<120 then '1-2h'
when 总在线时长_min>=120 and 总在线时长_min<180 then '2-3h'
when 总在线时长_min>=180 and 总在线时长_min<300 then '3-5h'
else '5h以上' end) as 在线时间段
from d
where 日期 = '2022-08-13';
查询结果如下:
现在我们来计算各在线时间段的玩家人数,同样,使用 group by 子句和 count() 函数即可实现。
将上述查询结果设为临时表 e ,则 SQL 的书写方法:
select 在线时间段,count(角色id) as 玩家人数
from e
group by 在线时间段;
将临时表 e 的查询语句代入,则 SQL 的书写方法:
with d as
(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
from c
group by 角色id,日期
)
select 在线时间段,count(角色id) as 玩家人数
from
(select 角色id,
(case when 总在线时长_min>=0 and 总在线时长_min<30 then '0-30min'
when 总在线时长_min>=30 and 总在线时长_min<60 then '30min-1h'
when 总在线时长_min>=60 and 总在线时长_min<120 then '1-2h'
when 总在线时长_min>=120 and 总在线时长_min<180 then '2-3h'
when 总在线时长_min>=180 and 总在线时长_min<300 then '3-5h'
else '5h以上' end) as 在线时间段
from d
where 日期 = '2022-08-13'
) as e
group by 在线时间段;
查询结果如下:
可以看到,虽然我们已经得到了各在线时间段的玩家人数,但是在线时间段的排列是乱序的,查看分布情况不是很方便。因此,我们需要对在线时间段进行重新排序。
「在线时间段」这一列数据类型为字符串,无法用 order by 子句进行简单排序,那么如何对在线时间段进行重新排序呢?
可以使用 field() 函数。field() 函数是自定义排序函数,可以自定义排列顺序,使用方法为:
order by field(值,str1,str2,str3,str4,……,strn) asc/desc
意思为:
将值按照 str1 , str2 , str3 , str4 ,……, strn 的顺序升序(asc)或者降序排列(desc)。
将其应用在本问题中,则为:
order by field(在线时间段,'0-30min','30min-1h','1-2h','2-3h','3-5h','5h以上') asc
即:将在线时间段这一列的值按照 '0-30min' , '30min-1h' , '1-2h' , '2-3h' , '3-5h' , '5h以上' 的顺序升序排列。
将其代入上述 SQL 语句中,则统计开服首日,玩家的在线时长分布的完整 SQL 的书写方法为:
with d as
(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
from c
group by 角色id,日期
)
select 在线时间段,count(角色id) as 玩家人数
from
(select 角色id,
(case when 总在线时长_min>=0 and 总在线时长_min<30 then '0-30min'
when 总在线时长_min>=30 and 总在线时长_min<60 then '30min-1h'
when 总在线时长_min>=60 and 总在线时长_min<120 then '1-2h'
when 总在线时长_min>=120 and 总在线时长_min<180 then '2-3h'
when 总在线时长_min>=180 and 总在线时长_min<300 then '3-5h'
else '5h以上' end) as 在线时间段
from d
where 日期 = '2022-08-13'
) as e
group by 在线时间段
order by field(在线时间段,'0-30min','30min-1h','1-2h','2-3h','3-5h','5h以上') asc;
查询结果如下:
【本题考点】
1、考察逻辑分析能力,即:如何将复杂问题拆解成容易解决的一个个子问题的能力;
2、考察排序窗口函数的灵活使用。在需要进行分组排序时,排序窗口函数往往是首选;
3、考察 case when 语句的灵活应用以及分组汇总时,group by 子句、聚合函数的搭配使用;
4、考察纵向联结和横向联结的使用。纵向联结使用 union 方法(union、union all),横向联结使用 join 方法(left join、innerjoin、right join);
5、考察多重子查询的应用以及 with…as 语句的应用。
⬇️点击「阅读原文」
文章来源:https://www.toymoban.com/news/detail-645139.html
免费报名 数据分析训练营文章来源地址https://www.toymoban.com/news/detail-645139.html
到了这里,关于游戏行业实战案例 5 :玩家在线分布的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!