在mysql中使用存储过程和触发器完成HelpDesk聊天记录的统计分析

应用场景：

HelpDesk团队有大约100名工程师，他们的支持聊天记录存储在一个叫conf的原始表里面：

userlist表存储着所有工程师的名单（为简化，略去一些字段）：

需求：

统计每个工程师在某一个给定时间段（比如日，周，月）的聊天会话的数量（以confid字段聚合, 代表一次完整聊天会话），有个奇葩的要求，fromname和toname（可能涉及多人，用分号隔开）都算工程师，都要统计。

原始表我们无权更改，如果用一条SQL语句实现（已经实现了，但是很复杂），这种情况下，用存储过程比较合适，下面是存储过程：

DROP PROCEDURE IF EXISTS `calConfNumberbyUser`;
DELIMITER //
  CREATE PROCEDURE calConfNumberbyUser(IN daynumber INT, IN startRow INT, IN pageSize INT)
  --外层循环从userlist表里面取到所有的工程师列表的游标
    BEGIN    
        DECLARE chatername VARCHAR(255) default '';
        DECLARE done int default false; 

        DECLARE cur CURSOR for (SELECT username FROM `userlist` ORDER BY id LIMIT startRow, pageSize);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=null;  

        OPEN cur;

        FETCH cur into chatername;
        WHILE(done is not null) DO
        --内层循环计算每一个工程师的在某一个时间段的会话数量
        BEGIN
         DECLARE usersgroup VARCHAR(255) default '';
         DECLARE confNumber int(8) default 0;
         DECLARE done int default false; 
         DECLARE cur CURSOR for (SELECT GROUP_CONCAT(fromname,";", toname) as name  FROM `conf` where messagetime < NOW() and messagetime >= date_sub(NOW(), INTERVAL daynumber DAY) group BY confid );

         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=null;
         OPEN cur;
         fetch cur into usersgroup;
         while(done is not null) do

            set usersgroup = REPLACE(usersgroup, ';', ',');
            if FIND_IN_SET(chatername, usersgroup)  THEN
            set confNumber = confNumber + 1;
            end if;
            fetch cur into usersgroup;
        end while;
        close cur;
        select chatername, confNumber;
        END;

        FETCH cur into chatername;
        END WHILE;

      CLOSE cur;
    END;
    //
DELIMITER ;

简单解释一下：

入参： 包括时间间隔，以及分页的参数

采用嵌套的循环，外层是取得userlist表的游标，内层是在conf表（先用confid聚合），然后在结果集里面查看是否有该工程师，如果有的话，计数器就加+1.

后来考虑这样计算搜索效率太低，虽然我们无法改变原始表，但是可以在原始表上增加一个触发器来生成一个新的表(confsta)，便于进行统计：

思路： 每一次原始表添加一个新的聊天记录， 将参与的工程师名(包括fromname和toname)和confid取出，并在confsta中查询，如果confid已存在，说明是同一个会话还在进行，则只update表中的时间，如果不存在，则添加一条新纪录：

DROP PROCEDURE IF EXISTS `calConfNumberbyUser`;
DELIMITER //
  CREATE PROCEDURE calConfNumberbyUser(IN daynumber INT, IN startRow INT, IN pageSize INT)
    BEGIN

        DECLARE chatername VARCHAR(255) default '';
        DECLARE done int default false; 

        DECLARE cur CURSOR for (SELECT username FROM `userlist` ORDER BY id LIMIT startRow, pageSize);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=null;  

        OPEN cur;

        FETCH cur into chatername;
        WHILE(done is not null) DO
        BEGIN
         DECLARE usersgroup VARCHAR(255) default '';
         DECLARE confNumber int(8) default 0;
         DECLARE done int default false; 
         DECLARE cur CURSOR for (SELECT GROUP_CONCAT(fromname,";", toname) as name  FROM `conf` where messagetime < NOW() and messagetime >= date_sub(NOW(), INTERVAL daynumber DAY) group BY confid );

         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=null;
         OPEN cur;
         fetch cur into usersgroup;
         while(done is not null) do

            set usersgroup = REPLACE(usersgroup, ';', ',');
            if FIND_IN_SET(chatername, usersgroup)  THEN
            set confNumber = confNumber + 1;
            end if;
            fetch cur into usersgroup;
        end while;
        close cur;
        select chatername, confNumber;
        END;

        FETCH cur into chatername;
        END WHILE;

      CLOSE cur;
    END;
    //
DELIMITER ;

用这种方式，再统计工程师的聊天数量，就可以通过简单的sql查询，而需要用到存储过程了。

在spring boot中调用存储过程：

以下为示例，跟上面的工程无关：

package demotest.controller;

import demotest.entity.SimonOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.repository.query.Param;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;
import java.util.Date;
import java.util.List;

/**
 * Created by simon on 2017/6/22.
 */
@CrossOrigin(origins = "http://localhost:3000")
@RestController
@PreAuthorize("hasRole('ADMIN')")
public class StoreProcedureController {

    @Autowired
    private EntityManager entityManager;

    @RequestMapping("/simonorder/calpro")
    public List<SimonOrder> callpro(@Param("delivery_date") String delivery_date, @Param("startrow") Integer startrow, @Param("pagesize") Integer pagesize)  {
        //传入的是时间戳的字符串： 1498186138000，转化为Date，作为存储过程的入参
        Date delivery = new Date(new Long(delivery_date));
        StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("test");
        storedProcedureQuery.registerStoredProcedureParameter("startrow", Integer.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter("pagesize", Integer.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter("delivery_date", Date.class, ParameterMode.IN);
        storedProcedureQuery.setParameter("startrow", startrow);
        storedProcedureQuery.setParameter("pagesize", pagesize);
        storedProcedureQuery.setParameter("delivery_date", delivery);
        storedProcedureQuery.execute();
        return storedProcedureQuery.getResultList();
    }
}

对应在MySQL的存储过程定义：

DROP PROCEDURE IF EXISTS `test`;
DELIMITER //
CREATE PROCEDURE test(IN startrow int, IN pagesize int, timelimit datetime)
BEGIN
SELECT * from simon_order where delivery_date < timelimit limit startrow, pagesize;
END;
//
DELIMITER ;

简单说一下，只要注入一个EntityManager， 然后按照示例一步一步照做就可以了，因为是从一个项目中直接拷贝过来的，有一些无关的代码（比如spring security的@PreAuthorize(“hasRole(‘ADMIN’)”)， 还有跨域的问题等，都不用管）

在客户端用Postman调用：

http://localhost:8099/simonorder/calpro?startrow=1&pagesize=10&delivery_date=1498186138000

返回结果：

[
  [
    33,
    4,
    1497801600000,
    "订单2"
  ]
]