前言：
尽管感觉比较笨，但是也算是准确计算出了结果
思路是：
用户登录和登出这段时间有唯一会话ID标记
用户每次状态切换都要记录一条数据
然后基于上述数据通过算法计算给定时间段内该用户的”在线“，”小休“，”离线“时间
难点：
性能
算法
表结构及模拟数据：
DROP TABLE IF EXISTS `im_line_record`; CREATE TABLE `im_line_record` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` varchar(64) DEFAULT NULL COMMENT '用户ID', `line_status` int(11) DEFAULT NULL COMMENT '在线状态：1在线，2小休，3离线', `line_time` datetime DEFAULT NULL COMMENT '状态切换时间', `login_status` int(11) DEFAULT NULL COMMENT '登录状态：1登录，2登出（登录传1，登出传2，切换传1）', `login_session_id` varchar(64) DEFAULT NULL COMMENT '登录唯一标识', PRIMARY KEY (`id`), KEY `idx_userid` (`user_id`), KEY `idx_linetime` (`line_time`) ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4; INSERT INTO `im_line_record` VALUES ('13', '838', '3', '2023-04-11 14:00:00', '1', 'mfdlzmpiad'); INSERT INTO `im_line_record` VALUES ('14', '838', '3', '2023-04-11 14:10:00', '1', 'mfdlzmpiad'); INSERT INTO `im_line_record` VALUES ('15', '838', '1', '2023-04-11 14:20:00', '1', 'mfdlzmpiad'); INSERT INTO `im_line_record` VALUES ('16', '838', '2', '2023-04-11 14:30:00', '1', 'mfdlzmpiad'); INSERT INTO `im_line_record` VALUES ('17', '838', '2', '2023-04-11 14:40:00', '2', 'mfdlzmpiad'); INSERT INTO `im_line_record` VALUES ('18', '838', '2', '2023-04-11 15:10:00', '1', 'rbtpehvdiu'); INSERT INTO `im_line_record` VALUES ('19', '838', '1', '2023-04-11 15:20:00', '1', 'rbtpehvdiu'); INSERT INTO `im_line_record` VALUES ('20', '838', '1', '2023-04-11 15:30:00', '1', 'rbtpehvdiu'); INSERT INTO `im_line_record` VALUES ('21', '838', '3', '2023-04-11 15:40:00', '1', 'rbtpehvdiu'); INSERT INTO `im_line_record` VALUES ('22', '838', '1', '2023-04-11 15:50:00', '1', 'rbtpehvdiu'); INSERT INTO `im_line_record` VALUES ('23', '838', '1', '2023-04-11 15:55:00', '2', 'rbtpehvdiu');
算法：
String nowStr = DateUtils.nullFormat(new Date(), DateUtils.TIME_3); Set<String> userIdList = new LinkedHashSet<>(); // 查被截断的会话 List<LineRecordInfo> cutted = statMapper.cutted(startTimeA, startTimeB); Map<String,LineRecordInfo> cuttedMap = new HashMap<>(); List<String> sessionIdList = new ArrayList<>(); for(LineRecordInfo info : cutted) { String loginSessionId = info.getLoginSessionId(); cuttedMap.put(loginSessionId, info); sessionIdList.add(loginSessionId); } // 查全部会话 List<LineRecordInfo> all = statMapper.all(startTimeA, startTimeB, sessionIdList); for(ImUser user : userList) { userIdList.add(user.getUserId()); } // 统计会话信息 List<LineRecordStat> statList = StatUtils.cal(all, userIdList, startTimeA, startTimeB, cuttedMap.keySet(), nowStr); // 补充接待信息 StatUtils.padding(receptionList, statList);
public interface StatMapper { List<LineRecordInfo> cutted(@Param("startTimeA") Date startTimeA, @Param("startTimeB") Date startTimeB); List<LineRecordInfo> all(@Param("startTimeA") Date startTimeA, @Param("startTimeB") Date startTimeB, @Param("sessionIdList") List<String> sessionIdList); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.zno.online.mapper.StatMapper"> <resultMap type="cn.zno.online.dto.LineRecordInfo" id="StatResultMap"> <result column="user_id" jdbcType="VARCHAR" property="userId" /> <result column="info" jdbcType="VARCHAR" property="info" /> <result column="login_session_id" jdbcType="VARCHAR" property="loginSessionId" /> </resultMap> <select id="cutted" parameterType="map" resultMap="StatResultMap"> select GROUP_CONCAT(CONCAT(line_status,'_',line_time) order by id asc) info ,user_id,login_session_id from im_line_record t group by user_id, login_session_id having (min(line_time) < #{startTimeA} and max(line_time) > #{startTimeA}) or (min(line_time) < #{startTimeB} and max(line_time) > #{startTimeB}) </select> <select id="all" parameterType="map" resultMap="StatResultMap"> select GROUP_CONCAT(CONCAT(line_status,'_',line_time) order by id asc) info ,user_id,login_session_id from im_line_record where line_time >= #{startTimeA} and line_time <= #{startTimeB} <if test="sessionIdList.size() > 0"> or login_session_id in ( <foreach collection="sessionIdList" item="item" separator=","> #{item} </foreach> ) </if> group by user_id, login_session_id order by min(id) asc </select> </mapper>
import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import cn.xs.ambi.bas.util.DateUtils; import cn.xs.ambi.bas.util.StringUtils; import cn.xs.qishi.entity.pojo.ImReception; import cn.xs.qishi.online.dto.LineRecordInfo; import cn.xs.qishi.online.dto.LineRecordSection; import cn.xs.qishi.online.dto.LineRecordStat; public class StatUtils { /** * 补充接待信息 * @param receptionList * @param statList */ public static void padding(List<ImReception> receptionList, List<LineRecordStat> statList){ Map<String, LineRecordStat> map = new HashMap<>(); for(LineRecordStat stat : statList) { map.put(stat.getUserId(), stat); } for(ImReception reception: receptionList) { String servId = reception.getServId(); LineRecordStat lineRecordStat = map.get(servId); if(lineRecordStat == null) { continue; } Integer effect = reception.getEffect(); Integer closeType = reception.getCloseType(); Integer firstResSec = reception.getFirstResSec(); lineRecordStat.setReceptionNum(forceNum(lineRecordStat.getReceptionNum()) + 1); if("1".equals(String.valueOf(effect))) { lineRecordStat.setEffect(forceNum(lineRecordStat.getEffect()) + 1); }else { lineRecordStat.setNoEff(forceNum(lineRecordStat.getNoEff()) + 1); } if("4".equals(String.valueOf(closeType))) { lineRecordStat.setTurned(forceNum(lineRecordStat.getTurned()) + 1); } if("0".equals(forceNum(firstResSec)+"")) { lineRecordStat.setNoRes(forceNum(lineRecordStat.getNoRes()) + 1); } } } /** * 计算会话统计信息 * @param all * @param userIds * @param a * @param b * @param cutted * @return */ public static List<LineRecordStat> cal(List<LineRecordInfo> all, Set<String> userIds, Date a, Date b, Set<String> cutted, String nowStr) { String aStr = DateUtils.nullFormat(a); String bStr = DateUtils.nullFormat(b); List<LineRecordStat> result = new ArrayList<>(); for (String userId : userIds) { LineRecordStat stat = cal(all, userId, aStr, bStr, cutted, nowStr); result.add(stat); } return result; } public static LineRecordStat cal(List<LineRecordInfo> all, String userId, String aStr, String bStr, Set<String> cutted, String nowStr) { List<LineRecordInfo> list = new ArrayList<>(); for(LineRecordInfo record : all) { if(record.getUserId().equals(userId)) { list.add(record); String loginSessionId = record.getLoginSessionId(); if(StringUtils.isBlank(loginSessionId)) { // 未结束的会话，需要追加一个当前时间 String tmp = record.getInfo(); String[] splits = tmp.split(","); String last = splits[splits.length - 1]; record.setInfo(tmp + "," + last.substring(0,last.length() - 19) + nowStr); } String info = embellish(record,aStr,bStr,cutted); record.setInfo(info); } } return initStat(list,userId); } /** * 初始化统计信息 * @param list * @param userId * @return */ public static LineRecordStat initStat(List<LineRecordInfo> list, String userId) { LineRecordStat lineRecordStat = new LineRecordStat(); lineRecordStat.setUserId(userId); int onlineDuration = 0; int offlineDuration = 0; int restDuration = 0; for (LineRecordInfo record : list) { String info = record.getInfo(); if(StringUtils.isBlank(info)) { continue; } String[] sections = info.split(","); Date start = null; Date end = null; Integer curr = null; List<LineRecordSection> secList = new ArrayList<>(); for (String section : sections) { String[] splits = section.split("_"); Integer status = Integer.valueOf(splits[0]); Date d = DateUtils.toDateNull(splits[1], DateUtils.TIME_3); if(curr != null && curr.intValue() == status) { end = d; continue; } if(curr == null) { curr = status; start = d; }else { end = d; LineRecordSection lineRecordSection = new LineRecordSection(); lineRecordSection.setStart(start); lineRecordSection.setEnd(end); lineRecordSection.setType(curr); lineRecordSection.setUserId(userId); secList.add(lineRecordSection); curr = status; start = d; end = null; } } if(end != null) { LineRecordSection lineRecordSection = new LineRecordSection(); lineRecordSection.setStart(start); lineRecordSection.setEnd(end); lineRecordSection.setType(curr); lineRecordSection.setUserId(userId); secList.add(lineRecordSection); } for(LineRecordSection sec : secList) { long dur = (sec.getEnd().getTime() - sec.getStart().getTime())/1000L; if(sec.getType() == 1) { onlineDuration += dur; }else if(sec.getType() == 2) { restDuration += dur; }else if(sec.getType() == 3) { offlineDuration += dur; } } } lineRecordStat.setOnlineDuration(onlineDuration); lineRecordStat.setOfflineDuration(offlineDuration); lineRecordStat.setRestDuration(restDuration); return lineRecordStat; } /** * 设置开始结束时间 * @param record * @param aStr * @param bStr * @param cutted * @return */ public static String embellish(LineRecordInfo record, String aStr, String bStr, Set<String> cutted) { boolean isCutted = cutted.contains(record.getLoginSessionId()); if(!isCutted) { return record.getInfo(); } String info = record.getInfo(); // 3_2023-04-11 14:42:38,3_2023-04-11 14:43:03,1_2023-04-11 14:43:21,2_2023-04-11 14:43:28,2_2023-04-11 14:49:57 { String[] sections = info.split(","); String remove = ""; String insert = ""; int cnt = 0; for(String section : sections) { String[] splits = section.split("_"); if(aStr.compareTo(splits[1]) >= 0) { remove += section + ","; insert = splits[0] + "_" + aStr; cnt++; }else { break; } } if(cnt == 0) { }else if(cnt == sections.length) { return null; }else { info = insert + "," + info.replace(remove, ""); } } { String[] sections = info.split(","); String remove = ""; String insert = ""; int cnt = 0; for(int i=sections.length - 1 ; i>-1;i--) { String section = sections[i]; String[] splits = sections[i].split("_"); if(bStr.compareTo(splits[1]) <= 0) { remove = section + "," + remove; insert = splits[0] + "_" + bStr; cnt++; }else { break; } } if(cnt == 0) { }else if(cnt == sections.length) { return null; }else { info = info.replace(remove.substring(0, remove.length()-1), "") + insert; } } return info; } public static int forceNum(Integer i) { if(i == null) { return 0; } return i.intValue(); } }
@Getter@Setter public class LineRecordInfo { private String userId; private String info; private String loginSessionId; } @Getter@Setter public class LineRecordSection { private String userId; private Date start; private Date end; private Integer type; } @Getter@Setter public class LineRecordStat { private String userId; private Integer onlineDuration; private Integer offlineDuration; private Integer restDuration; }
测试：
| 起止时间 | 在线时长 | 小休时长 | 离线时长 | 备注 |
| 2023-04-11 14:05:00 至 2023-04-11 14:39:00 | 600 | 540 | 900 | |
| 2023-04-11 14:00:00 至 2023-04-11 14:40:00 | 600 | 600 | 1200 | |
| 2023-04-11 14:00:01 至 2023-04-11 14:39:59 | 600 | 599 | 1199 | |
| 2023-04-11 15:21:01 至 2023-04-11 15:22:02 | 61 | 0 | 0 | |
| 2023-04-11 14:39:00 至 2023-04-11 15:11:00 | 0 | 120 | 0 |