用于查找数据集中大多数值的组合的最佳算法

Question

----------------------------------------
   ColumnA  |  ColumnB      | ColumnC  | 
----------------------------------------
      Cat   |     Shirt     |   Pencil | 
      Dog   |     Shirt     |   Eraser | 
      Worm  |     Dress     |   Pen    | 
      Cow   |     Shirt     |   Pen    | 
      Cat   |     Shirt     |   Pen    | 
      Cat   |     Jacket    |   Pen    | 
      Cow   |     Shirt     |   Pen    | 
      Cat   |     Shirt     |   Pen    | 
      Cat   |     Jacket    |   Pen    | 
      Cow   |     Shirt     |   Pen    | 
      Cat   |     Shirt     |   Pen    | 
      Cat   |     Jacket    |   Pen    | 

With the example data above I am trying to find the most re-occuring combinations which are a pair of 2 or greater.

For example

Shirt,Pen 6
Cat,Pen 6    
Cat,Shirt 4
Jacket, Pen 3
Pen,Cow 3
Cat,Shirt,Pen 3
Cat,Jacket,Pen 3
Cow,Shirt,Pen 3

I need this for up to 10 columns of data.

Cat,Shirt is the same as Shirt,Cat.

What is the best algorithm to use? Preferably in SQL but I could also try PHP?

Answer 1

You can do this in SQL by identifying each row and adding an "empty" element. Note: this assumes that the values are different in each column -- or at least fungible (it doesn't matter which column one is in).

Let me also assume that each row has a unique id:

with t as (
      select id, col
      from data d outer apply
           (values (col1), (col2), (col3), (NULL)) v(col)
     )
select t1.col, t2.col, t3.col, count(*)
from t t1 join
     t t2
     on t1.id = t2.id and (t2.col > t1.col or t2.col is null) join
     t t3
     on t1.id = t3.id and (t3.col > t2.col or (t2.col is null and t3.col is null))
group by t1.col, t2.col, t3.col
order by count(*) desc;