LeetCode-SQL（五）

最新推荐文章于 2024-05-24 15:09:18 发布

Fresh_anyu

最新推荐文章于 2024-05-24 15:09:18 发布

阅读量1.5k

点赞数

分类专栏： SQL 文章标签： sql leetcode 数据库

本文链接：https://blog.csdn.net/Sdhrs_nn/article/details/122420819

版权

以下题目均来自力扣

81、1225.报告系统状态的连续日期

难度：★★★★★

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.

系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+


Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。

解答：

两表进行组合
select 'failed' period_state,fail_date dt from Failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' period_state,success_date dt from Succeeded where success_date between '2019-01-01' and '2019-12-31'
开窗
select period_state,dt,rank() over(partition by period_state order by dt) rk
from
(select 'failed' period_state,fail_date dt from Failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' period_state,success_date dt from Succeeded where success_date between '2019-01-01' and '2019-12-31') t
计算
select period_state,dt,date_sub(dt,interval rk day) dt1 from
(select period_state,dt,rank() over(partition by period_state order by dt) rk
from
(select 'failed' period_state,fail_date dt from Failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' period_state,success_date dt from Succeeded where success_date between '2019-01-01' and '2019-12-31') t) k order by dt1
分组求日期
select period_state,min(dt) start_date,max(dt) end_date
from
(select period_state,dt,date_sub(dt,interval rk day) dt1 from
(select period_state,dt,rank() over(partition by period_state order by dt) rk
from
(select 'failed' period_state,fail_date dt from Failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' period_state,success_date dt from Succeeded where success_date between '2019-01-01' and '2019-12-31') t) k order by dt1) tmp
group by dt1,period_state order by start_date

拓展：连续问题

连续问题一般都是进行开窗排序，然后相减得到相同的就是连续的

82、1241.每个帖子的评论数

难度：★★☆☆☆

表 Submissions 结构如下：

+---------------+----------+
| 列名           | 类型     |
+---------------+----------+
| sub_id        | int      |
| parent_id     | int      |
+---------------+----------+
上表没有主键, 所以可能会出现重复的行。
每行可以是一个帖子或对该帖子的评论。
如果是帖子的话，parent_id 就是 null。
对于评论来说，parent_id 就是表中对应帖子的 sub_id。

编写 SQL 语句以查找每个帖子的评论数。

结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。

Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。

Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

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

Submissions table:
+---------+------------+
| sub_id  | parent_id  |
+---------+------------+
| 1       | Null       |
| 2       | Null       |
| 1       | Null       |
| 12      | Null       |
| 3       | 1          |
| 5       | 2          |
| 3       | 1          |
| 4       | 1          |
| 9       | 1          |
| 10      | 2          |
| 6       | 7          |
+---------+------------+

结果表：
+---------+--------------------+
| post_id | number_of_comments |
+---------+--------------------+
| 1       | 3                  |
| 2       | 2                  |
| 12      | 0                  |
+---------+--------------------+

表中 ID 为 1 的帖子有 ID 为 3、4 和 9 的三个评论。表中 ID 为 3 的评论重复出现了，所以我们只对它进行了一次计数。
表中 ID 为 2 的帖子有 ID 为 5 和 10 的两个评论。
ID 为 12 的帖子在表中没有评论。
表中 ID 为 6 的评论是对 ID 为 7 的已删除帖子的评论，因此我们将其忽略。

解答：

找出是帖子的id
select distinct sub_id post_id from Submissions where parent_id is null;
上表和Submissions进行左连接
select distinct post_id,sub_id
from (select distinct sub_id post_id from Submissions where parent_id is null) t
left join Submissions s on t.post_id=s.parent_id
;
分组求数
select post_id,sum(if(sub_id is null,0,1)) number_of_comments
from (select distinct post_id,sub_id
from (select distinct sub_id post_id from Submissions where parent_id is null) t
left join Submissions s on t.post_id=s.parent_id) r group by post_id order by post_id
;

83、1251.平均售价

难度：★★☆☆☆

Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id，start_date，end_date) 是 Prices 表的主键。
Prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。
查询结果格式如下例所示：

Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
 
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+

Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

解答：

两表进行连接
select u.product_id product_id,units*price price,units
from UnitsSold u join Prices p on u.product_id=p.product_id where
u.purchase_date between p.start_date and p.end_date
;
分组求平均值
select product_id,round(sum(price)/sum(units),2) average_price from(
select u.product_id product_id,units*price price,units
from UnitsSold u join Prices p on u.product_id=p.product_id where
u.purchase_date between p.start_date and p.end_date) t group by product_id
;

84、1264.页面推荐

难度：★★★☆☆

朋友关系列表： Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
这张表的主键是 (user1_id, user2_id)。
这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。

喜欢列表： Likes

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| page_id     | int     |
+-------------+---------+
这张表的主键是 (user_id, page_id)。
这张表的每一行代表着 user_id 喜欢 page_id。

写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

你返回的结果中不应当包含重复项。

返回结果的格式如下例所示：

Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+
 
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+

Result table:
+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为： 页面23 来自于 用户2, 页面24 来自于 用户3, 页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐，因为 用户1 已经喜欢了它。

解答：

求出用户1的朋友
select user1_id from Friendship where user2_id=1
union all
select user2_id from Friendship where user1_id=1
找到用户1朋友喜欢的
select distinct page_id
from Likes where user_id in (select user1_id from Friendship where user2_id=1
union all
select user2_id from Friendship where user1_id=1)
找到用户1喜欢的
select page_id from Likes where user_id=1
去除掉用户1喜欢的
select page_id
from (select distinct page_id
from Likes where user_id in (select user1_id from Friendship where user2_id=1
union all
select user2_id from Friendship where user1_id=1)) t where page_id not in (select page_id from Likes where user_id=1)

85、1280.学生们参加各科测试的次数

难度：★★☆☆☆

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
主键为 student_id（学生ID），该表内的每一行都记录有学校一名学生的信息。

科目表: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+

最低0.47元/天解锁文章

Fresh_anyu

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
LeetCode-SQL（五）

以下题目均来自力扣81、1225.报告系统状态的连续日期难度：★★★★★Table: Failed+--------------+---------+| Column Name | Type |+--------------+---------+| fail_date | date |+--------------+---------+该表主键为 fail_date。该表包含失败任务的天数.Table: Succeeded+--------------+--.
复制链接

扫一扫

专栏目录