【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。
「登录日志」记录各玩家的登录时间和登录时的角色等级。
「登出日志」记录各玩家的登出时间和登出时的角色等级。
其中，「角色 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 语句的应用。
⬇️点击「阅读原文」
免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
