我尝试了多个小时以找出对此的查询，但是我没有运气。我什至不知道只有一个查询是否可行。
我有这样的桌子
id - server_id - players_online - performance - timestamp
该表每5-10分钟为每台服务器包含大约一条记录。问题是，我想获取平均性能和每20分钟间隔内的players_online的总和，但是由于同一台服务器在这20分钟内可以出现多次，因此可能会破坏我想要的最终结果：
使用ROUND（timestamp /（20 * 60）），我可以轻松地按20分钟的间隔进行分组，但是如何进行。您将如何编写查询？
我到目前为止尝试过的查询：
SELECT avg(performance) as performance, sum(playersOnline) as playersOnline, timestamp
FROM stats_server
GROUP BY ROUND(timestamp/(1200))
范例资料：http : //www.mediafire.com/download/z629q3g38qhr46h/stats_server.sql.gz
结果（这次服务器的平均值/总和）：
timestamp | performance | online players
1404757200000 | 93 | 125
1404758400000 | 92 | 120
1404759600000 | 96 | 133
1404759800000 | 93 | 168
1404751000000 | 88 | 122
1404751200000 | 94 | 134
解：
SELECT min20 * 1200 AS timestamp, AVG( performance ) AS performance, SUM( players ) AS playersOnline
FROM (
SELECT serverID, FLOOR( UNIX_TIMESTAMP( timestamp ) / 1200 ) AS min20, AVG( performance ) AS performance, AVG( playersOnline ) AS players
FROM stats_server
GROUP BY min20, serverID
) tmp
GROUP BY min20
ORDER BY timestamp
SELECT min20 * 1200 AS timestamp, AVG( performance ) AS performance, SUM( players ) AS playersOnline FROM ( SELECT serverID, FLOOR( UNIX_TIMESTAMP( timestamp ) / 1200 ) AS min20, AVG( performance ) AS performance, AVG( playersOnline ) AS players FROM stats_server GROUP BY min20, serverID ) tmp GROUP BY min20 ORDER BY timestamp
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句