Лучший алгоритм для поиска наиболее часто встречающихся комбинаций значений в наборе данных
----------------------------------------
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 |
С данными примера выше я пытаюсь найти наиболее повторяющиеся комбинации, которые представляют собой пару 2 или больше.
Например
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
Мне нужно это до 10 столбцов данных.
Cat,Shirt такой же как Shirt,Cat,
Какой алгоритм лучше всего использовать? Желательно в SQL, но я также мог бы попробовать PHP?
Решение
Вы можете сделать это в SQL, идентифицируя каждую строку и добавляя «пустой» элемент. Примечание: это предполагает, что значения различны в каждом столбце — или, по крайней мере, взаимозаменяемы (не имеет значения, в каком столбце один).
Позвольте мне также предположить, что каждая строка имеет уникальный идентификатор:
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;
Другие решения
Одним из способов может быть это
SELECT c1, c2, c3, count(*) FROM (
SELECT ColumnA AS c1, ColumnB AS c2, NULL AS c3 FROM your_table
UNION ALL
SELECT ColumnA AS c1, ColumnC AS c2, NULL AS c3 FROM your_table
UNION ALL
SELECT ColumnB AS c1, ColumnC AS c2, NULL AS c3 FROM your_table
UNION ALL
SELECT ColumnA AS c1, ColumnB AS c2, ColumnC AS c3 FROM your_table
) tt
group by c1, c2, c3
order by count(*) desc