Loading

力扣(LeetCode) - 好友推荐

一. 题目

表:Listens

这个表没有主键,可能存在重复项

表中的每一行表示用户 user_id 在 day 这一天收听的歌曲 song_id

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| song_id     | int     |
| day         | date    |
+-------------+---------+
表:Friendship

(user1_id, user2_id) 是这个表的主键

表中的每一行表示 user1_id 和 user2_id 是好友

注意:user1_id < user2_id

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
写出 SQL 语句,为用户推荐好友,我们将符合下列条件的用户 x 推荐给用户 y :
  1. 用户 x 和 y 不是好友
  2. 并且用户 x 和 y 在同一天收听了相同的三首或更多不同歌曲

注意,好友推荐是单向的,这意味着如果用户 x 和用户 y 需要互相推荐给对方,结果表需要将用户 x 推荐给用户 y 并将用户 y 推荐给用户 x

另外,结果表不得出现重复项(即:用户 y 不可多次推荐给用户 x )

按任意顺序返回结果表,查询格式如下示例所示:
示例 1
输入:
-- Listens 表:
+---------+---------+------------+
| user_id | song_id | day        |
+---------+---------+------------+
| 1       | 10      | 2021-03-15 |
| 1       | 11      | 2021-03-15 |
| 1       | 12      | 2021-03-15 |
| 2       | 10      | 2021-03-15 |
| 2       | 11      | 2021-03-15 |
| 2       | 12      | 2021-03-15 |
| 3       | 10      | 2021-03-15 |
| 3       | 11      | 2021-03-15 |
| 3       | 12      | 2021-03-15 |
| 4       | 10      | 2021-03-15 |
| 4       | 11      | 2021-03-15 |
| 4       | 13      | 2021-03-15 |
| 5       | 10      | 2021-03-16 |
| 5       | 11      | 2021-03-16 |
| 5       | 12      | 2021-03-16 |
+---------+---------+------------+
-- Friendship 表:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
+----------+----------+
输出:
+---------+----------------+
| user_id | recommended_id |
+---------+----------------+
| 1       | 3              |
| 2       | 3              |
| 3       | 1              |
| 3       | 2              |
+---------+----------------+
解释:
  • 用户 1 和 2 在同一天收听了歌曲 10、11 和 12,但他们已经是好友了
  • 用户 1 和 3 在同一天收听了歌曲 10、11 和 12。由于他们不是好友,所以我们给他们互相推荐为好友
  • 用户 1 和 4 没有收听三首相同的歌曲
  • 用户 1 和 5 收听了歌曲 10、11 和 12,但不是在同一天收听的
  • 类似地,我们可以发现用户 2 和 3 在同一天收听了歌曲 10、11 和 12,且他们不是好友,所以我们给他们互相推荐为好友。

二. 答案

SELECT DISTINCT t1.user1 user_id, t1.user2 recommended_id FROM
(
-- 首先通过自连接根据歌曲ID、听歌日期相同,但用户ID不同的条件过滤出同一天听了相同歌曲不同用户的数据
SELECT l1.user_id user1, l1.`day`, l2.user_id user2, COUNT(l2.song_id) count FROM listens l1 LEFT JOIN listens l2 ON l1.song_id = l2.song_id AND l1.`day` = l2.`day` AND l1.user_id <> l2.user_id 
-- 然后通过用户、日期分组统计听了3首歌及以上的数据
GROUP BY l1.user_id, l1.`day`, l2.user_id HAVING COUNT(l2.user_id) >= 3
) t1
-- 最后左连接好友表过滤掉已经是好友的用户
LEFT JOIN friendship t2 ON (t1.user1 = t2.user1_id AND t1.user2 = t2.user2_id) OR (t1.user1 = t2.user2_id AND t1.user2 = t2.user1_id)
-- (通过左连接好友表没有联查到任何数据说明可以互相推荐为好友)
WHERE t2.user1_id IS NULL AND t2.user2_id IS NULL
posted @ 2022-10-25 22:55  Schieber  阅读(135)  评论(0编辑  收藏  举报