Определить рейтинг на основе нескольких столбцов в MySQL




sql-rank (3)

У меня есть таблица, которая имеет 3 поля, я хочу ранжировать столбцы на основе user_id и game_id.

Вот SQL Fiddle: http://sqlfiddle.com/#!9/883e9d/1

таблица уже у меня есть:

 user_id | game_id |   game_detial_sum  |
 --------|---------|--------------------|
 6       | 10      |  1000              |   
 6       | 11      |  260               |
 7       | 10      |  1200              |
 7       | 11      |  500               |
 7       | 12      |  360               |
 7       | 13      |  50                | 

ожидаемый результат:

user_id  | game_id |   game_detial_sum  |  user_game_rank  |
 --------|---------|--------------------|------------------|
 6       | 10      |  1000              |   1              |
 6       | 11      |  260               |   2              |
 7       | 10      |  1200              |   1              |
 7       | 11      |  500               |   2              |
 7       | 12      |  360               |   3              |
 7       | 13      |  50                |   4              |

Мои усилия до сих пор:

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:[email protected]s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

Изменить: (из Comments OP): порядок основан на порядке убывания game_detail

порядок игры


В производной таблице (подзапрос внутри предложения FROM ) мы упорядочиваем наши данные таким образом, чтобы все строки, имеющие одинаковые значения user_id объединялись вместе с дальнейшей сортировкой между ними на основе game_detail в порядке убывания.

Теперь мы используем этот набор результатов и используем условные выражения CASE..WHEN для оценки нумерации строк. Это будет похоже на метод Looping (который мы используем в коде приложения, например: PHP). Мы сохраняем значения предыдущей строки в пользовательских переменных, а затем проверяем значения текущей строки по сравнению с предыдущей строкой. В конце концов, мы назначим номер строки соответственно.

Изменить: на основе docs MySQL и наблюдения @Gordon Linoff:

Порядок вычисления для выражений с участием пользовательских переменных не определен. Например, нет гарантии, что SELECT @a, @a: = @ a + 1 сначала вычислит @a, а затем выполнит присваивание.

Нам нужно будет оценить номер строки и присвоить значение @u переменной @u в том же выражении.

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt 

Результат

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

Посмотреть на БД Fiddle

Интересная заметка из MySQL docs , которую я обнаружил недавно:

Предыдущие выпуски MySQL позволяли присваивать значение пользовательской переменной в операторах, отличных от SET. Эта функциональность поддерживается в MySQL 8.0 для обратной совместимости, но подлежит удалению в будущем выпуске MySQL.

Кроме того, благодаря товарищу по SO, который наткнулся на этот блог командой MySQL: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

Общее наблюдение состоит в том, что использование ORDER BY с оценкой пользовательских переменных в одном и том же блоке запроса не гарантирует, что значения всегда будут правильными. Так как оптимизатор MySQL может появиться на месте и изменить наш предполагаемый порядок оценки.

Лучший подход к этой проблеме - обновить MySQL до 8+ и использовать функциональность Row_Number() :

Схема (MySQL v8.0)

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

Результат

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

Посмотреть на БД Fiddle


Вы можете использовать очень простой коррелированный подзапрос:

SELECT *, (
    SELECT COUNT(DISTINCT game_detail) + 1
    FROM game_logs AS x
    WHERE user_id = t.user_id AND game_detail > t.game_detail
) AS user_game_rank
FROM game_logs AS t
ORDER BY user_id, user_game_rank

DB Fiddle

Это медленнее, но гораздо надежнее, чем пользовательские переменные. Все, что нужно, это один JOIN, чтобы сломать их.


SELECT user_id, game_id, game_detail, 
       CASE WHEN user_id = @lastUserId 
            THEN @rank := @rank + 1 
            ELSE @rank := 1 
       END As user_game_rank,
       @lastUserId := user_id
FROM game_logs
cross join (select @rank := 0, @lastUserId := 0) r
order by user_id, game_detail desc

SQLFiddle Demo







sql-rank