【力扣 | SQL题 | 每日四题】力扣534， 574， 2314， 2298

今天的每日四题比较简单，主要其中两题可以用窗口函数轻松解决。

1. 力扣534：游戏玩法分析3

1.1 题目：

表：Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。

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

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+
解释：
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。

1.2 思路：

以player_id 分区，然后以event_date排序，在同一分区内，当窗口指针指向第一条记录的时候，会计算第一条记录的和；当指针指向第二条记录的时候，会计算前两条记录的和...

需要注意的是：窗口函数的over括号内是对记录的逻辑分区和排序哦。并不会实际改变原来的表的记录的顺序。所以题目不要求顺序。

1.3 题解 ：

-- 窗口函数大法：
select player_id, event_date, sum(games_played) over (partition by player_id order by event_date) games_played_so_far
from Activity

2. 力扣574：当选者

2.1 题目：

表: Candidate

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
+-------------+----------+
id 是该表中具有唯一值的列
该表的每一行都包含关于候选对象的id和名称的信息。

表: Vote

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| candidateId | int  |
+-------------+------+
id 是自动递增的主键(具有唯一值的列)。
candidateId是id来自Candidate表的外键(reference 列)。
该表的每一行决定了在选举中获得第i张选票的候选人。

编写解决方案来报告获胜候选人的名字(即获得最多选票的候选人)。

生成的测试用例保证 只有一个候选人赢得 选举。

返回结果格式如下所示。

示例 1:

输入: 
Candidate table:
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
| 4  | D    |
| 5  | E    |
+----+------+
Vote table:
+----+-------------+
| id | candidateId |
+----+-------------+
| 1  | 2           |
| 2  | 4           |
| 3  | 3           |
| 4  | 2           |
| 5  | 5           |
+----+-------------+
输出: 
+------+
| name |
+------+
| B    |
+------+
解释: 
候选人B有2票。候选人C、D、E各有1票。
获胜者是候选人B。

2.2 思路：

看注释，临时表连接。

2.3 题解：

-- 以candidateId分组，找到出现candidateId次数最多的candidateId
with tep as (select candidateIdfrom Votegroup by candidateId having count(*) >= all(select count(*)from Votegroup by candidateId)
)
-- 然后简单的表join找到name
select name
from Candidate c 
join tep t 
on c.id = t.candidateId

3. 力扣2314：每个城市最高温度的第一天

3.1 题目：

表: Weather

+-------------+------+
| Column Name | Type |
+-------------+------+
| city_id     | int  |
| day         | date |
| degree      | int  |
+-------------+------+
(city_id, day) 是该表的主键（具有唯一值的列的组合）。
该表中的每一行都包含某一天某个城市的天气程度。
所有的学位都是在 2022 年获得的。

编写解决方案，找出每个城市中有最高温度记录的日子。如果同一城市多次记录最高气温，则返回其中最早的一天。

返回按 city_id 升序排序 的结果表。

查询结果格式示例如下。

示例 1:

输入: 
Weather 表:
+---------+------------+--------+
| city_id | day        | degree |
+---------+------------+--------+
| 1       | 2022-01-07 | -12    |
| 1       | 2022-03-07 | 5      |
| 1       | 2022-07-07 | 24     |
| 2       | 2022-08-07 | 37     |
| 2       | 2022-08-17 | 37     |
| 3       | 2022-02-07 | -7     |
| 3       | 2022-12-07 | -6     |
+---------+------------+--------+
输出: 
+---------+------------+--------+
| city_id | day        | degree |
+---------+------------+--------+
| 1       | 2022-07-07 | 24     |
| 2       | 2022-08-07 | 37     |
| 3       | 2022-12-07 | -6     |
+---------+------------+--------+
解释: 
城市 1 的最高气温出现在 2022-07-07，为24度。
城市 2 的最高气温出现在 2022-08-07 和 2022-08-17，为37度。我们选择较早的日期 (2022-08-07)。
城市 3 的最高气温记录在 2022-12-07 年，为-6 度。

3.2 思路：

用常规方法子查询写超时了，然后发现可以用窗口函数写。

3.3 题解：

-- 使用窗口函数，city_id，然后剩下两个字段排序
-- 然后rank依次给窗口内的记录赋值
with tep as (select city_id, day, degree, rank() over (partition by city_id order by degree desc, day) cntfrom Weather
)
-- cnt为1的记录当然就是同一城市温度最高，日期最早的一天了
select city_id, day, degree
from tep
where cnt = 1
order by city_id

4. 力扣2298：周末任务计数

4.1 题目：

表: Tasks

+-------------+------+
| Column Name | Type |
+-------------+------+
| task_id     | int  |
| assignee_id | int  |
| submit_date | date |
+-------------+------+
task_id 是该表的主键（具有唯一值的列）。
此表中的每一行都包含任务 ID、委托人 ID 和提交日期。

编写一个解决方案来报告：

• 在周末 (周六，周日) 提交的任务的数量 weekend_cnt，以及
• 工作日内提交的任务数 working_cnt。

按 任意顺序 返回结果表。
返回结果格式如以下示例所示。

示例 1:

输入: 
Tasks 表:
+---------+-------------+-------------+
| task_id | assignee_id | submit_date |
+---------+-------------+-------------+
| 1       | 1           | 2022-06-13  |
| 2       | 6           | 2022-06-14  |
| 3       | 6           | 2022-06-15  |
| 4       | 3           | 2022-06-18  |
| 5       | 5           | 2022-06-19  |
| 6       | 7           | 2022-06-19  |
+---------+-------------+-------------+
输出: 
+-------------+-------------+
| weekend_cnt | working_cnt |
+-------------+-------------+
| 3           | 3           |
+-------------+-------------+
解释: 
Task 1 是在周一提交的。
Task 2 是在周二提交的。
Task 3 是在周三提交的。
Task 4 是在周六提交的。
Task 5 是在周日提交的。
Task 6 是在周日提交的。
3 个任务是在周末提交的。
3 个任务是在工作日提交的。

4.2 思路：

没见过的日期函数，dayofweek()。

有意思的是星期天返回1， 星期六返回7

4.3 题解：

--学到了一手日期的函数：dayofweek：返回的是数字
-- 但需要注意的是：周日是1， 周六是7with tep1(working_cnt) as (select count(*) working_cntfrom Taskswhere dayofweek(submit_date) in (2, 3, 4, 5, 6)
), tep2(weekend_cnt) as (select count(*) weekend_cntfrom Taskswhere dayofweek(submit_date) in (7, 1)
)select weekend_cnt, working_cnt
from tep1, tep2