考点一 聚合函数 + group by

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

考点二 MySQL 8.0 的新特性——窗口函数

MySQL 8.0新特性提供了窗口函数，对于排序问题，常用的有三种窗口函数，以数值4,5,5,6为例:

ROW_NUMBER(): 求行数，结果为1,2,3,4
RANK(): 有间隔的分级，结果为1,2,2,4
DENSE_RANK(): 无间隔的分级，结果为1,2,2,3

使用这些窗口函数时，要用over设定窗口，用法为:

function OVER windowName
WINDOW windowName AS (PARTITION BY a_col ORDER BY b_col)

这两行代码含义为设定一个对a_col分组后对每组的b_col排序的窗口，对这个窗口应用function。

在本题中，要找到每位玩家第一次登录的设备id，则需要对玩家id进行分组，每组求ROW_NUMBER，使用ROW_NUMBER而不使用其他窗口函数的原因是ROW_NUMBER结果是不重复的，适合本题的场景。

select player_id, device_id
from (
    select player_id, device_id, ROW_NUMBER() OVER(PARTITION BY player_id ORDER BY event_date) as rn
    from activity) as a
where rn = 1;
496 ms

考点三 子查询

select activity.player_id, activity.device_id from activity, (
	select player_id, min(event_date) as first_login
	from activity group by player_id
) as temp
where activity.player_id = temp.player_id
and activity.event_date = temp.first_login;
589 ms

使用 all 配合 <= 筛选出 a1.player_id = a2.player_id 的 event_date 数据

select player_id, device_id from activity a1 where event_date <= all(
	select a2.event_date from activity a2 where a1.player_id = a2.player_id
);
1539 ms

考点四 左连接

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
where bonus is null or bonus < 1000;

考点五 LIMIT

select customer_number
from orders
group by customer_number
order by count(*) desc
LIMIT 1

考点六 round 和 isfull

将两个数字相除并取整 round 到 2 位小数，以得到想要的通过率。
总请求数，也就是分母，有可能为 0。所以我们需要使用 ifnull函数来处理这种特殊情况。

select
round(
    ifnull(
    (select count(*) from (select distinct requester_id, accepter_id from RequestAccepted) as A)
    /
    (select count(*) from (select distinct sender_id, send_to_id from FriendRequest) as B),
    0)
, 2) as accept_rate;