Простой рейтинг обновления MySQL со связями

Я пытаюсь сохранить ранг пользователей на основе оценки, все это одна таблица, и пропускаю ранги, когда есть ничья. Например:

ID   Score Rank
2    23    1
4    17    2
1    17    2
5    10    4
3    2     5

Каждый раз, когда обновляется оценка пользователя, также должен обновляться рейтинг для всей таблицы, поэтому после обновления оценки выполняется следующий запрос:

SET @rank=0;
UPDATE users SET rank= @rank:= (@rank+1) ORDER BY score DESC;

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

Я хочу добиться этого повторного ранжирования с помощью как можно меньшего количества запросов и без объединений (поскольку они кажутся довольно трудоемкими).

Я смог получить желаемый результат, добавив два столбца — last_score и tie_build_up — со следующим кодом:

SET @rank=0, @last_score = null, @tie_build_up = 0;
UPDATE users SET
    rank= @rank:= if(@last_score = score, @rank, @rank+@tie_build_up+1),
    tie_build_up= @tie_build_up:= if(@last_score = score, @tie_build_up+1, 0),
    last_score= @last_score:= score, ORDER BY score DESC;

Мне не нужны эти дополнительные столбцы, но я не мог заставить работать один запрос без них.

Любые идеи?

Спасибо.


mysql ranking
person Kyle    schedule 30.12.2011    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вот альтернативное решение: вообще не хранить ранги! :-)

Вы можете вычислить их на лету.

Пример:

SELECT id, (@next_rank := IF(@score <> score, 1, 0)) nr, 
           (@score := score) score, (@r := IF(@next_rank = 1, @r + 1, @r)) rank 
FROM rank, (SELECT @r := 0) dummy1
ORDER BY score DESC;

Результат:

  +------+----+-------+------+
  | id   | nr | score | rank |
  +------+----+-------+------+
  |    2 |  1 |    23 |    1 |
  |    4 |  1 |    17 |    2 |
  |    1 |  0 |    17 |    2 |
  |    5 |  1 |    10 |    3 |
  |    3 |  1 |     2 |    4 |
  +------+----+-------+------+

nr здесь находится вспомогательный столбец, который указывает, следует ли нам присваивать следующий ранг или нет.

Вы можете обернуть этот запрос в другой select и выполнить пейджинг, например.

SELECT id, score, rank 
FROM (SELECT id, (@next_rank := IF(@score <> score, 1, 0)) nr, 
           (@score := score) score, (@r := IF(@next_rank = 1, @r + 1, @r)) rank
      FROM rank, (SELECT @r := 0) dummy1
      ORDER BY score DESC) t
      WHERE rank > 1 and rank < 3;

Результат:

  +------+-------+------+
  | id   | score | rank |
  +------+-------+------+
  |    4 |    17 |    2 |
  |    1 |    17 |    2 |
  +------+-------+------+

ВНИМАНИЕ: поскольку теперь rank является вычисляемым столбцом, вы не можете индексировать его и эффективно пролистывать набор данных (то есть "выбирать записи с рангами от 3000 до 3010"). Но это все еще хорошо для «выбрать первые N рангов» (при условии, что вы поместите соответствующий LIMIT в запрос)

person Sergio Tulentsev    schedule 30.12.2011
comment
+1, я бы так и сделал. Не кажется правильным хранить вычисляемый столбец, если в этом нет крайней необходимости. Производительность при вычислении столбца, вероятно, лучше, чем при сохранении, поскольку для записи не требуются блокировки. - person Andrew Dunkman; 31.12.2011

arrow_upward
0
arrow_downward

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

person Assaf Karmon    schedule 30.12.2011

arrow_upward
-1
arrow_downward

я рассчитал ранг и позицию следующим образом:

чтобы обновить И получить нужные мне значения, я сначала добавил их, добавил еще 1 и вычел исходное значение. таким образом мне не нужны никакие вспомогательные столбцы внутри таблицы;

SET @rank=0;
SET @position=0;
SET @last_points=null;

UPDATE tip_invitation 
set 
    rank = @rank:=if(@last_points = points, @rank, @rank + 1),
    position = ((@last_points := points)-points) + (@position := @position+1)
where
    tippgemeinschaft_id = 1 ORDER BY points DESC;
person Bruno Jennrich    schedule 31.05.2014