Mysql every 10 minutes to implement the method of grouping statistics _mysql

Objective

The main content of this article is to introduce MySQL every 10 minutes to carry out the method of grouping statistics, it can be useful to draw user logins, operations in a single day, and I've only known how to use the "Stored Procedures" method (although it's fast, but it's really inflexible) and then learned to use the advanced Group by"method to flexibly implement similar functions.

Body:

--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 ' the ' cmd_info ' where ' date_str ' =20161120 Group by Time_flag ORDER by Time_f Lag --127 Rows Select round (Unix_timestamp (TIME_STR)/()) as TimeKey, COUNT (*) from ' security '. ' Cmd_info ' where ' date_ Str ' =20161120 GROUP by TimeKey Order by TimeKey; --126 rows--similar ideas for the above 2 SQL statements--use "group by" to differentiate, but the method is different, the former only for 10 minutes (or 1 hours) level, the latter can dynamically adjust the interval size, the two efficiency is similar, can choose according to the actual situation 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 (and DATe_format that equivalence) 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

and

DELIMITER//DROP PROCEDURE IF EXISTS ' usp_cmd_info '; CREATE PROCEDURE ' Usp_cmd_info ' (in Dates VARCHAR (a)) BEGIN SELECT Count (*) from ' cmd_info ' where ' time_str ' BETWEEN conc
 In (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 @c
 ount_1;
 ... SELECT Count (*) from ' cmd_info ' where ' time_str ' BETWEEN CONCAT (dates, "23:40:00") and CONCAT (dates, "23:50:00") into @c
 ount_142; SELECT Count (*) from ' cmd_info ' where ' time_str ' BETWEEN CONCAT (dates, "23:50:00") and CONCAT (dates, "23:59:59") into @c
 ount_143; Select @count_0, @count_1, @count_2, @count_3, @count_4, @count_5, @count_6, @count_7, the @count_8, @count_9 Nt_11, @count_12, @count_13, @count_14, @count_15, @count_16, @count_17, @count_18, @count_19, @count_20 Nt_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_43, @count_44, @count_45, @count_46, @count_47, @count_48, @count_49, @count_50, @count_54, @count_55, @count_56, @count_57, @count_58, @count_59, @count_60, @count_61, @count_65, @count_66, @count_67, @count_68, @count_69, @count_70, @count_71, @count_72, @count_76, @count_77, @count_78, @count_79, @count_80, @count_81, @count_82, @count_83, @count_87, @count_88, @count_89, @count_90, @count_91, @count_92, @count_93, @count_94, @count_98, @count_99, @count_100, @count_101, @count_102, @count_103, @count_104, @count_105, @count_106, @ 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_131, @count_132, @count_133, @count_134, @count_135, @count_136, @count_137, @count_138, @count_139,
@count_141, @count_142, @count_143;

End//DELIMITER; Show PROCEDURE status\g call Usp_cmd_info ("2016-10-20");

The statements above for this MySQL stored procedure are very long and cannot be entered manually, and can be automatically generated at the desired time interval using the following Python code:

Import datetime today = Datetime.date.today () # or converted from a given format string to # 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 = Datetim E.datetime.combine (Today, Datetime.time.max) # 2016-11-21 23:59:59 Sql_procedure_arr = [] sql_procedure_arr2 = [] for x i n 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.strft IME ("%y-%m-%d%h:%m:%s") select_str = ' SELECT count (*) from ' cmd_info ' where ' time_str ' BETWEEN ' {0} ' and ' {1} ' into @co UNT_{2}; '. Format (start_datetime, End_datetime, x) # print Select_str sql_procedure_arr.append (select_str) sql_procedure_arr2.a Ppend (' @count_ {0} '. Format (x)) print ' \ n '. Join (Sql_procedure_arr) print ' select {0}; '. Format (', '. Join (SQL_PROCEDURE_ARR2))

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.