MySQL - выберите рейтинг для пользователей в таблице результатов

У меня есть таблица user_score с такой структурой:

|id|user_id|group_id|score|     timestamp      |
| 1|      1|       1|  500| 2013-02-24 18:00:00|
| 2|      2|       1|  200| 2013-02-24 18:01:50|
| 3|      1|       2|  100| 2013-02-24 18:06:00|
| 4|      1|       1| 6000| 2013-02-24 18:07:30|

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

Что у меня есть (редактировать: после комментария Джокачина я обнаружил, что мой собственный запрос работает не так, как я ожидал, извините за все):

SELECT user_id, score, @curRank := @curRank + 1 AS rank
FROM (
    SELECT * 
    FROM (
           SELECT * FROM `user_score`
           WHERE `group_id` = 1
           ORDER BY `timestamp` DESC
    ) AS sub2
    GROUP BY `user_id`
) AS sub, (SELECT @curRank := 0) r
ORDER BY `rank`

Ожидаемый результат для примера data и group_id = 1:

|user_id|score|rank|
|      1| 6000|   1|
|      2|  200|   2|

Но подзапросы MySQL немного проблематичны, вы видите какое-нибудь другое решение, пожалуйста?

Мне, вероятно, нужно будет получить ранг одиночного пользователя в группе позже. Я потерян в данный момент.


mysql subquery ranking
person JCZ    schedule 24.02.2013    source источник
comment
Подзапросы в списке SELECT вызывают проблемы. С подзапросом в предложении FROM дело обстоит гораздо реже. Однако я не вижу смысла снова оборачивать самый внутренний запрос, чтобы выполнить GROUP BY.   -  person Michael Berkowski    schedule 24.02.2013
comment
Какой результат вы ожидаете от выборки данных? Что вы имеете в виду под рангом?   -  person jurgenreza    schedule 24.02.2013
comment
@Майкл Берковски: Спасибо! Самый внутренний запрос сортирует его по отметке времени, потому что я думаю, что только тогда я смогу сгруппировать их по user_id, чтобы получить последнюю оценку. Но может я и не прав (буду рад, если нет)   -  person JCZ    schedule 24.02.2013
comment
@jurgenreza: только что добавил ожидаемые данные. Спасибо.   -  person JCZ    schedule 24.02.2013
comment
@JCZ Если вместо этого вы измените оценку с идентификатором № 2 на 7200, она будет занимать второе место после 6000 в вашем запросе. Я неправильно понял, что ранжирование было по убыванию, или это непреднамеренно?   -  person Joachim Isaksson    schedule 24.02.2013
comment
@Joachim Isaksson: Спасибо, Иоахим, так что мой собственный запрос тоже не работает :( Я хотел выбрать последнюю (в соответствии с временной меткой) оценку для всех пользователей в группе, отсортированную по оценке, где наивысшая оценка занимает первое место в рейтинге.   -  person JCZ    schedule 24.02.2013

Ответы (3)


arrow_upward
5
arrow_downward

Хотя я не уверен, что означает «проблемный» в этом контексте, вот запрос, переписанный как простой LEFT JOIN с подзапросом только для того, чтобы получить ранжирование прямо в конце (ORDER BY нужно сделать до ранжирования);

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

SQLfiddle для тестирования.

РЕДАКТИРОВАТЬ: Чтобы принять во внимание group_id, вам нужно несколько расширить запрос;

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.group_id = u2.group_id       -- u and u2 have the same group
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
    AND u.group_id = 1                -- ...and that group is group 1
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

Еще один SQLfiddle.

person Joachim Isaksson    schedule 24.02.2013
comment
Спасибо большое за вашу помощь. Любая идея, как запросить ранг для точного user_id, пожалуйста? - person JCZ; 25.02.2013
comment
Извините, но я обнаружил, что не могу отфильтровать точный group_id? Я попытался добавить правило u.group_id WHERE, но оно не сработало. - person JCZ; 25.02.2013
comment
И, судя по всем комментариям здесь, я вижу, что я, вероятно, испортил проблемы с подвыборкой в ​​предложении WHERE с моей целью. - person JCZ; 25.02.2013
comment
@JCZ Обновил ответ с помощью группового фильтра. - person Joachim Isaksson; 25.02.2013
comment
Спасибо! Я забыл добавить, что у u2 такое же групповое правило. - person JCZ; 25.02.2013

arrow_upward
1
arrow_downward

Я знаю, что ответ был опубликован и принят, но у меня есть мнение о вашем исходном запросе, о котором, я думаю, стоит упомянуть.

При группировке вам разрешено выбирать только те столбцы, по которым вы группируете, и столбцы с агрегатными функциями, такими как MAX и COUNT. Выбор других столбцов технически неверен.

Это имеет смысл, так как неясно, данные какой строки должны быть возвращены. Mysql возвращает данные первой строки в группе; вот почему ваш запрос работает (из-за порядка по отметке времени во внутреннем подзапросе). С другой стороны, SQL-сервер вызывает исключение.

Я думаю, что подход SQL Server правильный, и такого типа запросов следует избегать.

person jurgenreza    schedule 24.02.2013

arrow_upward
0
arrow_downward

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

SELECT user_score.user_id, 
       MAX(user_score.score) AS score
FROM user_score 
WHERE user_score.group_id = 1 
GROUP BY user_score.user_id, 
         user_score.group_id
ORDER BY user_score.score DESC, 
         user_score.`timestamp` ASC
person zudtz    schedule 29.05.2018