1892.(Hard)Page Recommendations II

Table: Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id,user2_id)是Friendship表的主键。
该表的每一行表示用户user1_id和user2_id是好友。

Table: 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的好友数。 以任意顺序返回结果表。

查询结果格式示例如下:

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:
+---------+---------+---------------+
| 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推荐页面。

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/page-recommendations-ii

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

用 not in 排除“自己已喜欢的页面” 可以做出来，但是效率较低，超出时间限制了。

---- ⚠️⚠️⚠️
with user_friend as 
(
    SELECT user1_id, user2_id FROM Friendship
    union 
    SELECT user2_id, user1_id FROM Friendship
)
    SELECT f.user1_id user_id, l.page_id page_id, count(DISTINCT f.user2_id) friends_likes
    FROM user_friend f 
    LEFT JOIN Likes l on f.user2_id = l.user_id 
    where (user1_id, page_id) not in (SELECT * from Likes)
    GROUP BY f.user1_id, l.page_id

正确做法：

• 两次 LEFT JOIN Likes ，分别匹配出“朋友喜欢”和“我喜欢”，注意先后顺序。

• 配合 where l1.page_id is null 剔除掉同时喜欢的页面。

with user_friend as 
(
    SELECT user1_id, user2_id FROM Friendship
    union 
    SELECT user2_id, user1_id FROM Friendship
)
SELECT f.user1_id user_id, l2.page_id page_id, count(DISTINCT f.user2_id) friends_likes
FROM user_friend f 
LEFT JOIN Likes l2 on f.user2_id = l2.user_id  -- friends like
LEFT JOIN Likes l1 on f.user1_id = l1.user_id and l1.page_id = l2.page_id  -- we all like
where l1.page_id is null 
GROUP BY f.user1_id, l2.page_id
order by f.user1_id

Schema

Drop table Friendship, Likes;
Create table If Not Exists Friendship (user1_id int, user2_id int);
Create table If Not Exists Likes (user_id int, page_id int);
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
insert into Friendship (user1_id, user2_id) values ('1', '3');
insert into Friendship (user1_id, user2_id) values ('1', '4');
insert into Friendship (user1_id, user2_id) values ('2', '3');
insert into Friendship (user1_id, user2_id) values ('2', '4');
insert into Friendship (user1_id, user2_id) values ('2', '5');
insert into Friendship (user1_id, user2_id) values ('6', '1');
Truncate table Likes;
insert into Likes (user_id, page_id) values ('1', '88');
insert into Likes (user_id, page_id) values ('2', '23');
insert into Likes (user_id, page_id) values ('3', '24');
insert into Likes (user_id, page_id) values ('4', '56');
insert into Likes (user_id, page_id) values ('5', '11');
insert into Likes (user_id, page_id) values ('6', '33');
insert into Likes (user_id, page_id) values ('2', '77');
insert into Likes (user_id, page_id) values ('3', '77');
insert into Likes (user_id, page_id) values ('6', '88');
----