Как сравнить записи по свойствам?

Question

[Diversia]

Пользователи проходят тест, состоящего из 40 вопросов у которого НЕТ ПРАВИЛЬНОГО ответа, сохраняются просто варианты ответов. Вопросы с один вариантом ответа. Кол-во пользователей прошедших тест (кол-во результатов) может быть десятки тысяч. Каждый новый пользователь проходит тест, а результат заносится в таблицу (т.е. со временем появляются новые).
Пользователь добавил свой результат, который нужно сравнить с другими результатами и вывеси таблицу с сортировкой от лучшего к худшему (наиболее подходящему). Как оптимально с точки зрения производительности решить эту задачу?

Важно! Каждый новый пользователь со свои результатом - это эталон правильных ответов по отношению к другим.

Вариант 1 (неудачный). Создать таблицу со 40 свойствами. Заполним ее 10000 записями (к примеру). Далее выбираем одну из записей и сравниваем ее с остальными. Т.е. будет фильтр по записям (полям записи). В этом варианте будут выбраны только точно совпавшие записи и не будет возможности построить таблицу с сортировкой от лучшего к худшему.

Вариант 2 (неудачный). Через цикл открывать каждую запись и сравнивать свойства, создавая новый массив для построения таблицы. Но записей много и сервер загнется.

Comments

[Konata Izumi]

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

Тут не совсем понятно, чего вы хотите добиться?
Есть новый результат пользователя. Есть результаты предыдущих пользователей. Результат добавляется в таблицу.
Что дальше?

[Diversia]

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

[Александр]

Konata Izumi, +1.
И не нужно ставить "сложный" вопрос. У вас явная проблема в ТЗ. Сформулируейте его правильно, и вопросы по реализации сами собой отпадут.

[Konata Izumi]

Diversia, Вам не нужно сравнивать результаты.
Достаточно сделать выборку из таблицы с сортировкой.

[Diversia]

Александр, первый абзац подредактировал. Понятнее не могу, извините.

[Diversia]

Konata Izumi, так свойств же 40, как они смогу отсортироваться от лучшего к худшему (наиболее подходящему).

[Александр]

Diversia, зачем вам 40 свойств? У вас должна быть итоговая оценка, сколько пользователь набрал балов. По ней и сортируйте и делайте выборку ближайших.

[Diversia]

Александр, вот в этом и сложность! Каждый новый пользователь со свои результатом - это эталон правильных ответов по отношению к другим.

[Konata Izumi]

Diversia, Я думал, у вас есть таблица или колонка с итоговой суммой баллов за тест. По ней и сортировать.
А так вы пытаетесь сортировать по 40 колонкам?

[Konata Izumi]

Diversia, Что значит "эталон"?

[Diversia]

Konata Izumi, эталон - это правильный вариант относительно других записей. Т.е. я ответил на вопросы и хочу найти таких же пользователей как и я сам. Наиболее подходящих.

[Александр]

Diversia, Вообщем задача теперь ясна. Но лень писать решение :)

Answer 1

[Александр]

Есть 1 финт, с помощью которого можно реализовать вашу задачу, сортировать по расстоянию Левенштейна. Я дам вам пример на php. Думаю как это реализовать на стороне sql сами найдете:

$me = '1111111111';
$results = [
    '2222222222',
    '1111211141',
    '1111111111',
    '1111112111',
    '1121111111'
];

// ASC
usort($results, function ($a, $b) use ($me) {
    return levenshtein($me, $a) > levenshtein($me, $b);
});
var_dump('ACS', $results);

// DESC
usort($results, function ($a, $b) use ($me) {
    return levenshtein($me, $a) < levenshtein($me, $b);
});
var_dump('DESC', $results);

P.S. Сам алгоритм в данному случае не совсем подойдет, но можно сделать похожий не него.

Comments

[Diversia]

Спасибо, интересное решение. Но вопрос остался открытым при большом кол-ве записей и производительностью.

[Александр]

Diversia, вариант для SQL.

