求解，百万级数据时间分组查询的问题

打扰了 2018-12-18 10:55:08
就是要查昨天到今天当前时间一天的数据，并且时间五分钟分组，要查出每五分钟的平均值，该怎么查

一些字段属性是这样的，time加了索引
message_id | int(255) | NO | PRI | NULL | auto_increment |
| time | datetime | YES | MUL | NULL | |
| cpuUsageRate | varchar(255) | YES | | NULL | |

...全文
449 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-12-19
  • 打赏
  • 举报
 回复
24*60/5=288 1. 增加一个专用的表，存储 1 到 288 2. 你原来的表，增加一个计算列 seg ，将时间转化为 1 到 288 中的数值 3. 为 seg 增加索引 4. 你要查询的无非就是： 
SELECT 
	seg
	,AVG(CAST(cpuUsageRate AS  DECIMAL(10,3))) AS avgCpuUsageRate 
FROM `tableName`
WHERE `time`>='2019-12-18'
GROUP BY seg
大致这样， 你再细化下就可以了。
吉普赛的歌 2018-12-19
  • 打赏
  • 举报
 回复 
insert into `seg`(`timeSeg`,startTime,endTime)
SELECT 268,'22:15','22:20'
UNION ALL SELECT 269,'22:20','22:25'
UNION ALL SELECT 270,'22:25','22:30'
UNION ALL SELECT 271,'22:30','22:35'
UNION ALL SELECT 272,'22:35','22:40'
UNION ALL SELECT 273,'22:40','22:45'
UNION ALL SELECT 274,'22:45','22:50'
UNION ALL SELECT 275,'22:50','22:55'
UNION ALL SELECT 276,'22:55','23:00'
UNION ALL SELECT 277,'23:00','23:05'
UNION ALL SELECT 278,'23:05','23:10'
UNION ALL SELECT 279,'23:10','23:15'
UNION ALL SELECT 280,'23:15','23:20'
UNION ALL SELECT 281,'23:20','23:25'
UNION ALL SELECT 282,'23:25','23:30'
UNION ALL SELECT 283,'23:30','23:35'
UNION ALL SELECT 284,'23:35','23:40'
UNION ALL SELECT 285,'23:40','23:45'
UNION ALL SELECT 286,'23:45','23:50'
UNION ALL SELECT 287,'23:50','23:55'
UNION ALL SELECT 288,'23:55','00:00';
drop table if exists `t`;
CREATE table `t`(
	message_id int not null auto_increment primary key,
	`time` datetime null,
	`cpuUsageRate` varchar(255) null
);
insert into `t`(`time`,`cpuUsageRate`) values('2018-12-19 08:00','90');
insert into `t`(`time`,`cpuUsageRate`) values('2018-12-19 08:01','95');
insert into `t`(`time`,`cpuUsageRate`) values('2018-12-19 18:02','92');
# 以上为测试表及测试数据

# 1. 增加计算列
alter table `t` add `timeSeg` int as ( FLOOR( (DATE_FORMAT(`time`,'%H')*60+DATE_FORMAT(`time`,'%i'))/5 ) );

# 2. 增加索引
create index `ix_t_timeSeg` on t(`timeSeg`);

select date_format(t.`time`,'%Y-%m-%d') as `date`
    ,t.timeSeg
    ,seg.startTime
    ,seg.endTime 
		,AVG(CAST(cpuUsageRate AS  DECIMAL(10,3))) AS avgCpuUsageRate
		from t inner join seg on t.timeSeg=seg.timeSeg
group by 
    date_format(t.`time`,'%Y-%m-%d')
    ,t.timeSeg
    ,seg.startTime
    ,seg.endTime
吉普赛的歌 2018-12-19
  • 打赏
  • 举报
 回复
代码多了点， 分两贴： 
# 1. 增加辅助表
drop table if exists `seg`;
create table `seg`(
	`timeSeg` int primary key,
	startTime char(5),
	endTime char(5)
);
insert into `seg`(`timeSeg`,startTime,endTime)
          SELECT 1,'00:00','00:05'
