{"id":3003,"date":"2016-11-30T20:11:18","date_gmt":"2016-11-30T12:11:18","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=3003"},"modified":"2016-11-30T20:11:18","modified_gmt":"2016-11-30T12:11:18","slug":"mysql%e4%b8%ad%e5%a6%82%e4%bd%95%e5%ae%9e%e7%8e%b0%e6%8c%89%e6%af%8f%e9%9a%9410%e5%88%86%e9%92%9f%e8%bf%9b%e8%a1%8c%e5%88%86%e7%bb%84%e7%bb%9f%e8%ae%a1","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/3003.html","title":{"rendered":"MySQL\u4e2d\u5982\u4f55\u5b9e\u73b0\u6309\u6bcf\u969410\u5206\u949f\u8fdb\u884c\u5206\u7ec4\u7edf\u8ba1"},"content":{"rendered":"<p>=Start=<\/p>\n<h4>\u7f18\u7531\uff1a<\/h4>\n<p>\u5728\u753b\u7528\u6237\u767b\u5f55\u3001\u64cd\u4f5c\u60c5\u51b5\u5728\u4e00\u5929\u5185\u7684\u5206\u5e03\u56fe\u65f6\u4f1a\u975e\u5e38\u6709\u7528\uff0c\u4e4b\u524d\u6211\u53ea\u77e5\u9053\u7528\u300c\u5b58\u50a8\u8fc7\u7a0b\u300d\u5b9e\u73b0\u7684\u65b9\u6cd5\uff08\u867d\u7136\u6267\u884c\u901f\u5ea6\u5feb\uff0c\u4f46\u771f\u7684\u662f\u592a\u4e0d\u7075\u6d3b\u4e86\uff09\uff0c\u540e\u6765\u5b66\u4f1a\u4e86\u7528\u9ad8\u7ea7\u70b9\u7684\u300cgroup by\u300d\u65b9\u6cd5\u6765\u7075\u6d3b\u5b9e\u73b0\u7c7b\u4f3c\u529f\u80fd\u3002<\/p>\n<h4>\u6b63\u6587\uff1a<\/h4>\n<pre class=\"lang:default decode:true\">-- time_str '2016-11-20 04:31:11'\n-- date_str 20161120\n\nselect 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;\t-- 127 rows\n\nselect round(unix_timestamp(time_str)\/(10 * 60)) as timekey, count(*) from `security`.`cmd_info` where `date_str`=20161120 group by timekey order by timekey;\t-- 126 rows\n\n-- \u4ee5\u4e0a2\u4e2aSQL\u8bed\u53e5\u7684\u601d\u8def\u7c7b\u4f3c\u2014\u2014\u4f7f\u7528\u300cgroup by\u300d\u8fdb\u884c\u533a\u5206\uff0c\u4f46\u662f\u65b9\u6cd5\u6709\u6240\u4e0d\u540c\uff0c\u524d\u8005\u53ea\u80fd\u9488\u5bf910\u5206\u949f(\u62161\u5c0f\u65f6)\u7ea7\u522b\uff0c\u540e\u8005\u53ef\u4ee5\u52a8\u6001\u8c03\u6574\u95f4\u9694\u5927\u5c0f\uff0c\u4e24\u8005\u6548\u7387\u5dee\u4e0d\u591a\uff0c\u53ef\u4ee5\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u9009\u7528\n\nselect 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;\t-- 145 rows\n\nselect 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;\t-- 127 rows (\u548c date_format \u90a3\u4e2a\u7b49\u4ef7)\n\nselect 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;\t\t-- 151 rows\n<\/pre>\n<p>&amp;<\/p>\n<pre class=\"lang:default decode:true\">DELIMITER \/\/\n\nDROP PROCEDURE IF EXISTS `usp_cmd_info`;\n\nCREATE PROCEDURE `usp_cmd_info`(IN dates VARCHAR(12))\nBEGIN\n\tSELECT count(*) from `cmd_info` where `time_str` BETWEEN CONCAT(dates, \" 00:00:00\") AND CONCAT(dates, \" 00:10:00\") INTO @count_0;\n\tSELECT count(*) from `cmd_info` where `time_str` BETWEEN CONCAT(dates, \" 00:10:00\") AND CONCAT(dates, \" 00:20:00\") INTO @count_1;\n\t...\n\tSELECT count(*) from `cmd_info` where `time_str` BETWEEN CONCAT(dates, \" 23:40:00\") AND CONCAT(dates, \" 23:50:00\") INTO @count_142;\n\tSELECT count(*) from `cmd_info` where `time_str` BETWEEN CONCAT(dates, \" 23:50:00\") AND CONCAT(dates, \" 23:59:59\") INTO @count_143;\n\tselect @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;\nEND \/\/\n\nDELIMITER ;\n\nshow PROCEDURE status\\G\n\nCALL usp_cmd_info(\"2016-10-20\");<\/pre>\n<p>\u4e0a\u9762\u7684\u8fd9\u6bb5MySQL\u5b58\u50a8\u8fc7\u7a0b\u7684\u8bed\u53e5\u975e\u5e38\u957f\uff0c\u4e0d\u53ef\u80fd\u7528\u624b\u5de5\u8f93\u5165\uff0c\u53ef\u4ee5\u7528\u4e0b\u9762\u7684\u8fd9\u6bb5Python\u4ee3\u7801\u6309\u6240\u9700\u7684\u65f6\u95f4\u95f4\u9694\u81ea\u52a8\u751f\u6210\uff1a<\/p>\n<pre class=\"lang:default decode:true  \">import datetime\n\ntoday = datetime.date.today()\n# \u6216 \u7531\u7ed9\u5b9a\u683c\u5f0f\u5b57\u7b26\u4e32\u8f6c\u6362\u6210\n# today = datetime.datetime.strptime('2016-11-21', '%Y-%m-%d')\n\nmin_today_time = datetime.datetime.combine(today, datetime.time.min)\t# 2016-11-21 00:00:00\nmax_today_time = datetime.datetime.combine(today, datetime.time.max)\t# 2016-11-21 23:59:59\n\nsql_procedure_arr = []\nsql_procedure_arr2 = []\nfor x in xrange(0, 60*24\/5, 1):\n    start_datetime = min_today_time + datetime.timedelta(minutes = 5*x)\n    end_datetime = min_today_time + datetime.timedelta(minutes = 5*(x+1))\n    # print x, start_datetime.strftime(\"%Y-%m-%d %H:%M:%S\"), end_datetime.strftime(\"%Y-%m-%d %H:%M:%S\")\n    select_str = 'SELECT count(*) from `cmd_info` where `time_str` BETWEEN \"{0}\" AND \"{1}\" INTO @count_{2};'.format(start_datetime, end_datetime, x)\n    # print select_str\n    sql_procedure_arr.append(select_str)\n    sql_procedure_arr2.append('@count_{0}'.format(x))\nprint '\\n'.join(sql_procedure_arr)\nprint 'select {0};'.format(', '.join(sql_procedure_arr2))\n<\/pre>\n<h4>\u53c2\u8003\u94fe\u63a5\uff1a<\/h4>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/2793994\/group-mysql-query-by-15-min-intervals\">http:\/\/stackoverflow.com\/questions\/2793994\/group-mysql-query-by-15-min-intervals<\/a>\u00a0#\u7ed9\u529b<\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/4289345\/mysql-datetime-group-by-15-mins\">http:\/\/stackoverflow.com\/questions\/4289345\/mysql-datetime-group-by-15-mins<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/10403039\/mysql-select-query-5-minute-increment\">http:\/\/stackoverflow.com\/questions\/10403039\/mysql-select-query-5-minute-increment<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u5728\u753b\u7528\u6237\u767b\u5f55\u3001\u64cd\u4f5c\u60c5\u51b5\u5728\u4e00\u5929\u5185\u7684\u5206\u5e03\u56fe\u65f6\u4f1a\u975e\u5e38\u6709\u7528\uff0c\u4e4b\u524d\u6211\u53ea\u77e5\u9053\u7528\u300c\u5b58\u50a8\u8fc7\u7a0b\u300d\u5b9e\u73b0\u7684\u65b9 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7],"tags":[617,16,33],"class_list":["post-3003","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-groupby","tag-mysql","tag-stored-procedure"],"views":3957,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/3003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=3003"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/3003\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=3003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=3003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=3003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}