本文的內(nèi)容主要是介紹了MYSQL每隔10分鐘進(jìn)行分組統(tǒng)計(jì)的實(shí)現(xiàn)方法，在畫用戶登錄、操作情況在一天內(nèi)的分布圖時(shí)會(huì)非常有用，之前我只知道用「存儲(chǔ)過程」實(shí)現(xiàn)的方法（雖然執(zhí)行速度快，但真的是太不靈活了），后來學(xué)會(huì)了用高級(jí)點(diǎn)的「group by」方法來靈活實(shí)現(xiàn)類似功能。
-- 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個(gè)SQL語句的思路類似——使用「group by」進(jìn)行區(qū)分，但是方法有所不同，前者只能針對(duì)10分鐘(或1小時(shí))級(jí)別，后者可以動(dòng)態(tài)調(diào)整間隔大小，兩者效率差不多，可以根據(jù)實(shí)際情況選用
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 那個(gè)等價(jià))
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存儲(chǔ)過程的語句非常長(zhǎng)，不可能用手工輸入，可以用下面的這段Python代碼按所需的時(shí)間間隔自動(dòng)生成：
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。
