今天的每日四题比较简单，主要其中两题可以用窗口函数轻松解决。
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 Activity2. 力扣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 candidateId
from Vote
group by candidateId
having count(*) >= all(
select count(*)
from Vote
group by candidateId
)
)
-- 然后简单的表join找到name
select name
from Candidate c
join tep t
on c.id = t.candidateId3. 力扣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) cnt
from Weather
)
-- cnt为1的记录当然就是同一城市温度最高，日期最早的一天了
select city_id, day, degree
from tep
where cnt = 1
order by city_id4. 力扣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， 周六是7
with tep1(working_cnt) as (
select count(*) working_cnt
from Tasks
where dayofweek(submit_date) in (2, 3, 4, 5, 6)
), tep2(weekend_cnt) as (
select count(*) weekend_cnt
from Tasks
where dayofweek(submit_date) in (7, 1)
)
select weekend_cnt, working_cnt
from tep1, tep2
扫一扫
1179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
