MYSQL で 10 分ごとのグループ統計を実装する方法

Mysql リレーショナル データベース管理システム

MySQL は、スウェーデンの MySQL AB 社によって開発された、オープンソースの小規模リレーショナル データベース管理システムです。 MySQL は、インターネット上の小規模および中規模の Web サイトで広く使用されています。 MySQL は、サイズが小さく、速度が速く、総所有コストが低く、特にオープンソースの特性により、Web サイトの総所有コストを削減するために、多くの中小規模の Web サイトが Web サイト データベースとして MySQL を選択しています。

この記事では、主に MYSQL を使用して 10 分ごとのグループ統計を実装する方法を紹介します。この記事には詳細なサンプル コードが記載されているので、必要な方はぜひご覧ください。以下を見てください。

はじめに

この記事の内容は、主に MYSQL の 10 分ごとのグループ統計の実装方法を紹介します。これは、1 日以内のユーザーのログインと稼働状況の分布図を描くときに非常に役立ちます。私は「Storage Process」実装方法を使用しました（実行速度は速いですが、実際には柔軟性が低すぎます）。その後、より高度な「group by」方法を使用して同様の機能を柔軟に実装する方法を学びました。

本文:

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

概要

上記は、10 分ごとの MYSQL グループ化統計の実装方法の全内容です。さらに関連するコンテンツについては、PHP 中国語 Web サイト (www.php.ん）！