MYSQL每隔10分钟进行分组统计的实现方法
前言
本文的内容主要是介绍了MYSQL每隔10分钟进行分组统计的实现方法，在画用户登录、操作情况在一天内的分布图时会非常有用，之前我只知道用「存储过程」实现的方法（虽然执行速度快，但真的是太不灵活了），后来学会了用高级点的「groupby」方法来灵活实现类似功能。
正文：
--time_str'2016-11-2004:31:11' --date_str20161120 selectconcat(left(date_format(time_str,'%y-%m-%d%h:%i'),15),'0')astime_flag,count(*)ascountfrom`security`.`cmd_info`where`date_str`=20161120groupbytime_flagorderbytime_flag;--127rows selectround(unix_timestamp(time_str)/(10*60))astimekey,count(*)from`security`.`cmd_info`where`date_str`=20161120groupbytimekeyorderbytimekey;--126rows --以上2个SQL语句的思路类似——使用「groupby」进行区分，但是方法有所不同，前者只能针对10分钟(或1小时)级别，后者可以动态调整间隔大小，两者效率差不多，可以根据实际情况选用 selectconcat(date(time_str),'',hour(time_str),':',round(minute(time_str)/10,0)*10),count(*)from`security`.`cmd_info`where`date_str`=20161120groupbydate(time_str),hour(time_str),round(minute(time_str)/10,0)*10;--145rows selectconcat(date(time_str),'',hour(time_str),':',floor(minute(time_str)/10)*10),count(*)from`security`.`cmd_info`where`date_str`=20161120groupbydate(time_str),hour(time_str),floor(minute(time_str)/10)*10;--127rows(和date_format那个等价) selectconcat(date(time_str),'',hour(time_str),':',ceil(minute(time_str)/10)*10),count(*)from`security`.`cmd_info`where`date_str`=20161120groupbydate(time_str),hour(time_str),ceil(minute(time_str)/10)*10;--151rows
&
DELIMITER//
DROPPROCEDUREIFEXISTS`usp_cmd_info`;
CREATEPROCEDURE`usp_cmd_info`(INdatesVARCHAR(12))
BEGIN
SELECTcount(*)from`cmd_info`where`time_str`BETWEENCONCAT(dates,"00:00:00")ANDCONCAT(dates,"00:10:00")INTO@count_0;
SELECTcount(*)from`cmd_info`where`time_str`BETWEENCONCAT(dates,"00:10:00")ANDCONCAT(dates,"00:20:00")INTO@count_1;
...
SELECTcount(*)from`cmd_info`where`time_str`BETWEENCONCAT(dates,"23:40:00")ANDCONCAT(dates,"23:50:00")INTO@count_142;
SELECTcount(*)from`cmd_info`where`time_str`BETWEENCONCAT(dates,"23:50:00")ANDCONCAT(dates,"23:59:59")INTO@count_143;
select@count_0,@count_1,@count_2,@count_3,@count_4,@count_5,@count_6,@count_7,@count_8,@count_9,@count_10,@count_11,@count_12,@count_13,@count_14,@count_15,@count_16,@count_17,@count_18,@count_19,@count_20,@count_21,@count_22,@count_23,@count_24,@count_25,@count_26,@count_27,@count_28,@count_29,@count_30,@count_31,@count_32,@count_33,@count_34,@count_35,@count_36,@count_37,@count_38,@count_39,@count_40,@count_41,@count_42,@count_43,@count_44,@count_45,@count_46,@count_47,@count_48,@count_49,@count_50,@count_51,@count_52,@count_53,@count_54,@count_55,@count_56,@count_57,@count_58,@count_59,@count_60,@count_61,@count_62,@count_63,@count_64,@count_65,@count_66,@count_67,@count_68,@count_69,@count_70,@count_71,@count_72,@count_73,@count_74,@count_75,@count_76,@count_77,@count_78,@count_79,@count_80,@count_81,@count_82,@count_83,@count_84,@count_85,@count_86,@count_87,@count_88,@count_89,@count_90,@count_91,@count_92,@count_93,@count_94,@count_95,@count_96,@count_97,@count_98,@count_99,@count_100,@count_101,@count_102,@count_103,@count_104,@count_105,@count_106,@count_107,@count_108,@count_109,@count_110,@count_111,@count_112,@count_113,@count_114,@count_115,@count_116,@count_117,@count_118,@count_119,@count_120,@count_121,@count_122,@count_123,@count_124,@count_125,@count_126,@count_127,@count_128,@count_129,@count_130,@count_131,@count_132,@count_133,@count_134,@count_135,@count_136,@count_137,@count_138,@count_139,@count_140,@count_141,@count_142,@count_143;
END//
DELIMITER;
showPROCEDUREstatus\G
CALLusp_cmd_info("2016-10-20");
上面的这段MySQL存储过程的语句非常长，不可能用手工输入，可以用下面的这段Python代码按所需的时间间隔自动生成：
importdatetime
today=datetime.date.today()
#或由给定格式字符串转换成
#today=datetime.datetime.strptime('2016-11-21','%Y-%m-%d')
min_today_time=datetime.datetime.combine(today,datetime.time.min)#2016-11-2100:00:00
max_today_time=datetime.datetime.combine(today,datetime.time.max)#2016-11-2123:59:59
sql_procedure_arr=[]
sql_procedure_arr2=[]
forxinxrange(0,60*24/5,1):
start_datetime=min_today_time+datetime.timedelta(minutes=5*x)
end_datetime=min_today_time+datetime.timedelta(minutes=5*(x+1))
#printx,start_datetime.strftime("%Y-%m-%d%H:%M:%S"),end_datetime.strftime("%Y-%m-%d%H:%M:%S")
select_str='SELECTcount(*)from`cmd_info`where`time_str`BETWEEN"{0}"AND"{1}"INTO@count_{2};'.format(start_datetime,end_datetime,x)
#printselect_str
sql_procedure_arr.append(select_str)
sql_procedure_arr2.append('@count_{0}'.format(x))
print'\n'.join(sql_procedure_arr)
print'select{0};'.format(','.join(sql_procedure_arr2))
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。