So implementieren Sie Gruppenstatistiken alle 10 Minuten in MYSQL

Relationales MySQL-Datenbankverwaltungssystem

MySQL ist ein kleines relationales Open-Source-Datenbankverwaltungssystem, das von der schwedischen Firma MySQL AB entwickelt wurde. MySQL wird häufig auf kleinen und mittelgroßen Websites im Internet verwendet. Aufgrund der geringen Größe, der hohen Geschwindigkeit, der niedrigen Gesamtbetriebskosten und insbesondere der Eigenschaften von Open Source wählen viele kleine und mittlere Websites MySQL als Website-Datenbank, um die Gesamtbetriebskosten der Website zu senken.

Dieser Artikel stellt hauptsächlich die Verwendung von MYSQL zur Implementierung von Gruppenstatistiken vor. Ich glaube, dass er für alle nützlich sein wird hat einen gewissen Referenzwert. Freunde in Not können einen Blick unten werfen.

Vorwort

Der Inhalt dieses Artikels stellt hauptsächlich die Implementierungsmethode der Gruppenstatistik von MYSQL vor. Beim Zeichnen des Verteilungsdiagramms der Benutzeranmeldung und des Betriebsstatus innerhalb eines Tages Wird sehr nützlich sein. Früher wusste ich nur, wie man „gespeicherte Prozeduren“ verwendet (obwohl die Ausführungsgeschwindigkeit sehr hoch ist, ist sie wirklich zu unflexibel). Funktionen.

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

Zusammenfassung

Das Obige ist der gesamte Inhalt der Implementierungsmethode von MYSQL, die jeweils Gruppenstatistiken durchführt 10 Minuten Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!