SQL游戏行业实战案例5：玩家在线分布（自定义排序，条件求和）

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

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

其中，“角色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语句的应用。