SQL: возвращает таблицу пользователя с вычисленным столбцом для процентного соотношения?

В настоящее время я пишу webapp, который соответствует пользователям на основе ответа на вопрос. Я реализовал свой алгоритм соответствия только в одном запросе и настроил его до сих пор, чтобы вычислить процент соответствия между двумя пользователями. Но мой webapp должен взять список пользователей и выполнить итерацию в списке, выполняющем этот запрос. Для 5000 пользователей это заняло 50 секунд на моей локальной машине. Можно ли разместить все в одном запросе, который возвращает один столбец с user_id и одним столбцом с вычисленным совпадением? Или это хранимая процедура?

В настоящее время я работаю с MySQL, но хочу переключать базы данных, если это необходимо.

Для всех, кто интересуется схемой и данными, я создал SQLFiddle: http://sqlfiddle.com/#!2/84233/1

и мой соответствующий запрос:

SELECT COALESCE(SQRT( (100.0*as1.actual_score/ps1.possible_score) * (100.0*as2.actual_score/ps2.possible_score) ) - (100/ps1.commonquestions), 0) AS perc FROM (SELECT SUM(imp.value) AS actual_score FROM user_questions AS uq1 INNER JOIN importances imp ON imp.id = uq1.importance INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = 101 AND (uq1.accans1 = uq2.answer_id OR uq1.accans2 = uq2.answer_id OR uq1.accans3 = uq2.answer_id OR uq1.accans4 = uq2.answer_id) WHERE uq1.user_id = 1) AS as1, (SELECT SUM(value) AS possible_score, COUNT(*) AS commonquestions FROM user_questions AS uq1 INNER JOIN importances ON importances.id = uq1.importance INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = 101 WHERE uq1.user_id = 1) AS ps1, (SELECT SUM(imp.value) AS actual_score FROM user_questions AS uq1 INNER JOIN importances imp ON imp.id = uq1.importance INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = 1 AND (uq1.accans1 = uq2.answer_id OR uq1.accans2 = uq2.answer_id OR uq1.accans3 = uq2.answer_id OR uq1.accans4 = uq2.answer_id) WHERE uq1.user_id = 101) AS as2, (SELECT SUM(value) AS possible_score FROM user_questions AS uq1 INNER JOIN importances ON importances.id = uq1.importance INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = 1 WHERE uq1.user_id = 101) AS ps2 

Мне было скучно, поэтому: Вот перезаписанная версия вашего запроса – на основе порта PostgreSQL вашей схемы – который вычисляет совпадения для всех пар пользователей одновременно:

http://sqlfiddle.com/#!12/30524/6

Я проверил, и он дает те же результаты для пары пользователей (1,5).

 WITH userids(uid) AS ( select distinct user_id from user_questions ), users(u1,u2) AS ( SELECT u1.uid, u2.uid FROM userids u1 CROSS JOIN userids u2 WHERE u1 <> u2 ), scores AS ( SELECT sum(CASE WHEN uq2.answer_id IN (uq1.accans1, uq1.accans2, uq1.accans3, uq1.accans4) THEN imp.value ELSE 0 END) AS actual_score, sum(imp.value) AS potential_score, count(1) AS common_questions, users.u1, users.u2 FROM user_questions AS uq1 INNER JOIN importances imp ON imp.id = uq1.importance INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id INNER JOIN users ON (uq1.user_id=users.u1 AND uq2.user_id=users.u2) GROUP BY u1, u2 ), score_pairs(u1,u2,u1_actual,u2_actual,u1_potential,u2_potential,common_questions) AS ( SELECT s1.u1, s1.u2, s1.actual_score, s2.actual_score, s1.potential_score, s2.potential_score, s1.common_questions FROM scores s1 INNER JOIN scores s2 ON (s1.u1 = s2.u2 AND s1.u2 = s2.u1) WHERE s1.u1 < s1.u2 ) SELECT u1, u2, COALESCE(SQRT( (100.0*u1_actual/u1_potential) * (100.0*u2_actual/u2_potential) ) - (100/common_questions), 0) AS "match" FROM score_pairs; 

Нет причин, по которым вы не могли бы перенести это обратно в MySQL, поскольку CTE доступен только для чтения и не делает ничего, что вы не можете сделать с FROM (SELECT ...) . Нет предложения WITH RECURSIVE , и на CTE не ссылаются более чем на один другой CTE. У вас будет немного страшный вложенный запрос, но это просто проблема форматирования.

Изменения:

  • Создание набора отдельных пользователей
  • Self-join, что набор различных пользователей для создания набора пар пользователей
  • а затем присоединитесь к этому списку пар в запросе оценки для создания таблицы оценок
  • Создайте таблицу баллов, объединив в основном дублирующиеся запросы для возможных значений 1 и possiblelescore2, actualscore1 и actualscore2.
  • затем суммируйте его в последнем внешнем запросе

Я не оптимизировал запрос; как написано, он работает в 5 мс в моей системе. На больших данных возможно, вам может понадобиться реструктурировать некоторые из них или использовать трюки, такие как преобразование некоторых предложений CTE в SELECT ... INTO TEMPORARY TABLE temp, которые вы затем индексируете перед запросом.

Также возможно, что вы захотите переместить генерацию набора строк users из CTE и в предложение FROM subquery из scores . Это связано с тем, что WITH должен вести себя как оптимизационный забор между предложениями, поэтому база данных должна материализовать строки и не может использовать трюки, такие как выдвижение предложений вверх или вниз.