【流量帝】计算某一天每分钟的增量订单量用以展现曲线图

新建了存储每分钟订单量的表：

CREATE TABLE `rpt_order_per_min` (
  `minute_string` varchar(32) NOT NULL,
  `total_count` bigint(20) DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  PRIMARY KEY (`minute_string`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

新建了一个存储过程：

CREATE PROCEDURE `countOrderPerMinute`(
        IN datestr VARCHAR(8)
) 
BEGIN
    DECLARE _done int default 0;
    DECLARE uid BIGINT ;
    DECLARE ttlcount int(11) default 0;
    DECLARE _cs CURSOR FOR 
        SELECT `id` from pro_order where date_format(`creation_date`,'%Y%m%d')=datestr and `status` ='处理完成';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
    OPEN _cs;
    REPEAT 
        fetch _cs into uid;
        IF NOT _done THEN 
            set ttlcount = ttlcount + 1;
            insert into `rpt_order_per_min`(`minute_string` ,`total_count` ,`creation_date` ) select date_format(creation_date, '%Y%m%d%H%i'),ttlcount as total_count1,`creation_date`  from `pro_order` where id = uid  ON DUPLICATE KEY UPDATE total_count=total_count+1;
        END IF;
    UNTIL _done END REPEAT; 
    CLOSE _cs;  

END 

利用其它手段（代码也好，数据库事件机制也好）调用如下sql就可以计算到表里了：

call countOrderPerMinute('20180831');

最终页面显示效果如下：