SQL架构
表: Friendship
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user1_id | int | | user2_id | int | +---------------+---------+ (user1_id,user2_id)是Friendship表的主键。 该表的每一行表示用户user1_id和user2_id是好友。
表: Likes
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | page_id | int | +-------------+---------+ (user_id,page_id)是Likes表的主键。 (user_id, page_id) is the primary key for this table. 该表的每一行表示user_id喜欢page_id。
您正在为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id
的 至少一个朋友喜欢 ,而 不被user_id
喜欢 ,你的系统将 推荐 一个页面到user_id
。
编写一个SQL查询来查找针对每个用户的所有可能的 页面建议 。每个建议应该在结果表中显示为一行,包含以下列:
user_id
: 系统向其提出建议的用户的ID。page_id
: 推荐为user_id
的页面ID。.friends_likes
:user_id
对应page_id
的好友数。
以 任意顺序 返回结果表。
查询结果格式示例如下。
示例 1:
输入: Friendship 表: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 4 | | 2 | 5 | | 6 | 1 | +----------+----------+ Likes 表: +---------+---------+ | user_id | page_id | +---------+---------+ | 1 | 88 | | 2 | 23 | | 3 | 24 | | 4 | 56 | | 5 | 11 | | 6 | 33 | | 2 | 77 | | 3 | 77 | | 6 | 88 | +---------+---------+ 输出: +---------+---------+---------------+ | user_id | page_id | friends_likes | +---------+---------+---------------+ | 1 | 77 | 2 | | 1 | 23 | 1 | | 1 | 24 | 1 | | 1 | 56 | 1 | | 1 | 33 | 1 | | 2 | 24 | 1 | | 2 | 56 | 1 | | 2 | 11 | 1 | | 2 | 88 | 1 | | 3 | 88 | 1 | | 3 | 23 | 1 | | 4 | 88 | 1 | | 4 | 77 | 1 | | 4 | 23 | 1 | | 5 | 77 | 1 | | 5 | 23 | 1 | +---------+---------+---------------+ 解释: 以用户1为例: —用户1是用户2、3、4、6的好友。 -推荐页面有23(用户2喜欢),24(用户3喜欢),56(用户3喜欢),33(用户6喜欢),77(用户2和用户3喜欢)。 -请注意,第88页不推荐,因为用户1已经喜欢它。 另一个例子是用户6: —用户6是用户1的好友。 -用户1只喜欢了88页,但用户6已经喜欢了。因此,用户6没有推荐。 您可以使用类似的过程为用户2、3、4和5推荐页面。
exists:
with t1 as (
select
user1_id id,user2_id friend_id # 用户 id 和他的朋友 id
from
Friendship
union all
select
user2_id,user1_id
from
Friendship
),
t2 as (
select
t1.id user_id,l.page_id
from
t1 left join Likes l
on t1.friend_id = l.user_id
)
select
user_id, page_id,count(1) friends_likes
from
t2
where not exists (select user_id,page_id from Likes l where t2.user_id = l.user_id and t2.page_id = l.page_id ) -- EXISTS
group by user_id,page_id
join 和 null:
# Write your MySQL query statement below
with t1 as (
select
user1_id id,user2_id friend_id # 用户 id 和他的朋友 id
from
Friendship
union all
select
user2_id,user1_id
from
Friendship
),
t2 as (
select
t1.id user_id,t1.friend_id,l.page_id
from
t1 left join Likes l
on t1.friend_id = l.user_id
)
select
t2.user_id, t2.page_id,count(1) friends_likes
from
t2 left join Likes l
on t2.user_id = l.user_id and t2.page_id = l.page_id
where l.user_id is null
group by t2.user_id,t2.page_id
笔记:
不要用 not in 效率低 用 left join 然后 where 过滤 有null 的或者 没 null的 效率会高很多
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况(本题选择使用not exists)
就记第一个 第二种 会混 记不住