Обновление таблицы MySQL с ранжированием записей в группах

Question

У меня есть таблица под названием "победители" с колонками "id", "category", "score", "rank".

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

Большинство найденных мной ответов основаны на утверждениях select, которые просто имеют тенденцию выводить только табличное представление, но я нашел очень хороший ответ "Update" ( /questions/19431712/obnovite-rejting-v-tablitse-mysql/19431722#19431722), в частности обновление ответа где связи должны иметь одинаковый ранг.

Образец

CREATE TABLE winners (
id int,
category_id int,
score double,
rank int
);

INSERT INTO winners VALUES 
(1, 1, 4.36, NULL),
(2, 1, 2.35, NULL),
(3, 1, 1.25, NULL),
(4, 2, 4.21, NULL),
(5, 2, 3.33, NULL),
(6, 1, 4.24, NULL),
(7, 1, 1.22, NULL),
(8, 1, 1.25, NULL),
(9, 2, 4.21, NULL),
(10, 2, 3.63, NULL);

+----------+-------------+-------+------+
|       id | category_id | score | rank |
+----------+-------------+-------+------+
|        1 |           1 |  4.36 |      |
|        2 |           1 |  2.35 |      |
|        3 |           1 |  1.25 |      |
|        4 |           2 |  4.21 |      |
|        5 |           2 |  3.33 |      |
|        6 |           1 |  4.24 |      |
|        7 |           1 |  1.22 |      |
|        8 |           1 |  1.25 |      |
|        9 |           2 |  4.21 |      |
|       10 |           2 |  3.63 |      |
+----------+-------------+-------+------+

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

Я попытался добавить предложение where в код (строка 8)

1. UPDATE   winners
2. JOIN     (SELECT  w.score,
3.                IF(@lastPoint <> w.score, 
4.                   @curRank := @curRank + 1, 
5.                   @curRank)  AS rank,
6.                @lastPoint := w.rating
7.      FROM      winners w
8.      WHERE category_id = 1
9.      JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
10.     ORDER BY  w.score DESC
11.     ) ranks ON (ranks.score = winners.score)
12. SET winners.rank = ranks.rank;

с целью попытки запустить код дважды для каждого category_id, но скрипт завершается ошибкой.

Любые варианты изменения ответа выше для нескольких категорий будут фантастическими.

Нужно просто уточнить результат (ранжирование по категориям).

+----------+-------------+-------+------+
|       id | category_id | score | rank |
+----------+-------------+-------+------+
|        1 |           1 |  4.36 |    1 |
|        6 |           1 |  4.24 |    2 |
|        2 |           1 |  2.35 |    3 |
|        8 |           1 |  1.25 |    4 |
|        3 |           1 |  1.25 |    4 |
|        7 |           1 |  1.22 |    5 |
|        4 |           2 |  4.21 |    1 |
|        9 |           2 |  4.21 |    1 |
|       10 |           2 |  3.63 |    2 |
|        5 |           2 |  3.33 |    3 |
+----------+-------------+-------+------+

Спасибо, парни!

ОБНОВИТЬ

Мне удалось найти еще один фрагмент кода /questions/3054906/obnovlenie-mysql-dlya-sohraneniya-pozitsij-rejtinga/3054917#3054917 который я каким-то образом изначально пропустил и смог изменить его с помощью предложения where для каждого category_id. Не идеальный способ сделать это - запустить несколько раз для нескольких категорий, но на данный момент это нормально.

set @currentRank = 0,
@lastRating = null,
@rowNumber = 1;
update winners r
inner join (
    select
        score,
        @currentRank := if(@lastRating = score, @currentRank, @rowNumber) rank,
        @rowNumber := @rowNumber + if(@lastRating = score, 0, 1) rowNumber,
        @lastRating := score
    from winners
    where category_id = 1
    order by score desc
) var on var.score = r.score
set r.rank = var.rank

дальнейшие ответы на более "автоматическую" обработку нескольких категорий в рамках ранжирования за 1 сценарий по-прежнему приветствуются и приветствуются.

Спасибо

ОБНОВИТЬ

Просто заметил, что ответ, который я нашел, не очень хорошо справляется с нулевыми баллами (0,00) и ставит их в середину других баллов.

Ответ shawnt00 ниже работает и правильно оценивает ноль баллов. /questions/9832386/obnovlenie-tablitsyi-mysql-s-ranzhirovaniem-zapisej-v-gruppah/9832398#9832398

Answer 1

update winners
set rank = (
    select count(score) + 1
    from winners w2
    where w2.category_id = winners.category_id and w2.score > winners.score
)

count(*) будет равен нулю, даже если ни одна строка не соответствует условию, т. е. верхний рейтинг. Если вам нужен плотный ранг, вы можете сделать это:

update winners
set rank = (
    select count(distinct score) + 1
    from winners w2
    where w2.category_id = winners.category_id and w2.score > winners.score
)

РЕДАКТИРОВАТЬ: По вашему комментарию я нашел запрос, который работает. (Он работает на SQL Server, и я не знаком с особенностями MySQL.) http://sqlfiddle.com/

update winners
inner join (
    select w.id, w.category_id, count(w2.score) + 1 rank
    from winners w left outer join winners w2
        on w2.category_id = w.category_id and w2.score > w.score
    group by w.id
) r
    on r.id = winners.id
set winners.rank = r.rank