SELECT *, (
	IF (`a1` = 'e', 1, 0) +
    IF (`a2` = 'e', 1, 0) +
    IF (`a3` = 'e', 1, 0) +
    IF (`a4` = 'e', 1, 0) +
    IF (`a5` = 'e', 1, 0) +
    IF (`a6` = 'e', 1, 0) +
    IF (`a7` = 'e', 1, 0) +
    IF (`a8` = 'e', 1, 0)
) as `weight`
FROM `results`
ORDER BY `weight` DESC;

[Diversia]

Александр, поясните пожалуйста про

IF (`a1` = 'e', 1, 0) ...

[Александр]

Diversia, a1 - это столбец с ответом, а e - это ответ текушего пользователя.

[Diversia]

Александр, а 1, 0 - это то/иначе?

[Александр]

Diversia, да, если совпадает, то 1, если нет то 0. Затем суммирете все совпадения и по ним сортируете.

[Diversia]

Александр, а limit тут использовать как я понимаю нельзя?

[Александр]

Diversia, можно.

[Diversia]

Александр, Если использовать limit, то пагинация будет не по всем результатам (записям) в таблице. Т.е. Если 1000 записей, а мы делаем limit 20 (например) и сортировка будет только по этим 20 записям без учета остальных. Или это не так?

[Александр]

Diversia, это обычный запрос. Все, что можно сделать с обычной выборкой, можно сделать и с ним, в том числе и пагинацию.

[Diversia]

Александр, пример: мои ответы на вопросы (результат) - 2222222222.
Другие результаты:
1) 1222212122
2) 2121212222
3) 2222121222
4) 2222222221
5) 1122222222
...
200) 2222222222

Если сделать limit 10, то результат под номером 200 не будет в таблице. Или я не прав?

[Diversia]

Александр, имел ввиду не будет на первой странице

[Diversia]

Александр, ответьте пожалуйста

[Александр]

Diversia, результат 200 будет отсортирован и будет 1-м в списке т.к. у него будет максимальное значение в поле `weight`.

Answer 2

[Konata Izumi]

Надо было сразу уточнить, что без вариантов ответа.

Создать таблицу со 40 свойствами.

Это излишне. Вижу такую структуру.

Таблица с вопросами: id, текст вопроса,
Таблица с ответами: id, id вопроса, id юзера, ответ, флаг эталона
Таблица со статистикой: id, id юзера, счет (% эталонных ответов)

1ая таблица будет неизменна (пока не добавятся новые вопросы)
во 2ой будут добавлятся новые ответы, а при новом эталоне будут меняться флаги.
в 3ей будет пересчет после обновления эталона, на основе неё можно сортировать юзеров по счету

Comments

[Diversia]

поясните пожалуйста, что такое "флаг эталона"? Все ответы пользователя - это эталон для конкретного вопроса у каждого из пользователей.

[Diversia]

варианты ответов есть по каждому вопросу.

[Konata Izumi]

Diversia, в моем понимании. Есть вопрос, есть множество вариантов ответа, которые давали на него пользователи. Один из них помечаем эталонным.
Видимо, это представление так же неверно.
Теперь выясняется, что у каждого пользователя свой эталонный ответ. Т.е. эталонных ответов для одного вопроса может быть больше 1го.
Тут по сути меняется алгоритм подсчета % эталонных ответов у юзера. Данные все те же.

P.s. с каждым уточнением задача становится сложнее и сложнее.
P.p.s Вам бы и правда от начала до конца сформулировать ТЗ, а потом за ответами ходить. Не удивлюсь, если выяснятся еще какие-то подробности, которые серьезно повлияют на реализацию.

[Александр]

Konata Izumi, я думаю понял, что хочет ТС. Он хочет сделать релевантную сортировку результатов для текущего пользователя, который завершил тест. 1-ый в списке результат пользователя, а дольше по убыванию, от тех кто дал больше таких же ответов, как и он сам, к тем, кто ответил иначе на все вопросы.

[Diversia]

Александр, спасибо! Правильно переформулировали мой вопрос.