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

#php #mysql #sql

Вопрос:

Я пытаюсь получить игру … и максимальный балл для пользователя, и информация о том, есть ли более высокое / низкое значение (оценка других игроков), чем максимальное значение пользователя. Я объясню ситуацию.

Пользователь должен получить свои игры и максимальный балл за каждую игру, В которой игра активна. Это отлично работает с JOIN и GROUP BY , но я понятия не имею, как получить информацию, есть ли больше баллов и выше или ниже, чем максимальный балл пользователя.

Таблица 1: scores

  ---- -------- ------ -------- ------- 
| id | gameID | game | player | score |
 ---- -------- ------ -------- ------- 
|  1 |      1 | pang | lea    |    50 |
|  2 |      1 | pang | lea    |    60 |
|  3 |      1 | pang | paola  |    70 |
|  4 |      2 | pong | lea    |   100 |
|  5 |      2 | pong | paola  |    90 |
 ---- -------- ------ -------- -------

Таблица 2: games

  ---- ------ -------- 
| id | name | active |
 ---- ------ -------- 
|  1 | pang | yes    |
|  2 | pong | yes    |
|  3 | pung | yes    |
 ---- ------ --------

Код:

 $loggedUser = 'lea';
$sql = 
    "
SELECT s.gameID
     , s.game
     , COUNT(s.id) c
     , MAX(s.score) Max
  FROM scores s
 RIGHT 
  JOIN games g
    ON s.gameID = g.id
 WHERE g.active = 'yes' 
   AND s.player = '$loggedUser'
 GROUP 
    BY s.gameID 
 ORDER 
    BY c
";

Вывод должен выглядеть следующим образом:

Привет, Леа, твои лучшие результаты:

pang сыграно 2 раза, максимальный балл 60 очков (максимальный балл 70)….

в понг сыграно 1 раз, 100 очков, у вас более высокий балл….

1. Не совсем то, что вы хотите, но у вас могло бы быть представление, в котором хранился бы список всех игр и 2 лучших результата для каждой. В этом случае пользователю будет отображен либо наивысший балл, либо его оценка является наивысшей, и вы можете отобразить 2-й балл как следующий лучший. При этом вам просто нужно будет внутренне присоединиться к представлению.

2. Какую версию MySQL вы используете?

Ответ №1:

В MySQL 8.0 вы можете использовать оконные функции для:

оцените результаты игрока за каждую игру: ROW_NUMBER() OVER(PARTITION BY player, game ORDER BY score DESC)
подсчитайте, сколько раз игрок сыграл в каждую игру: COUNT(*) OVER(PARTITION BY player, game)
вычислите максимальный результат в данной игре среди всех игроков : MAX(score) OVER(PARTITION BY game ORDER BY score DESC)

Рассмотрим следующий запрос:

 SELECT game_name, cnt games_played, score max_score, top_score
FROM (
    SELECT
        g.name game_name,
        s.player,
        s.score,
        ROW_NUMBER() OVER(PARTITION BY player, game ORDER BY score DESC) rn,
        COUNT(*) OVER(PARTITION BY player, game) cnt,
        MAX(score) OVER(PARTITION BY game ORDER BY score DESC) top_score
    FROM scores s
    INNER JOIN games g ON g.id = s.gameID AND g.active = 'yes'
) x WHERE rn = 1 AND player = 'lea';

В этом скрипте db с вашими образцами данных запрос выдает:

 | game_name | games_played | max_score | top_score |
| --------- | ------------ | --------- | --------- |
| pang      | 2            | 60        | 70        |
| pong      | 1            | 100       | 100       |

В более ранних версиях MySQL одним из решений было бы использовать агрегированный подзапрос для вычисления наивысшего балла за каждую игру среди всех игроков, и JOIN его с основным агрегированным запросом:

 SELECT g.name game_name, COUNT(*) games_played, MAX(s.score) max_score, ts.top_score
FROM  scores s
INNER JOIN games g 
    ON g.id = s.gameID AND g.active = 'yes'
INNER JOIN (SELECT gameID, MAX(score) top_score FROM scores GROUP BY gameID) ts 
    ON s.gameID = ts.gameID
WHERE s.player = 'lea'
GROUP BY s.player, g.id, g.name;

Демонстрация на скрипке DB.