mysql - 找出 24 小时内的最大值

Question

所以我有一个 MySQL 数据库，它按小时计算具有给定使用级别的服务器数量，如下所示:

Date                Status  Population
2012-01-13 15:33:40 UP      Standard
2012-01-13 15:33:40 UP      Light
2012-01-13 15:33:40 UP      Heavy
2012-01-13 15:33:40 UP      Light

2012-01-13 16:33:40 UP      Light
2012-01-13 16:33:40 UP      Very Heavy
2012-01-13 16:33:40 UP      Light

等等

和一个查询，该查询计算按分钟分组的给定使用级别的服务器数量，因此

SELECT DATE, ROUND( UNIX_TIMESTAMP( DATE ) / ( 1*60 ) ) AS TIME, COUNT( IF( POPULATION =  "Light", 1, NULL ) ) AS LightCount, COUNT( IF( POPULATION =  "Standard", 1, NULL ) ) AS StandardCount, COUNT( IF( POPULATION =  "Heavy", 1, NULL ) ) AS HeavyCount, COUNT( IF( POPULATION =  "Very Heavy", 1, NULL ) ) AS VeryHeavyCount, COUNT( IF( POPULATION =  "Full", 1, NULL ) ) AS FullCount, COUNT( IF( POPULATION =  "Offline", 1, NULL ) ) AS OfflineCount
FROM  `Servers` 
GROUP BY TIME
ORDER BY DATE ASC

输出如下:

DATE            TIME            LightCount  StandardCount   
2012-01-13 15:33:40 22108174    16          146     
2012-01-13 16:33:35 22108180    16          147 

依此类推，每小时运行一次。

我正在尝试找到一种方法来确定每 24 小时内“LightCount”或“StandardCount”等的最大数量。换句话说，任何一天的最高每小时 LightCount 是多少？

这有可能吗？它需要某种嵌套查询吗？

非常感谢您的帮助。

Answer 1

首先，您可以使用 GROUP BY DATE(DATE),HOUR(DATE) 计算每小时总和:

SELECT `DATE`, COUNT(...), ... -- HOUR(`DATE`) if you wanted to see it
FROM `Servers`
GROUP BY DATE(`DATE`),HOUR(`DATE`)
ORDER BY `DATE` ASC

然后每天做最大小时计数

SELECT DATE(`DATE`) as Day, MAX(LightCount) as MaxHourlyLightCount, ...
FROM (
    SELECT `DATE`, ....
    FROM `Servers`
    GROUP BY DATE(`DATE`),HOUR(`DATE`)
    ORDER BY `DATE` ASC
     ) HourlyCounts
GROUP BY Day

Here's MySQL 日期/时间函数的链接——您可以使用 GROUP BY MONTH(``Date``), YEAR(``Date``) 进行每月统计，使用 进行年度统计GROUP BY YEAR(``Date``) 等 - 非常方便。

(注意:将上面的那些双反引号替换为单反引号——我不知道如何在 stackoverflow 中正确地转义它们)。