Выбор максимального значения за период и timestamp этого значения. Ошибка sql_mode=only_full_group_by?

Question

[Кирилл Красин]

Здравствуйте. БД mysql 8.3.0. Структура:

id - int [AI]
player_count - int
timestamp - timestamp [CURRENT_TIMESTAMP, on update]

Каждые 30 минут добавляется новая строка с текущим значением player_count и timestamp соответственно.

Задача делать выборку с максимальным значением за период, например за месяц, и получать не только значение player_count, но и timestamp этого значения.

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

SELECT MAX(player_count) AS players_max, `timestamp` FROM steam_stats WHERE MONTH(`timestamp`) = 7 AND YEAR(`timestamp`) = 2024

Делая запрос выше получаю ошибку:

1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'db.steam_stats.timestamp'; this is incompatible with sql_mode=only_full_group_by

Если убрать `timestamp` из SELECT, получаю нужное значение, но соответственно не знаю его timestamp.

Как правильно сформировать SELECT и GROUP BY чтобы не отключать режим sql_mode=only_full_group_by?

Answer 1

[Akina]

Ну типа так:

SELECT DISTINCT
       MAX(player_count) OVER () AS players_max, 
       FIRST_VALUE(`timestamp`) OVER (ORDER BY player_count DESC) AS players_max_timestamp
FROM steam_stats 
WHERE `timestamp` >= '2024-07-01' AND `timestamp` < '2024-08-01'

Если максимальное значение player_count встречается более чем один раз, то будет выведен случайный timestamp из возможных. Если нужен определённый (например, самый последний из них), расширьте выражение сортировки в определении окна.

Comments

[Кирилл Красин]

Благодарю!

Answer 2

[Rsa97]

SELECT player_count, `timestamp`
  FROM steam_stats
  WHERE `timestamp` >= TIMESTAMP('2024-07-01')
    AND `timestamp` < TIMESTAMP('2024-08-01')
  ORDER BY player_count DESC
  LIMIT 1