这篇文章主要给大家介绍了如何利用 MYSQL 实现每隔 10 分钟进行分组统计的方法,文中给出了详细的示例代码,相信对大家的理解和学习具有一定的参考借鉴价值,有需要的朋友们下面来一起看看吧。
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
前言
本文的内容主要是介绍了 MYSQL 每隔 10 分钟进行分组统计的实现方法,在画用户登录、操作情况在一天内的分布图时会非常有用,之前我只知道用「存储过程」实现的方法(虽然执行速度快,但真的是太不灵活了),后来学会了用高级点的「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))
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。
来源: