MYSQL每隔10分钟进行分组统计的实现方法

前言

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

正文：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- time_str '2016-11-20 04:31:11' -- date_str 20161120   select concat( left (date_format(time_str, '%y-%m-%d %h:%i' ),15), '0' ) as time_flag,  count (*) as count from `security`.`cmd_info`  where `date_str`=20161120  group by time_flag  order by time_flag; -- 127 rows   select round(unix_timestamp(time_str)/(10 * 60)) as timekey, count (*)  from `security`.`cmd_info`  where `date_str`=20161120 group by timekey order by timekey; -- 126 rows   -- 以上2个SQL语句的思路类似——使用「group by」进行区分，但是方法有所不同， 前者只能针对10分钟(或1小时)级别， 后者可以动态调整间隔大小，两者效率差不多，可以根据实际情况选用   select concat( date (time_str), ' ' , hour (time_str), ':' ,round( minute (time_str)/10,0)*10),  count (*) from `security`.`cmd_info` where `date_str`=20161120  group by date (time_str), hour (time_str), round( minute (time_str)/10,0)*10;  -- 145 rows   select concat( date (time_str), ' ' , hour (time_str), ':' ,floor( minute (time_str)/10)*10),  count (*) from `security`.`cmd_info` where `date_str`=20161120  group by date (time_str), hour (time_str), floor( minute (time_str)/10)*10;  -- 127 rows (和 date_format 那个等价)   select concat( date (time_str), ' ' , hour (time_str), ':' ,ceil( minute (time_str)/10)*10),  count (*) from `security`.`cmd_info` where `date_str`=20161120  group by date (time_str), hour (time_str), ceil( minute (time_str)/10)*10;  -- 151 rows

&

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

DELIMITER //   DROP PROCEDURE IF EXISTS `usp_cmd_info`;   CREATE PROCEDURE `usp_cmd_info`( IN dates VARCHAR (12)) BEGIN   SELECT count (*) from `cmd_info` where `time_str`  BETWEEN CONCAT(dates, " 00:00:00" ) AND CONCAT(dates, " 00:10:00" ) INTO @count_0;   SELECT count (*) from `cmd_info` where `time_str`  BETWEEN CONCAT(dates, " 00:10:00" ) AND CONCAT(dates, " 00:20:00" ) INTO @count_1;   ...   SELECT count (*) from `cmd_info` where `time_str` BETWEEN CONCAT(dates, " 23:40:00" ) AND CONCAT(dates, " 23:50:00" ) INTO @count_142;   SELECT count (*) from `cmd_info` where `time_str`  BETWEEN CONCAT(dates, " 23:50:00" ) AND CONCAT(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 ;   show PROCEDURE status\G   CALL usp_cmd_info( "2016-10-20" );

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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 = [] for x in xrange(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))

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。

文章来源：http://www.jb51.net/article/100166.htm