How to implement group statistics every 10 minutes in MYSQL

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly introduces how to use MYSQL to implement group statistics every 10 minutes. The article gives detailed sample code. I believe it will be helpful to everyone. Understanding and learning has certain reference value. Friends in need can take a look below.

Preface

The content of this article mainly introduces the implementation method of MYSQL's group statistics every 10 minutes. When drawing the distribution chart of user login and operation status within a day, it will be Very useful. Before, I only knew how to use "stored procedure" (although the execution speed is fast, it is really too inflexible). Later, I learned to use the more advanced "group by" method to flexibly implement similar functions.

Text:

-- 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

&

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代码按所需的时间间隔自动生成：
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))

Summary

The above is the entire content of the implementation method of MYSQL grouping statistics every 10 minutes, more For related content, please pay attention to the PHP Chinese website (www.php.cn)!