LeetCode-SQL(一）

---

leetcode系列：

• LeetCode-算法（一）
• LeetCode-SQL(一）
• LeetCode-SQL(二）
• LeetCode-SQL(三）

---

511. 游戏玩法分析 I 🔒

https://leetcode-cn.com/problems/game-play-analysis-i/

SQL架构：

活动表 Activity：

表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。

写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

查询结果的格式如下所示：

题解：根据player_id 进行分组，获取每组中最小的日期即可

select
	player_id，
	min(event_date ) as first_login 
from
	Activity 
	group by player_id；

512. 游戏玩法分析 II 🔒

https://leetcode-cn.com/problems/game-play-analysis-ii/

• 表结构同—游戏玩法分析 I

请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称
查询结果格式在以下示例中：

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;

534. 游戏玩法分析 III 🔒

https://leetcode-cn.com/problems/game-play-analysis-iii/

• 表结构同—游戏玩法分析 I

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

查询结果格式如下所示：

题解：滑动窗口函数累加 sum() over()

select
    player_id,
    event_date,
    sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity

550. 游戏玩法分析 IV 🔒

https://leetcode-cn.com/problems/game-play-analysis-iv/

• 表结构同—游戏玩法分析 I

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果格式如下所示：

题解：

SELECT
	ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(distinct player_id) FROM Activity), 
	2) AS fraction
FROM
    Activity
WHERE
	(player_id,event_date)
	IN
	(SELECT 
        player_id,
        Date(min(event_date)+1)
	FROM Activity
	GROUP BY player_id);

184. 部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

方法一：使用 JOIN 和 IN 语句

因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

注意：有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息。

DepartmentId	MAX(Salary)
1	90000
2	80000

然后，我们可以把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

方法二：窗口函数的运用

select Department, Employee, Salary
from 
(
select 
    D.Name as Department, 
    E.Name as Employee, 
    E.Salary as Salary, 
    rank() over(partition by D.Name order by E.Salary desc) as rank_
from Employee E join Department D on E.DepartmentId = D.Id
) as tmp
where rank_ = 1；

连续更新leetcode系列SQL & 算法包括需要解锁🔒的部分 记得关注我哦~