按小时统计玩家平均在线人数的MySQL查询求助

需要统计一天中每个小时的player-count平均值，以此了解玩家的高峰在线时段。MySQL数据表结构示例如下：

id   |   datetime               |  player-count  | players
1    |   2023-11-10 23:10:05    |    32          | [{jsondata}]
1    |   2023-11-10 23:15:02    |    10          | [{jsondata}]
1    |   2023-11-10 23:20:05    |    25          | [{jsondata}]
1    |   2023-11-10 23:25:02    |    9           | [{jsondata}]
1    |   2023-11-10 23:30:01    |    3           | [{jsondata}]

尝试过以下查询语句但无法正常运行：

SELECT *,AVG(`player-count`) AS average from `".$db->prefix."server_player_count` GROUP BY CAST(`datetime` as DATE), DATEPART(Hour, StartDate) ORDER BY CAST(`datetime` as DATE) ASC;");

1. MySQL不支持DATEPART函数，提取小时需用HOUR()函数
2. 查询中引用了不存在的字段StartDate，实际目标字段为datetime
3. SELECT *与GROUP BY混用违反MySQL默认的ONLY_FULL_GROUP_BY规则，不能直接查询非聚合、非分组字段
4. 语句末尾存在多余的语法错误字符");

1. 按日期+小时分组，统计每日各时段平均在线人数 #

SELECT
    DATE(`datetime`) AS stat_date,
    HOUR(`datetime`) AS stat_hour,
    AVG(`player-count`) AS average_player_count
FROM
    `".$db->prefix."server_player_count`
GROUP BY
    stat_date, stat_hour
ORDER BY
    stat_date ASC, stat_hour ASC;

2. 统计指定日期的各小时平均在线人数 #

若仅需统计某一天（如2023-11-10）的数据，可添加过滤条件：

SELECT
    HOUR(`datetime`) AS stat_hour,
    AVG(`player-count`) AS average_player_count
FROM
    `".$db->prefix."server_player_count`
WHERE
    DATE(`datetime`) = '2023-11-10'
GROUP BY
    stat_hour
ORDER BY
    stat_hour ASC;

3. 合并所有日期的小时平均值（查看整体时段高峰） #

若想忽略日期，统计所有数据中每个小时的整体平均水平，可按小时单独分组并排序：

SELECT
    HOUR(`datetime`) AS stat_hour,
    AVG(`player-count`) AS average_player_count
FROM
    `".$db->prefix."server_player_count`
GROUP BY
    stat_hour
ORDER BY
    average_player_count DESC;

内容的提问来源于stack exchange，提问作者Dawson Irvine