How can you compare sets of numbers and get the most relevant results using MySQL and PHP?

Question

Consider this:

set A: 1 2 3 4
set B:     3 4 5 6
set C:       4 5 6 7
set D: 1

I want to compare D with the rest and get as a result a set of numbers as most relevant. The result should be in this order: 4 (as D has a common number with A and 4 is in A and also in B and C), 3 (as D has a common number with A and 3 is in A and B), 2 (as D has a common number with A and 2 is also in A), then 5, 6, 7.

Is there some algorithm to do this in an efficient way in PHP/MySQL? I don't want to reinvent the wheel, and also the database would eventually have a huge number of sets..

Answer 1

In SQL, I'll assume you have a table called sets, with 2 columns, e for the elements and s for the set name.

select e,count(*) as c from sets where s in
(select s from sets where e in (select e from sets where s='D') group by s)
group by e order by c desc

explanation:

(select e from sets where s='D')

selects the elements of group D.

(select s from sets where e in (select e from sets where s='D') group by s)

selects all the groups that have common members with the previously selected group.

and then you select all elements from these sets, and order them by the number of appearances (as joel suggested)