按时间每个5分钟统计数据

表结构

t_current_channel_static

	Field	Type	Comment
	id	varchar(255) NULL
	sampletime	varchar(255) NULL
	year	varchar(255) NULL
	month	varchar(255) NULL
	day	varchar(255) NULL
	currenttime	varchar(255) NULL
	jioylx	varchar(255) NULL
	trannum	varchar(255) NULL
	successnum	varchar(255) NULL
	successrate	varchar(255) NULL
	trantime	varchar(255) NULL
	date	varchar(255) NULL

currenttime这个字段 内数据：16:53

编写sql

SELECT cc.date,cc.`jioylx`,cc.`year`,cc.`month`,cc.`day`,DATE_FORMAT(STR_TO_DATE( cc.sampletime, '%Y%m%d-%H'),'%H') AS h,FLOOR(DATE_FORMAT(STR_TO_DATE( cc.currenttime, '%H:%i' ),'%i')/5) AS c,SUM(cc.trannum),SUM(cc.successnum),AVG(cc.successrate) 
FROM t_current_channel_static cc
WHERE cc.jioyl="" AND cc.date >='20121011' AND cc.date<='20121012' GROUP BY cc.`year`,cc.`month`,cc.`day`,h,c