- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
ActorDirector
表:+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp 是这张表的主键(具有唯一值的列).编写解决方案找出合作过至少三次的演员和导演的 id 对
(actor_id, director_id)
示例 1:
输入: ActorDirector 表: +-------------+-------------+-------------+ | actor_id | director_id | timestamp | +-------------+-------------+-------------+ | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 1 | 6 | +-------------+-------------+-------------+ 输出: +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ 解释: 唯一的 id 对是 (1, 1),他们恰好合作了 3 次。
三,建表语句
Create table If Not Exists ActorDirector (actor_id int, director_id int, timestamp int);
Truncate table ActorDirector;
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '0');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '1');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '2');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '3');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '4');
insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '5');
insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '6');
select * from ActorDirector;
四,分析
题解:
表:演员与导演
字段:演员id,导演id,时间戳
求,演员与导演合作过至少3次的 演员和导演
分析:
解法一,开窗解法
第一步,count(*) 以演员+导演id分组 拿到次数
第二步,大于=3的值 在去重一下
解法二
常规分组+过滤
五,SQL解答
解法一,开窗
with t1 as (
select actor_id, director_id, timestamp ,
count(*) over(partition by actor_id ,director_id) co
from ActorDirector
)
select distinct actor_id,director_id from t1 where t1.co>=3;
解法二,分组+过滤+聚合
select actor_id,director_id from actordirector group by actor_id,director_id having count(actor_id)>=3;
六,验证
七,知识点总结
- 分组,以多个字段分组的练习
- 分组+过滤+聚合的练习
- 开窗函数的练习
- 分组求count最大次数的练习
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用