Как сформировать SQL (для MySQL) запрос по нахождению наиболее похожего профиля с множеством полей-флагов?

Question

[Роми]

Есть таблица с профилями такого типа (упростил для наглядности):

id | name | flag1 | flag2 | flag3 | flag4
1 | user1 | 1 | 0 | 1 | 0
2 | user2 | 0 | 1 | 0 | 0
3 | user3 | 1 | 1 | 0 | 1
4 | user4 | 1 | 0 | 0 | 0

каким будет SQL-запрос, выбирающий для, например, user1, профили, с наибольшим числом совпадений, и ранжирующий их по этому признаку?

я почему-то уверен, что это типовая задача для БД))

просто я с ней раньше не сталкивался, и не хочу велосипеды придумывать

вдруг есть типовое идеальное решение и вы им поделитесь. т.е. чтобы это было наименее ресурсоемко из возможных.

Comments

[Денис Юрьев]

правильное с точки зрения математики решение - через векторизацию и нахождение вектора, ближайшего к данному (см расстояние между векторами)

на практике это крайне ресурсоемко, только если заранее периодически пересчитывать для всех пользователей

[Роми]

Денис Юрьев,

правильное с точки зрения математики решение - через векторизацию и нахождение вектора, ближайшего к данному (см расстояние между векторами)

А как это на SQL будет выглядеть для данного случая?

на практике это крайне ресурсоемко, только если заранее периодически пересчитывать для всех пользователей

Да, я это понимаю. Надо будет делать фоновый процесс и писать результаты в отдельную таблицу(ы) с периодическим обновлением.

[Денис Юрьев]

Роми, если у вас в таблице только булевые значения, вернее дискретно 1 и 0, то вариант Slava Rozhnev - частный случай векторизации, пусть и грубоватый, но в данном случае упращения дают пользу по профиту со скоростью

[Роми]

Денис Юрьев, а можете чуточку развернуть вот это:

но в данном случае упращения дают пользу по профиту со скоростью

?

Это можно как-то ускорить? Как именно?

[Денис Юрьев]

Роми, в смысле, что там уже упростил автор ответа уже всю векторную алгебру, даже если сделал это не умышлено.

я бы только умножение на единицу убрал, лишняя операция, но предполагаю что mysql научился такие вещи оптимизировать.

[Роми]

Денис Юрьев, ну это типа коэффициент важности, вес, тоже годная штука!)))

Answer 1

[ComodoHacker]

Велосипед, конечно же, уже придуман, и называется расстояние Хэмминга. Только ваши флаги нужно представить в виде битовой строки.

https://stackoverflow.com/questions/4777070/hammin...

Comments

[d'Ivan]

https://habr.com/ru/post/211264/

[ComodoHacker]

Лентюй, Да. Зато можно материализовать.

[Роми]

это и на русском-то не очень понятно... )))) ок, разберусь, спасибо!

[Роми]

Роман Мирр, пробежался глазами, на ночь я это точно читать не буду)))))

[Роми]

и да, большое спасибо за "расстояние Хэмминга" - теперь хоть понятно что гуглить)))))) буду искать статью, которая не разрушит мою психику ))

[d'Ivan]

Роми,
я бы не скидывал со счетов решение от Slava Rozhnev и перепроверил бы, чтобы не отмечать решение сгоряча.

Answer 2

[Slava Rozhnev]

Можно предложить такое решение:

select * 
from users
order by ((flag1=1)*1 + (flag2=0)*1 + (flag3=1)*1 +(flag4=0)*1) desc
;

MariaDB fiddle

При сортировке используем сравнение параметров с желаемым профилем. При необходимости можно использовать различные коэффициенты для различных параметров

Comments

[Роми]

вау! спасибо!

а что значит эта конструкция:

order by (flag1=1)*1

? :)

т.е.

order by flag1

это я понимаю :) а то что вы написали?

[profesor08]

Роми чтоб было понятнее, тут происходит следующее. Значение операции сравнения приводится к 1 или 0, далее вычисляется сумма. Чем больше будет совпадений, тем большее значение будет у суммы. А зная эту сумму, можно отсортировать по убыванию. Таким образом сверху будут наиболее подходящие записи. Единственном тут надо исключить запись изначального юзера, чтоб не мешалась. Сам же запрос строится на основе данных флагов нужного юзера. В качестве флагов может быть что угодно. Так-же можно умножать не на 1, а на промежуточное значение между от 0 до 1, таким образом можно балансировать важность тех или иных флагов.

[Slava Rozhnev]

Роми, сравнение в MySQL дает резулбтат 0 или 1. Соответственно в нашем случае если flag1 равен единице то (flag1=1)*1 будет тоже равно единице. При совпадении всех 4х флагов получим максимальный вариант - 4. Так, как сортировка по убываию то следом будет строка с 3-мя совпадениями и т. д.

[Slava Rozhnev]

profesor08, Спасибо за подробный комментарий

[Роми]

Slava Rozhnev, аааа.... круто!)) Спасибо большое!

[Роми]

profesor08, если честно: сходу я не понял)) я помедитирую над этим))

Скажите, а ведь такая выборка на базе со, скажем, хотя бы миллионом записей, будет очень ресурсоемкая, да?

[Роми]

Slava Rozhnev, повторю вопрос Профессору:

Скажите, а ведь такая выборка на базе со, скажем, хотя бы миллионом записей, будет очень ресурсоемкая, да?

[Роми]

Slava Rozhnev, и да, а зачем тогда умножать на 1?

я про

*1

или это не умножение?)))

[Akina]

Роми, это весовые коэффициенты отдельных флагов. Если все флаги равнозначны, само собой все умножения на 1 можно поудалять.

Вы же просили именно типовое решение...

[Роми]

Akina, спасибо!

//а оно реально типовое? ))

[Akina]

Роми,

а оно реально типовое?

Оно не соответствует требованиям нормализации. Впрочем, предметная область не определена, потому нельзя сказать, оправдана нормализация или нет. Если нет - то решение действительно типовое.

[Роми]

Лентюй, вот именно в таком виде если, как предложено?

А что делать? (помимо того, что вынести это в фоновые процессы)

[Роми]

Akina, ну, планируется сортировать профайлы по степени схожести, типа как это делается на дэйтинг-сервисах. Т.е. что-то вроде того ))

[Akina]

Роми,

планируется сортировать профайлы по степени схожести

Тогда посмотрите на предмет использования SET datatype и соответственно BIT_COUNT().