力扣1892，页面推荐Ⅱ，为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id的 至少一个朋友喜欢 ，而 不被user_id喜欢 ，你的系统将 推荐 一个页面到user_id。
题目描述
表： 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。
您正在为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id的 至少一个朋友喜欢 ，而 不被user_id喜欢 ，你的系统将 推荐 一个页面到user_id。
编写一个解决方案来查找针对每个用户的所有可能的 页面建议 。每个建议应该在结果表中显示为一行，包含以下列:
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推荐页面。
解题思路
这个问题可以通过结合两个表（Friendship和Likes）来解决，目标是找到对每个用户推荐的页面。具体步骤如下：
- 确定朋友关系：首先，我们需要从
Friendship表中识别每个用户的所有朋友。由于朋友关系是双向的（如果A是B的朋友，那么B也是A的朋友），我们需要考虑user1_id和user2_id两个方向。 - 识别朋友喜欢的页面：接下来，我们需要查找每个用户的朋友喜欢哪些页面。
- 排除用户已经喜欢的页面：我们需要确保推荐的页面不包括用户已经喜欢的页面。
- 计算每个推荐页面的朋友喜欢数：对于每个推荐的页面，我们需要计算有多少个朋友喜欢它。
- 输出结果：最后，我们需要按照题目要求输出结果，包括
user_id、page_id和friends_likes。
完整代码
SELECT
F.user_id,
L.page_id,
COUNT(*) AS friends_likes
FROM (
SELECT user1_id AS user_id, user2_id AS friend_id FROM Friendship
UNION ALL
SELECT user2_id, user1_id FROM Friendship
) AS F
JOIN Likes AS L ON F.friend_id = L.user_id
LEFT JOIN Likes AS UL ON F.user_id = UL.user_id AND L.page_id = UL.page_id
WHERE UL.user_id IS NULL
GROUP BY F.user_id, L.page_id
ORDER BY F.user_id, L.page_id;
- 朋友关系：通过
UNION ALL将Friendship表中的user1_id和user2_id合并，确保朋友关系的双向性被考虑。 - 朋友喜欢的页面：通过将上述结果与
Likes表连接，找到每个用户的朋友喜欢哪些页面。 - 排除已喜欢的页面：使用
LEFT JOIN将用户喜欢的页面与朋友喜欢的页面进行比较，通过WHERE UL.user_id IS NULL条件排除用户已经喜欢的页面。 - 计算朋友喜欢数：通过
COUNT(*)计算每个推荐页面的朋友喜欢数。 - 输出结果：最后，根据
user_id和page_id分组，按照题目要求输出结果。
通过
优化
强调效率问题，其实在运行能力有限的情况下，讨论优化SQL是十分必要的。尽量多用left/right join + where xx is null的形式来代替not in的表示形式。
with t1 as (
select user1_id user_id
,user2_id friend_id
from Friendship
union all
select user2_id user_id
,user1_id friend_id
from Friendship
)
,t3 as (
select t1.user_id
,t1.friend_id
,t2.page_id
from t1
left join Likes t2
on t1.friend_id = t2.user_id
)
select t3.user_id
,t3.page_id
,count(1) friends_likes
from Likes t4
right join t3
on t4.user_id = t3.user_id
and t4.page_id = t3.page_id
where t4.page_id is null
group by 1,2
