mysql 计算列实现时间分段

需要注意的是：计算列的 AS 后面一定要带括号 

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 )+1 );

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

# 2. 增加辅助表
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\'
UNION ALL 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\';

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