图解面试题：如何分析游戏？

【题目】

猴子是一个班级的班主任，由于所带班级的学生成绩普遍不是很好。现在他需要找出每门课程中成绩最差的学生，然后有针对性的辅导。

下面的成绩表，记录来每个学生选修课程的成绩。

请注意：每门课程倒数第一的学生可能有多名，他们的成绩相同。请用你的聪明智慧帮助猴子老师尽快的找出这些学生，帮助他们提升成绩吧。

【解题思路】

方法 1：找出每门课程最差的成绩，然后再按条件去查找出对应的学号

1. 找出每门课程最差的成绩

还记得我们在《从零学会 sql》里讲过的吗？当有 “每个” 出现的时候，就要想到是要分组（group by）了，这里是按课程号分组。

最差的成绩，也就是成绩最小，对应的汇总函数是 min(成绩)。对应的查询语句是：

select 课程号,min(成绩) as 成绩 
from 成绩表 
group by 课程号;

2. 找出每门课程成绩最差的学生的学号

在第一步查询到每门课程成绩最差的课程号和成绩，接下来就是要找出这个课程和成绩对应的学生信息。

这种同时使用‘第一步的查询结果’（表 a）和‘原始成绩表’（表 b）两个表的查询，就要用到多表查询了 。

两个表的联结条件是课程号相同，并且成绩也相同，所以是：

on a.课程号=b.课程号 and a.成绩=b.成绩

多表查询的 sql 语句如下，就找到了每门课程里成绩最差的学生：

select b.学号,a.课程号,a.成绩 
from
(select 课程号,min(成绩) as 成绩 from 成绩表 group by 课程号) as a
inner join
(select * from 成绩表) as b
on a.课程号=b.课程号 and a.成绩=b.成绩;

方法 2：先求出每门课程最差的成绩作为辅助列，接下来只要筛选出等于最差的成绩的数据就可以了。

1. 求出每门课程最差的成绩作为辅助列

使用窗口函数，将每一科目成绩的平均值求出。

select *, min(成绩) over (partition by 课程号) as 最差的成绩
from 成绩表;

2. 筛选出等于最差的成绩的数据即可

在上一步的查询结果里加入条件：成绩 = 最差的成绩，就可以把每门课程里最差成绩的数据筛选出来了。

select * from
(select *, min(成绩) over (partition by 课程号) as 最差的成绩
from 成绩表) t
where 成绩=最差的成绩;

【本题考点】

1. 当有 “每个” 出现的时候，要想到用 “分组汇总” 来完成这个业务需求

2. 涉及到多个表时，使用多表查询

3. 如何将业务需求使用 sql 来实现的能力

【举一反三】

下面的游戏玩家表（activity 表）记录了游戏玩家在游戏平台上的行为活动。

每行数据记录了该玩家（player）在某天（event_datert 日期)，使用同一台设备（device_id 设备编号，比如苹果手机、pad 是不一样的设备）登录平台后打开的游戏的数目（games_played 游戏数码）。表的主键是 (player, event_date)。

【问题 1】找出每位玩家第一次登陆平台的日期

找出每个玩家第一次登陆游戏平台的日期，也就是查找登陆游戏平台的日期最小的玩家 id。

和我们前面查询每门课程的成绩最小学生其实业务需求本质是一样的，可以用分组汇总来实现：

select player_id as player , min(event_date) as first_login  
from  activity 
group by player_id;

【问题 2】找出每位玩家首次登陆的设备名称

（1）先和上一题一样，分组查找每组日期最小的玩家，建立临时表

select player_id as player , min(event_date) as first_login  
from  activity 
group by player_id;

（2）将第一步的临时表和原表进行联结，联结条件是玩家 ID 和日期

select a.player_id as player_id, a.device_id 
from  activity as a
left join  
(select player_id, min(event_date) as first_login  
from  activity 
group by player_id） as b
on a.player_id = b.player_id  and a.event_date = b.first_login;

推荐：如何从零学会 sql？