查找 24 小时内的最大值

发布于 2024-12-28 08:58:17 字数 1304 浏览 0 评论 0原文

所以我有一个 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 是多少？

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

非常感谢您的帮助。

原文

So I've got a MySQL database that counts the number of servers with given usage levels on an hourly basis, as such:

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

etc.

and a query that counts the number of servers with given usage levels grouped by minute, thusly

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

The output looks like:

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

and so on, with running counts for every hour.

I'm trying to find a way to determine the highest number of "LightCount" or "StandardCount" etc. within each 24 hour period. In other words, what was the highest hourly LightCount for any given day?

Is this at all possible? Would it require a nested query of some sort?

Thanks much for any help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

莫相离 2025-01-04 08:58:17

首先，您可以使用 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

这里一个链接MySQL日期/时间函数——您可以使用 GROUP BY MONTH(``Date``), YEAR(``Date``) 进行每月统计，使用 GROUP BY YEAR(` `Date``) 等 - 非常方便。

（注意：将上面的双反引号替换为单反引号——我不知道如何在 stackoverflow 中正确转义它们）。

First, you can use GROUP BY DATE(DATE),HOUR(DATE) for hourly sums:

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

Then to do maximum hourly counts per day you'd do

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 a link to the MySQL Date/Time functions -- you can do monthly stats with GROUP BY MONTH(``Date``), YEAR(``Date``), yearly stats with GROUP BY YEAR(``Date``), etc - very handy.

(Note: replace those double-backticks above with single-backticks -- I don't know how to escape them properly in stackoverflow).

回复收藏 0 原文