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

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 dwhere 日期 = '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 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 总在线时长_minfrom cgroup 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 dwhere 日期 = '2022-08-13';

现在我们来计算各在线时间段的玩家人数，同样，使用group by子句和count()函数即可实现。

将上述查询结果设为临时表e，则SQL的书写方法：

select 在线时间段,count(角色id) as 玩家人数from egroup 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 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 总在线时长_minfrom cgroup 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 dwhere 日期 = '2022-08-13') as egroup 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 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 总在线时长_minfrom cgroup 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 dwhere 日期 = '2022-08-13') as egroup 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语句的应用。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。