#mysql #sorting #join #sql-update #window-functions
Вопрос:
Допустим, у меня есть следующая таблица, рейтинги,
|film_id|user_id|rank|
|-------|-------|----|
|1 |1 |2 |
|2 |1 |3 |
|3 |1 |1 |
|1 |2 |2 |
|2 |2 |1 |
|3 |2 |3 |
и я удаляю идентификатор фильма 2 .
Есть ли способ MySQL повторно выполнить просмотр оставшихся фильмов для каждого пользователя в одном запросе, например:
|film_id|user_id|rank|
|-------|-------|----|
|1 |1 |2 |
|3 |1 |1 |
|1 |2 |1 | <- changed from 2 to 1
|3 |2 |2 | <- changed from 3 to 2
Отредактируйте для ясности: я не уверен, что назвал бы это формулой, но все, что я хочу сделать, это создать запрос, который загружает оставшиеся фильмы в порядке их ранга, а затем перестраивает рейтинг в том же порядке и последовательно.
Другими словами, если после удаления строки рейтинг пользователя вырос на 1,2,4,5,6, я хочу, чтобы он был восстановлен последовательно, начиная с 1 (1,2,3,4,5).
Спасибо!
Комментарии:
1. Очевидный способ-пересчитать рейтинги на той же основе, на которой они были рассчитаны ранее. Здесь нет ничего, что указывало бы на то, как это было сделано.
2. @TangentiallyPerpendicular, извините, это не казалось особенно актуальным для вопроса, но я рад уточнить. Это не рассчитано само по себе. Он устанавливается пользователем. Логика приложения принимает список film_ids по порядку и обновляет рейтинг по мере необходимости. Это простой и недорогой способ установить порядок в случае сортировки пользователем. В случае обработки удаления я задался вопросом, возможно ли и быстрее обработать все детали в базе данных, поскольку вся необходимая информация уже находится в базе данных.
3. Если вы предоставите формулу для вычисления ранга, это вполне возможно сделать. На данный момент в вопросе отсутствует эта информация, поэтому ответить на него невозможно.
4. @TheImpaler, Может быть, я просто недооцениваю это, я не смотрю на формулу. Хотя я думаю, что так оно и есть. Я просто хочу удалить пробелы, оставшиеся после удаления строки, для каждого пользователя. Я обновил вопрос дополнительной информацией.
5. Вы хотите обновить таблицу новыми рейтингами или просто выбрать запрос? Кроме того, какова ваша версия MySQL?
Ответ №1:
Присоедините таблицу к запросу, который использует ROW_NUMBER() функцию окна для восстановления рейтинга:
UPDATE tablename t1
INNER JOIN (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY `rank`) rn
FROM tablename
) t2 ON t2.film_id = t1.film_id AND t2.user_id = t1.user_id
SET t1.`rank` = t2.rn
WHERE t1.`rank` <> t2.rn; -- actually this in not needed
-- as MySql does not perform the update
-- if the current and new values are the same
Смотрите демонстрацию.
Комментарии:
1. Это сработало великолепно! Спасибо. Мне нравятся подобные решения, которые позволяют обрабатывать данные там, где это наиболее эффективно, но я просто не мог этого понять. (Мне нужно провести некоторые исследования по этому
OVER (PARTITION BY...пункту, потому что я этого раньше не видел.)