UNION ALL SELECT 2,'00:05','00:10'
UNION ALL SELECT 3,'00:10','00:15'
UNION ALL SELECT 4,'00:15','00:20'
UNION ALL SELECT 5,'00:20','00:25'
UNION ALL SELECT 6,'00:25','00:30'
UNION ALL SELECT 7,'00:30','00:35'
UNION ALL SELECT 8,'00:35','00:40'
UNION ALL SELECT 9,'00:40','00:45'
UNION ALL SELECT 10,'00:45','00:50'
UNION ALL SELECT 11,'00:50','00:55'
UNION ALL SELECT 12,'00:55','01:00'
UNION ALL SELECT 13,'01:00','01:05'
UNION ALL SELECT 14,'01:05','01:10'
UNION ALL SELECT 15,'01:10','01:15'
UNION ALL SELECT 16,'01:15','01:20'
UNION ALL SELECT 17,'01:20','01:25'
UNION ALL SELECT 18,'01:25','01:30'
UNION ALL SELECT 19,'01:30','01:35'
UNION ALL SELECT 20,'01:35','01:40'
UNION ALL SELECT 21,'01:40','01:45'
UNION ALL SELECT 22,'01:45','01:50'
UNION ALL SELECT 23,'01:50','01:55'
UNION ALL SELECT 24,'01:55','02:00'
UNION ALL SELECT 25,'02:00','02:05'
UNION ALL SELECT 26,'02:05','02:10'
UNION ALL SELECT 27,'02:10','02:15'
UNION ALL SELECT 28,'02:15','02:20'
UNION ALL SELECT 29,'02:20','02:25'
UNION ALL SELECT 30,'02:25','02:30'
UNION ALL SELECT 31,'02:30','02:35'
UNION ALL SELECT 32,'02:35','02:40'
UNION ALL SELECT 33,'02:40','02:45'
UNION ALL SELECT 34,'02:45','02:50'
UNION ALL SELECT 35,'02:50','02:55'
UNION ALL SELECT 36,'02:55','03:00'
UNION ALL SELECT 37,'03:00','03:05'
UNION ALL SELECT 38,'03:05','03:10'
UNION ALL SELECT 39,'03:10','03:15'
UNION ALL SELECT 40,'03:15','03:20'
UNION ALL SELECT 41,'03:20','03:25'
UNION ALL SELECT 42,'03:25','03:30'
UNION ALL SELECT 43,'03:30','03:35'
UNION ALL SELECT 44,'03:35','03:40'
UNION ALL SELECT 45,'03:40','03:45'
UNION ALL SELECT 46,'03:45','03:50'
UNION ALL SELECT 47,'03:50','03:55'
UNION ALL SELECT 48,'03:55','04:00'
UNION ALL SELECT 49,'04:00','04:05'
UNION ALL SELECT 50,'04:05','04:10'
UNION ALL SELECT 51,'04:10','04:15'
UNION ALL SELECT 52,'04:15','04:20'
UNION ALL SELECT 53,'04:20','04:25'
UNION ALL SELECT 54,'04:25','04:30'
UNION ALL SELECT 55,'04:30','04:35'
UNION ALL SELECT 56,'04:35','04:40'
UNION ALL SELECT 57,'04:40','04:45'
UNION ALL SELECT 58,'04:45','04:50'
UNION ALL SELECT 59,'04:50','04:55'
UNION ALL SELECT 60,'04:55','05:00'
UNION ALL SELECT 61,'05:00','05:05'
UNION ALL SELECT 62,'05:05','05:10'
UNION ALL SELECT 63,'05:10','05:15'
UNION ALL SELECT 64,'05:15','05:20'
UNION ALL SELECT 65,'05:20','05:25'
UNION ALL SELECT 66,'05:25','05:30'
UNION ALL SELECT 67,'05:30','05:35'
UNION ALL SELECT 68,'05:35','05:40'
UNION ALL SELECT 69,'05:40','05:45'
UNION ALL SELECT 70,'05:45','05:50'
UNION ALL SELECT 71,'05:50','05:55'
UNION ALL SELECT 72,'05:55','06:00'
UNION ALL SELECT 73,'06:00','06:05'
UNION ALL SELECT 74,'06:05','06:10'
UNION ALL SELECT 75,'06:10','06:15'
UNION ALL SELECT 76,'06:15','06:20'
UNION ALL SELECT 77,'06:20','06:25'
UNION ALL SELECT 78,'06:25','06:30'
UNION ALL SELECT 79,'06:30','06:35'
UNION ALL SELECT 80,'06:35','06:40'
UNION ALL SELECT 81,'06:40','06:45'
UNION ALL SELECT 82,'06:45','06:50'
UNION ALL SELECT 83,'06:50','06:55'
UNION ALL SELECT 84,'06:55','07:00'
UNION ALL SELECT 85,'07:00','07:05'
UNION ALL SELECT 86,'07:05','07:10'
UNION ALL SELECT 87,'07:10','07:15'
UNION ALL SELECT 88,'07:15','07:20'
UNION ALL SELECT 89,'07:20','07:25'
UNION ALL SELECT 90,'07:25','07:30'
UNION ALL SELECT 91,'07:30','07:35'
UNION ALL SELECT 92,'07:35','07:40'
UNION ALL SELECT 93,'07:40','07:45'
UNION ALL SELECT 94,'07:45','07:50'
UNION ALL SELECT 95,'07:50','07:55'
UNION ALL SELECT 96,'07:55','08:00'
UNION ALL SELECT 97,'08:00','08:05'
UNION ALL SELECT 98,'08:05','08:10'
UNION ALL SELECT 99,'08:10','08:15'
UNION ALL SELECT 100,'08:15','08:20'
UNION ALL SELECT 101,'08:20','08:25'
UNION ALL SELECT 102,'08:25','08:30'
UNION ALL SELECT 103,'08:30','08:35'
UNION ALL SELECT 104,'08:35','08:40'
UNION ALL SELECT 105,'08:40','08:45'
UNION ALL SELECT 106,'08:45','08:50'
UNION ALL SELECT 107,'08:50','08:55'
UNION ALL SELECT 108,'08:55','09:00'
UNION ALL SELECT 109,'09:00','09:05'
UNION ALL SELECT 110,'09:05','09:10'
UNION ALL SELECT 111,'09:10','09:15'
UNION ALL SELECT 112,'09:15','09:20'
UNION ALL SELECT 113,'09:20','09:25'
UNION ALL SELECT 114,'09:25','09:30'
UNION ALL SELECT 115,'09:30','09:35'
UNION ALL SELECT 116,'09:35','09:40'
UNION ALL SELECT 117,'09:40','09:45'
UNION ALL SELECT 118,'09:45','09:50'
UNION ALL SELECT 119,'09:50','09:55'
UNION ALL SELECT 120,'09:55','10:00'
UNION ALL SELECT 121,'10:00','10:05'
UNION ALL SELECT 122,'10:05','10:10'
UNION ALL SELECT 123,'10:10','10:15'
UNION ALL SELECT 124,'10:15','10:20'
UNION ALL SELECT 125,'10:20','10:25'
UNION ALL SELECT 126,'10:25','10:30'
UNION ALL SELECT 127,'10:30','10:35'
UNION ALL SELECT 128,'10:35','10:40'
UNION ALL SELECT 129,'10:40','10:45'
UNION ALL SELECT 130,'10:45','10:50'
UNION ALL SELECT 131,'10:50','10:55'
UNION ALL SELECT 132,'10:55','11:00'
UNION ALL SELECT 133,'11:00','11:05'
UNION ALL SELECT 134,'11:05','11:10'
UNION ALL SELECT 135,'11:10','11:15'
UNION ALL SELECT 136,'11:15','11:20'
UNION ALL SELECT 137,'11:20','11:25'
UNION ALL SELECT 138,'11:25','11:30'
UNION ALL SELECT 139,'11:30','11:35'
UNION ALL SELECT 140,'11:35','11:40'
UNION ALL SELECT 141,'11:40','11:45'
UNION ALL SELECT 142,'11:45','11:50'
UNION ALL SELECT 143,'11:50','11:55'
UNION ALL SELECT 144,'11:55','12:00'
UNION ALL SELECT 145,'12:00','12:05'
UNION ALL SELECT 146,'12:05','12:10'
UNION ALL SELECT 147,'12:10','12:15'
UNION ALL SELECT 148,'12:15','12:20'
UNION ALL SELECT 149,'12:20','12:25'
UNION ALL SELECT 150,'12:25','12:30'
UNION ALL SELECT 151,'12:30','12:35'
UNION ALL SELECT 152,'12:35','12:40'
UNION ALL SELECT 153,'12:40','12:45'
UNION ALL SELECT 154,'12:45','12:50'
UNION ALL SELECT 155,'12:50','12:55'
UNION ALL SELECT 156,'12:55','13:00'
UNION ALL SELECT 157,'13:00','13:05'
UNION ALL SELECT 158,'13:05','13:10'
UNION ALL SELECT 159,'13:10','13:15'
UNION ALL SELECT 160,'13:15','13:20'
UNION ALL SELECT 161,'13:20','13:25'
UNION ALL SELECT 162,'13:25','13:30'
UNION ALL SELECT 163,'13:30','13:35'
UNION ALL SELECT 164,'13:35','13:40'
UNION ALL SELECT 165,'13:40','13:45'
UNION ALL SELECT 166,'13:45','13:50'
UNION ALL SELECT 167,'13:50','13:55'
UNION ALL SELECT 168,'13:55','14:00'
UNION ALL SELECT 169,'14:00','14:05'
UNION ALL SELECT 170,'14:05','14:10'
UNION ALL SELECT 171,'14:10','14:15'
UNION ALL SELECT 172,'14:15','14:20'
UNION ALL SELECT 173,'14:20','14:25'
UNION ALL SELECT 174,'14:25','14:30'
UNION ALL SELECT 175,'14:30','14:35'
UNION ALL SELECT 176,'14:35','14:40'
UNION ALL SELECT 177,'14:40','14:45'
UNION ALL SELECT 178,'14:45','14:50'
UNION ALL SELECT 179,'14:50','14:55'
UNION ALL SELECT 180,'14:55','15:00'
UNION ALL SELECT 181,'15:00','15:05'
UNION ALL SELECT 182,'15:05','15:10'
UNION ALL SELECT 183,'15:10','15:15'
UNION ALL SELECT 184,'15:15','15:20'
UNION ALL SELECT 185,'15:20','15:25'
UNION ALL SELECT 186,'15:25','15:30'
UNION ALL SELECT 187,'15:30','15:35'
UNION ALL SELECT 188,'15:35','15:40'
UNION ALL SELECT 189,'15:40','15:45'
UNION ALL SELECT 190,'15:45','15:50'
UNION ALL SELECT 191,'15:50','15:55'
UNION ALL SELECT 192,'15:55','16:00'
UNION ALL SELECT 193,'16:00','16:05'
UNION ALL SELECT 194,'16:05','16:10'
UNION ALL SELECT 195,'16:10','16:15'
UNION ALL SELECT 196,'16:15','16:20'
UNION ALL SELECT 197,'16:20','16:25'
UNION ALL SELECT 198,'16:25','16:30'
UNION ALL SELECT 199,'16:30','16:35'
UNION ALL SELECT 200,'16:35','16:40'
UNION ALL SELECT 201,'16:40','16:45'
UNION ALL SELECT 202,'16:45','16:50'
UNION ALL SELECT 203,'16:50','16:55'
UNION ALL SELECT 204,'16:55','17:00'
UNION ALL SELECT 205,'17:00','17:05'
UNION ALL SELECT 206,'17:05','17:10'
UNION ALL SELECT 207,'17:10','17:15'
UNION ALL SELECT 208,'17:15','17:20'
UNION ALL SELECT 209,'17:20','17:25'
UNION ALL SELECT 210,'17:25','17:30'
UNION ALL SELECT 211,'17:30','17:35'
UNION ALL SELECT 212,'17:35','17:40'
UNION ALL SELECT 213,'17:40','17:45'
UNION ALL SELECT 214,'17:45','17:50'
UNION ALL SELECT 215,'17:50','17:55'
UNION ALL SELECT 216,'17:55','18:00'
UNION ALL SELECT 217,'18:00','18:05'
UNION ALL SELECT 218,'18:05','18:10'
UNION ALL SELECT 219,'18:10','18:15'
UNION ALL SELECT 220,'18:15','18:20'
UNION ALL SELECT 221,'18:20','18:25'
UNION ALL SELECT 222,'18:25','18:30'
UNION ALL SELECT 223,'18:30','18:35'
UNION ALL SELECT 224,'18:35','18:40'
UNION ALL SELECT 225,'18:40','18:45'
UNION ALL SELECT 226,'18:45','18:50'
UNION ALL SELECT 227,'18:50','18:55'
UNION ALL SELECT 228,'18:55','19:00'
UNION ALL SELECT 229,'19:00','19:05'
UNION ALL SELECT 230,'19:05','19:10'
UNION ALL SELECT 231,'19:10','19:15'
UNION ALL SELECT 232,'19:15','19:20'
UNION ALL SELECT 233,'19:20','19:25'
UNION ALL SELECT 234,'19:25','19:30'
UNION ALL SELECT 235,'19:30','19:35'
UNION ALL SELECT 236,'19:35','19:40'
UNION ALL SELECT 237,'19:40','19:45'
UNION ALL SELECT 238,'19:45','19:50'
UNION ALL SELECT 239,'19:50','19:55'
UNION ALL SELECT 240,'19:55','20:00'
UNION ALL SELECT 241,'20:00','20:05'
UNION ALL SELECT 242,'20:05','20:10'
UNION ALL SELECT 243,'20:10','20:15'
UNION ALL SELECT 244,'20:15','20:20'
UNION ALL SELECT 245,'20:20','20:25'
UNION ALL SELECT 246,'20:25','20:30'
UNION ALL SELECT 247,'20:30','20:35'
UNION ALL SELECT 248,'20:35','20:40'
UNION ALL SELECT 249,'20:40','20:45'
UNION ALL SELECT 250,'20:45','20:50'
UNION ALL SELECT 251,'20:50','20:55'
UNION ALL SELECT 252,'20:55','21:00'
UNION ALL SELECT 253,'21:00','21:05'
UNION ALL SELECT 254,'21:05','21:10'
UNION ALL SELECT 255,'21:10','21:15'
UNION ALL SELECT 256,'21:15','21:20'
UNION ALL SELECT 257,'21:20','21:25'
UNION ALL SELECT 258,'21:25','21:30'
UNION ALL SELECT 259,'21:30','21:35'
UNION ALL SELECT 260,'21:35','21:40'
UNION ALL SELECT 261,'21:40','21:45'
UNION ALL SELECT 262,'21:45','21:50'
UNION ALL SELECT 263,'21:50','21:55'
UNION ALL SELECT 264,'21:55','22:00'
UNION ALL SELECT 265,'22:00','22:05'
UNION ALL SELECT 266,'22:05','22:10'
UNION ALL SELECT 267,'22:10','22:15'
matlab常微分方程求解
matlab常微分方程求解，通过编程实战掌握具体应用。包括matlab常微分方程求解前、matlab常微分方程求解中、matlab常微分方程求解后。
java按秒查询数据_ClickHouse留存分析工具十亿数据秒级查询方案
作者：陈璐，腾讯 CSIG 高级数据分析师本文实践了对于千万级别的用户，操作总数达万级别，每日几十亿操作流水的留存分析工具秒级别查询的数据构建方案。同时，除了留存分析，对于用户群分析，事件分析等也可以尝试用此方案来解决。背景你可能听说过Growingio、神策等数据分析平台，本文主要介绍实现留存分析工具相关的内容。留存分析是一种用来分析用户参与情况/活跃程度的分析模型，可考查进行初始行为后的用户中...
强化学习求解组合最优化问题的研究综述
摘要组合最优化问题（COP）的求解方法已经渗透到人工智能、运筹学等众多领域。随着数据规模的不断增大、问题更新速度的变快,运用传统方法求解COP问题在速度、精度、泛化能力等方面受到很大冲击。近年来,强化学习（RL）在无人驾驶、工业自动化等领域的广泛应用,显示出强大的决策力和学习能力,故而诸多研究者尝试使用RL求解COP问题,为求解此类问题提供了一种全新的方法。首先简要梳理常见的COP问题及其RL的基本原理;其次阐述RL求解COP问题的难点,分析RL应用于组合最优化（CO）领域的优势,对RL与COP问题结合的原
【路径规划】基于遗传算法求解多中心带时间窗车辆路径规划问题（VRPTW）matlab代码
1 简介 在分析时间窗的惩罚函数基础上,建立了带有时间窗的多配送中心车辆调度模型,针对模型设计了两阶段求解算法,先通过扫描算法把客户化分到不同的配送中心负责配送,然后采用遗传算法求解带有时间窗的多配送中心的车辆调度模型,最后,结合算例仿真计算验证了算法的有效性. 现有的区域内多配送中心配送多采用分区域独立配送模式．多个配送中心虽同属于一家企业，但一般情况下，企业会根据行政区划为每个配送中心划分一个业务范围，各配送中心间相对独立．在分区域独立配送模式下，一个客户会固定从属于某一特定配送中心，而不会根据客户
时间序列数据的预处理方法总结
点击上方“小白学视觉”，选择加"星标"或“置顶”重磅干货，第一时间送达时间序列数据随处可见，要进行时间序列分析，我们必须先对数据进行预处理。时间序列预处理技术对数据建模的准确性有重大影响。在本文中，我们将主要讨论以下几点：时间序列数据的定义及其重要性。时间序列数据的预处理步骤。构建时间序列数据，查找缺失值，对特征进行去噪，并查找数据集中存在的异常值。首先，让我们先了解时间序列的定义：时间序列是在特...
MySQL

56,679

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章