mysql每半小时平均值计算的sql语句

数据运维 2023-04-25 大树 手机阅读

表结构： 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; 统计每半小时平均值，实现可以如

                        <p>表结构：</p>

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;

统计每半小时平均值，实现可以如下：

select timestamp-timestamp% (560) , avg(cup) from instance group by timestamp-timestamp% (560)

另外还有：

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;