表結構：
|
1
2
3
4
5
6
|
CREATE TABLE `instance` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `timestamp` int(10) unsigned DEFAULT NULL, `cpu` decimal(8,3) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; |
統計每半小時平均值，實現可以如下：
|
1
2
3
|
select `timestamp`-`timestamp`% (5*60) , avg(cup)from `instance`group by `timestamp`-`timestamp`% (5*60) |
另外還有：
|
1
2
3
4
5
|
SELECT queueName, HOUR(dt) AS h, FLOOR(MINUTE(dt) / 30) AS v , avg(memoryUsed), sum(memoryUsed), count(memoryUsed) from queue_resource where dt>='2014-04-14 00:00:00'GROUP BY h, v, queueName; |
