• notice
  • Congratulations on the launch of the Sought Tech site

Mysql performs group statistics every 10 minutes

Foreword

The content of this article mainly introduces the implementation method of MYSQL grouping statistics every 10 minutes.It will be very useful when drawing the distribution diagram of user login and operation in a day.Before I only knew to use "stored procedures" to achieve (Although the execution speed is fast, it is really too inflexible).Later, I learned to use the advanced "group by" method to achieve similar functions flexibly.

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

- The idea of ​​the above two SQL statements is similar-use "group by" to distinguish, but the method is different, the former can only be used for 10 minutes (or 1 hour) level, the latter can dynamically adjust the interval size, the efficiency of the two Almost, you 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 (equivalent to 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, @c ount_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");

The statement of the MySQL stored procedure above is very long, and it is impossible to enter it manually.You can use the following Python code to automatically generate it at the required time interval:

import datetime

today = datetime.date.today()
# Or converted from the given format string
# 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 this article.I hope that the content of this article will be helpful to your study or work.If you have any questions, you can leave a message to communicate.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy atorvastatin 80mg generic & lt;a href="https://lipiws.top/"& gt;atorvastatin 20mg price& lt;/a& gt; lipitor 10mg tablet

Yrdqfu

2024-03-08

Leave a Reply

+