MYSQL:寻找 friend 之间相似和独特的喜好

Question

假设我们有两个表，如下所示:

1. friend (uid1 int，uid2 int)

   此表保存了彼此好友的所有 ID。

2. 喜欢(uid，盖子)

   此表保存每个用户 ID 喜欢的项目(按盖子索引)

我想知道，对于每个 friend A 和 B，他们都喜欢多少个项目，以及他们总共喜欢多少个独特的项目。

我使用的是 MySQL，所以我们没有完全连接。

对我来说，这似乎是一个非常复杂的查询。我认为我在某种程度上得到了其中一些值，但我想知道其他人会如何做到这一点:

以下是为 friend 和喜欢构建示例表的代码

create table friend (uid1 int, uid2 int);

insert into friend values
(1,2),
(2,1),
(1,3),
(2,3);

create table likes(uid int, lid int);

insert into likes values
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(3, 5),
(3, 4);

这是我迄今为止的实现:

select r.uid1, r.uid2, 
    sum(case when r.lid1 is null then 0 else 1 end) as uniqueLike1, 
    sum(case when r.lid2 is null then 0 else 1 end) as uniqueLike2, 
    sum(case when r.lid1 is not null and r.lid2 is not null then 1 else 0 end) as uniqueLike1and2
from
(select f.uid1 as uid1, f.uid2 as uid2, l1.lid as lid1, l2.lid as lid2
from friend f join likes l1 on f.uid1 = l1.uid left join likes l2 on f.uid2=l2.uid and l1.lid = l2.lid
union
select ff.uid1 as uid1, ff.uid2 as uid2, ll2.lid as lid1, ll1.lid as lid2
from friend ff join likes ll1 on ff.uid2 = ll1.uid left join likes ll2 on ff.uid1=ll2.uid and ll1.lid = ll2.lid) r
group by r.uid1, r.uid2;

Answer 1

这不是传统方法。我什至不 promise 它是有效的(特别是如果用户喜欢很多东西)。但是，它的优点是相对简单:

select f.uid1, f.uid2,
       count(distinct case when l1.lid = l2.lid then l1.lid end) as likes_in_common,
       (count(distinct l1.lid) - 
        count(distinct case when l1.lid = l2.lid then l1.lid end)
       ) as likes_only1,
       (count(distinct l2.lid) - 
        count(distinct case when l1.lid = l2.lid then l1.lid end)
       ) as likes_only2
from friend f left join
     likes l1
     on l1.uid = f.uid1 left join
     likes l2
     on l2.uid = f.uid2
group by f.uid1, f.uid2;

这个想法是计算共同项目的数量。这是第一个 count() 表达式。然后，通过减法给出唯一的。

COUNT(DISTINCT) 是必需的，因为 FROM 子句会为两个用户的所有点赞生成笛卡尔积。