我有一个名为features
的表,其中包含多个用户A,B,C
等的功能。
我想匹配最密切相关的用户。
例如,如果我想要最密切相关的匹配A
,那么在下表中A = {1,2,3,4,5,6}
和B = {1,2,3,4}
,这意味着A和B是比A
和C
最接近的匹配
如何实现 SQL 查询以匹配特定用户最密切相关的查询?
user features
------------------
A 1
A 2
A 3
A 4
A 5
A 6
B 1
B 2
B 3
B 4
C 3
C 4
您可以计算自连接的共同要素数量:
select f.user, f2.user, count(*) as num_in_common
from features f join
features f2
on f.feature = f2.feature and f.user <> f2.user
where f.user = @user -- whatever user you want to compare to
group by f.user, f2.user
order by count(*) desc;
http://sqlfiddle.com/#!9/e73813/13
Select
user, count(features in (Select features from t where user = 'A')) Count
From
t
Group By user
Order by Count Desc