Mysql入门MYSQL每隔10分钟进行分组统计的实现方法

《Mysql入门MYSQL每隔10分钟进行分组统计的实现方法》要点：
本文介绍了Mysql入门MYSQL每隔10分钟进行分组统计的实现方法，希望对您有用。如果有疑问，可以联系我们。

前言MYSQL教程

本文的内容主要是介绍了MYSQL每隔10分钟进行分组统计的实现方法,在画用户登录、操作情况在一天内的分布图时会非常有用,之前我只知道用「存储过程」实现的方法(虽然执行速度快,但真的是太不灵活了),后来学会了用高级点的「group by」方法来灵活实现类似功能.MYSQL教程

正文：MYSQL教程

-- 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),0)*10; -- 145 rows
select concat(date(time_str),floor(minute(time_str)/10)*10),floor(minute(time_str)/10)*10; -- 127 rows (和 date_format 那个等价)
select concat(date(time_str),ceil(minute(time_str)/10)*10),ceil(minute(time_str)/10)*10; -- 151 rows

&
MYSQL教程

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 statusG
CALL usp_cmd_info("2016-10-20");

上面的这段MySQL存储过程的语句非常长,不可能用手工输入,可以用下面的这段Python代码按所需的时间间隔自动生成：
MYSQL教程

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

总结MYSQL教程

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流.MYSQL教程

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!