Выберите объекты, которые являются вершиной N результаты для связанной таблицы
Скажите, что у меня есть игра, где вопрос задают, люди отправляют ответы, которые выиграны, и лучшие 10 побед ответов. У меня есть база данных SQL, которая хранит всю эту информацию, таким образом, у меня могли бы быть таблицы, такие как Пользователи, Вопросы и Ответы. Таблица Responses имеет foreign_keys user_id и question_id и атрибут total_score.
Очевидно, для конкретного Вопроса я могу получить лучшие 10 Ответов с порядком и пределом:
SELECT * FROM Responses WHERE question_id=? ORDER BY total_score DESC LIMIT 10;
То, что я ищу, является способом, которым я могу определить, для конкретного Пользователя, списка всех их Ответов, которые являются победителями (в лучших 10 для их конкретного Вопроса). Просто программно ступить посредством каждого Ответа и видеть, включено ли это в лучшие 10 для своего Вопроса, но я хотел бы оптимизировать это так, я не делаю запросов N+1, где N является количеством Ответов, Пользователь отправил.
4 ответа
Если вы используете Oracle, Microsoft SQL Server, DB2 или PostgreSQL, эти базы данных поддерживают оконные функции. Присоединяйтесь к ответам пользователя на другие ответы на тот же вопрос. Затем разделите по вопросам и по убыванию очков. Используйте номер строки в каждом разделе, чтобы ограничить набор теми, которые находятся в верхних 10. Также передайте user_id данного пользователя, чтобы вы могли выбрать их из первых 10, поскольку вас интересуют только ответы данного пользователя.
SELECT *
FROM (
SELECT r1.user_id AS given_user, r2.*,
ROW_NUMBER() OVER (PARTITION BY r2.question_id ORDER BY r2.total_score DESC) AS rownum
FROM Responses r1 JOIN Responses r2 ON r1.question_id = r2.question_id
WHERE r1.user_id = ?
) t
WHERE rownum <= 10 AND user_id = given_user;
Однако, если вы используете MySQL, SQLite или другие базы данных, которые не поддерживают оконные функции, вы можете использовать это другое решение:
Запросить ответы пользователя и использовать соединение для сопоставления других ответов на соответствующие вопросы с большим количеством очков (или более ранним ПК в случае ничьей). Сгруппируйте по вопросам и подсчитайте количество ответов, которые имеют более высокий балл. Если количество меньше 10, то ответ пользователя входит в топ-10 на вопрос.
SELECT r1.*
FROM Responses r1
LEFT OUTER JOIN Responses r2 ON r1.question_id = r2.question_id
AND (r1.total_score < r2.total_score
OR r1.total_score = r2.total_score AND r1.response_id > r2.response_id)
WHERE r1.user_id = ?
GROUP BY r1.question_id
HAVING COUNT(*) < 10;
Попробуйте встроенную инструкцию select. Сегодня у меня нет доступа к инструменту БД, поэтому я не могу подтвердить синтаксис / вывод. Просто внесите соответствующие изменения, чтобы захватить все нужные столбцы. Вы также можете добавить вопросы к основному запросу и объединить ответы.
select *
from users
, responses
where users.user_id=responses.user_id
and responses.response_id in (SELECT z.response_id
FROM Responses z
WHERE z.user_id = users.user_id
ORDER BY total_score DESC
LIMIT 10)
Или вы можете действительно оптимизировать его, добавив другое поле, например «IsTopPost». Вам нужно будет обновить самые популярные сообщения, когда кто-то проголосует, но ваш запрос будет простым:
SELECT * FROM Responses WHERE user_id=? and IsTopPost = 1
Думаю, что-то вроде этого должно помочь:
SELECT
user_id, question_id, response_id
FROM
Responses AS r1
WHERE
user_id = ?
AND
response_id IN (SELECT response_id
FROM Responses AS r2
WHERE r2.question_id = r1.question_id
ORDER BY total_score DESC LIMIT 10)
Фактически, для каждого question_id выполняется подзапрос, который определяет 10 самых популярных ответов для этого question_id.
Вы можете рассмотреть возможность добавления столбца, в котором определенные ответы отмечены как «победители». Таким образом, вы можете просто выбрать эти строки и избавить базу данных от необходимости снова и снова вычислять первые 10.