mysql 按小时计算的组平均玩家计数

lymgl2op 于 2023-11-16 发布在 Mysql

关注(0)|答案(2)|浏览(110)

我想收集一天中每个小时的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}]

字符串
我不确定如何构造MySQL查询。我尝试了一些沿着的东西

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;");

型
但似乎不起作用谁能给我指个方向。

mysql

来源：https://stackoverflow.com/questions/77467273/group-average-player-count-by-hour

2条答案

按热度按时间

slsn1g291#

不如这样吧：

SELECT 
  DATE(`datetime`) AS `date`,
  HOUR(`datetime`) AS `hour`,
  AVG(`player-count`) AS `average` 
FROM `server_player_count` 
GROUP BY `date`, `hour` 
ORDER BY `date` ASC, `hour` ASC;

字符串
演示：http：//sqlfiddle.com/#！9/99 d103/1
这看起来很像你的建议，那么出了什么问题？你为什么要问这个问题？

赞(0）回复(0）举报 2023-11-16

5f0d552i2#

select *,avg(player_count) from table group by hour,date

字符串
所以这里你没有添加其余的列在group by condition中，因为你在select语句中提到了 *。所以这是抛出错误（这是我的假设）
下面是一个查询，它将工作，以获得播放器_计数天和小时明智的

**SELECT 
  AVG([player-count]) AS average,DATEPART(Hour, [datetime]) as hour,CAST([datetime] as DATE) as date from [server_player_count]
  GROUP BY CAST([datetime] as DATE), DATEPART(Hour, [datetime]) ORDER BY CAST([datetime] as DATE) ASC;**

型

赞(0）回复(0）举报 2023-11-16

我来回答

mysql 按小时计算的组平均玩家计数

2条答案

相关问题

热门标签

最新问答