SQL架构
表: Listens
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | song_id | int | | day | date | +-------------+---------+ 这个表没有主键,可能存在重复项。 表中的每一行表示用户 user_id 在 day 这一天收听的歌曲 song_id。
表: Friendship
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user1_id | int | | user2_id | int | +---------------+---------+ (user1_id, user2_id) 是这个表的主键。 表中的每一行表示 user1_id 和 user2_id 是好友。 注意,user1_id < user2_id。
写出 SQL 语句,为 Leetcodify 用户推荐好友。我们将符合下列条件的用户 x
推荐给用户 y
:
- 用户
x
和y
不是好友,且 - 用户
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,且他们不是好友,所以我们给他们互相推荐为好友。
with t1 as (
select
user_id,recommended_id #在同一天 听相同的歌的个数>=3的两个人的 id
from
(
select
l1.user_id user_id,l1.song_id,l1.day,l2.user_id recommended_id
from
Listens l1 left join Listens l2
#根据 day 和 song_id,还有 user_id 不相同, 左连接 拼表 然后 再根据 l1.user_id 和 l2.user_id 分组 求 去重后 歌曲的个数 >=3 就是 同一天 听相同的歌的个数>=3的两个人的 id
on l1.song_id = l2.song_id and l1.day = l2.day and l1.user_id <> l2.user_id
group by
l1.user_id,l1.day,l2.user_id
having count(distinct l1.song_id)>=3
)s1
where recommended_id is not null
),
t2 as (
select
user1_id,user2_id #求出 每个user_id 的朋友id
from
Friendship
union all
select
user2_id,user1_id
from
Friendship
)
select
t1.user_id,t1.recommended_id
from
t1 left join t2
#左连接拼表的目的是把t1 表中 互为朋友的 id 给去掉 不要用 not in ...效率低
on t1.user_id = t2.user1_id and t1.recommended_id = t2.user2_id
where t2.user1_id is null
group by
t1.user_id,t1.recommended_id