一聚教程网:一个值得你收藏的教程网站

如何实现MYSQL每隔10分钟进行分组统计

时间:2016-12-15 00:00:00 编辑:简简单单 来源:转载

前言

本文的内容主要是介绍了MYSQL每隔10分钟进行分组统计的实现方法,在画用户登录、操作情况在一天内的分布图时会非常有用,之前我只知道用「存储过程」实现的方法(虽然执行速度快,但真的是太不灵活了),后来学会了用高级点的「group by」方法来灵活实现类似功能。

正文

-- time_str '2016-11-20 04:31:11'
-- date_str 20161120
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;-- 127 rows
selectround(unix_timestamp(time_str)/(10 * 60))astimekey,count(*)from`security`.`cmd_info`where`date_str`=20161120groupbytimekeyorderbytimekey;-- 126 rows
-- 以上2个SQL语句的思路类似——使用「group by」进行区分,但是方法有所不同,前者只能针对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;-- 145 rows
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;-- 127 rows (和 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;-- 151 rows

&

DELIMITER //
DROPPROCEDUREIF EXISTS `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
CALL usp_cmd_info("2016-10-20");

上面的这段MySQL存储过程的语句非常长,不可能用手工输入,可以用下面的这段Python代码按所需的时间间隔自动生成:

import datetime
 
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-21 00:00:00
max_today_time = datetime.datetime.combine(today, datetime.time.max) # 2016-11-21 23: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))
  # print x, start_datetime.strftime("%Y-%m-%d %H:%M:%S"), end_datetime.strftime("%Y-%m-%d %H:%M:%S")
  select_str ='SELECT count(*) from `cmd_info` where `time_str` BETWEEN "{0}" AND "{1}" INTO @count_{2};'.format(start_datetime, end_datetime, x)
  # print select_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))

文章评论

热门栏目