public Map<String, Object> getPwoTgInfoByDB(String orgCode, String dateStr) { // 1. 参数预处理 if (!StringUtils.hasText(orgCode) || "undefined".equals(orgCode)) { orgCode = rootDept; } if (!StringUtils.hasText(dateStr)) { // 使用Java 8的日期/时间API，更健壮 dateStr = LocalDate.now().format(DateTimeFormatter.ISO_LOCAL_DATE); // 格式: yyyy-MM-dd } // 特定业务逻辑: CmsCode转换 if (orgCode.startsWith("027")) { String cmsCode = pwoTgDao.getCmsCodeBySysCode(orgCode); if (StringUtils.hasText(cmsCode)) { orgCode = cmsCode; } } // 2. 准备SQL查询所需的各种日期格式参数 DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); DateTimeFormatter dbFormatter = DateTimeFormatter.ofPattern("yyyyMMdd"); LocalDate queryDate = LocalDate.parse(dateStr, inputFormatter); Map<String, Object> params = new HashMap<>(); params.put("orgCode", orgCode); params.put("startDateStr", queryDate.format(dbFormatter)); // 例如: "20250627" params.put("endDateStr", queryDate.plusDays(1).format(dbFormatter)); // 例如: "20250628" params.put("lookbackDateStr", queryDate.minusDays(3).format(dbFormatter)); // 例如: "20250624" String dbDateStr = queryDate.format(dbFormatter); // 3. 分别查询 terminal 和 meter 的原始数据 // 查询 Terminal 数据 params.put("source", "terminal"); List<Map<String, Object>> terminalData = pwoTgDao.getPwoTgInfo(params); // 查询 Meter 数据 params.put("source", "meter"); List<Map<String, Object>> meterData = pwoTgDao.getPwoTgInfo(params); // 4. **数据丰富化处理（两步走）** // 第一步: 用计划停电信息丰富数据 enrichWithPlannedStatusOptimized(terminalData, dbDateStr); enrichWithPlannedStatusOptimized(meterData, dbDateStr); // 第二步: 用实际恢复时间丰富数据 (针对所有缺少结束时间的行) enrichWithRecoveryTime(terminalData); // enrichWithRecoversTime(meterData); // 5. 对最终结果进行格式化，以满足前端展示需求 // (假设 PwoUtils.formatDate 执行日期格式转换等操作) JSONArray formattedTerminalData = PwoUtils.formatDate(terminalData, "eventtime", "powerofftime", "powerontime"); JSONArray formattedMeterData = PwoUtils.formatDate(meterData, "eventtime", "powerofftime", "powerontime"); // 6. 组装并返回最终结果 Map<String, Object> result = new HashMap<>(); result.put("terminal", formattedTerminalData); result.put("meter", formattedMeterData); return result; } /** * 【数据丰富化 - 步骤一】 * 使用计划停电信息来丰富原始数据列表。 * 为匹配到的数据行添加 isPlanned, poweroffType, poweroffStartTime, poweroffEndTime 字段。 * * @param dataList 需要被丰富的数据列表 (例如 terminalData 或 meterData) * @param dateStr 查询日期，格式为 "yyyyMMdd" */ private void enrichWithPlannedStatusOptimized(List<Map<String, Object>> dataList, String dateStr) { if (dataList == null || dataList.isEmpty()) { return; } // 1. 收集所有不重复的 lineId，用于一次性数据库查询 List<String> lineIds = dataList.stream() .map(row -> (String) row.get("lineid")) .filter(Objects::nonNull) .distinct() .collect(Collectors.toList()); if (lineIds.isEmpty()) { dataList.forEach(row -> row.put("isPlanned", 0)); // 如果都没有线路ID，则全部标记为非计划 return; } // 2. 一次性从数据库查询所有相关线路在当天的所有计划 List<Map<String, Object>> allPlans = pwoTgDao.getPlansForLinesByDay(lineIds, dateStr); // 3. 将查询到的计划按 lineId 分组存入Map，以实现 O(1) 复杂度的快速查找 Map<String, List<Map<String, Object>>> plansByLineId = allPlans.stream() .filter(plan -> plan.get("lineId") != null) .collect(Collectors.groupingBy(plan -> (String) plan.get("lineId"))); // 定义 eventTime 的解析格式 SimpleDateFormat eventTimeFormat = new SimpleDateFormat("yyyyMMddHHmmss"); // 4. 遍历主数据列表，在内存中进行匹配和逻辑判断 for (Map<String, Object> row : dataList) { String lineId = (String) row.get("lineid"); Date eventTimeStr = (Date) row.get("eventtime"); // 默认设置为0 (非计划) row.put("isPlanned", 0); // 如果行数据缺少关键信息，或者该线路根本没有任何计划，则跳过 if (lineId == null || eventTimeStr == null || !plansByLineId.containsKey(lineId)) { continue; } try { // String replace = eventTimeStr.replace("T", "").replace("Z", ""); // Date eventTime = eventTimeFormat.parse(replace); List<Map<String, Object>> linePlans = plansByLineId.get(lineId); // 检查是否存在一个计划，其开始时间在 eventTime 之后 // (这个逻辑可能需要根据您的业务需求确认，这里保留了您的原始逻辑) boolean hasPlanned = linePlans.stream() .anyMatch(plan -> { Object startTimeObj = plan.get("poweroffStartTime"); if (startTimeObj instanceof Date) { return eventTimeStr.after((Date) startTimeObj); } return false; }); if (hasPlanned) { row.put("isPlanned", 1); Map<String, Object> firstPlan = linePlans.get(0); // 假设使用第一个匹配的计划信息 String type = firstPlan.get("poweroffType").toString(); row.put("poweroffType", convertPoweroffType(type)); // 使用辅助方法使代码更清晰 row.put("poweroffStartTime", firstPlan.get("poweroffStartTime")); row.put("poweroffEndTime", firstPlan.get("poweroffEndTime")); } } catch (Exception e) { // 如果事件时间格式错误，打印堆栈信息，便于调试 e.printStackTrace(); } } } /** * 【数据丰富化 - 步骤二】 * 使用模拟量日志来丰富数据，为缺少 `powerOffEndTime` 的行查找恢复时间。 * * @param dataToEnrich 需要被丰富的数据列表 */ private void enrichWithRecoveryTime(List<Map<String, Object>> dataToEnrich) { // 1. 筛选出所有有停电开始时间但没有结束时间的行 (这部分逻辑不变) List<Map<String, Object>> targets = dataToEnrich.stream() .filter(row -> row.get("powerofftime") != null && row.get("powerontime") == null) .collect(Collectors.toList()); if (targets.isEmpty()) { return; } DateTimeFormatter utcParser = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").withZone(ZoneId.of("Asia/Shanghai")); // 2. 准备批量查询的精确条件 (esn, startTime) List<Map<String, Object>> searchCriteria = new ArrayList<>(); for (Map<String, Object> targetRow : targets) { Object esnObj = targetRow.get("esn"); Object startTimeObj = targetRow.get("powerofftime"); if (esnObj == null || startTimeObj == null) { continue; } try { Instant powerOffStartInstant; if (startTimeObj instanceof Instant) { powerOffStartInstant = (Instant) startTimeObj; } else if (startTimeObj instanceof Date) { powerOffStartInstant = ((Date) startTimeObj).toInstant(); } else if(startTimeObj instanceof String){ powerOffStartInstant = Instant.from(utcParser.parse((String)startTimeObj)); } else { continue; // 跳过未知类型 } Map<String, Object> criterion = new HashMap<>(); criterion.put("esn", esnObj.toString()); criterion.put("startTime", powerOffStartInstant); // 传入Date对象，MyBatis会处理 searchCriteria.add(criterion); } catch (Exception e) { System.err.println("时间解析失败: " + startTimeObj + "，错误: " + e.getMessage()); } } if (searchCriteria.isEmpty()) { return; } // 3. 执行一次高度优化的数据库批量查询 // 注意：这里调用的是新的DAO方法 List<Map<String, Object>> recoveryEvents = pwoTgDao.findEarliestRecoveryEvents(searchCriteria); if (recoveryEvents.isEmpty()) { return; // 未找到任何恢复事件 } // 4. 将查询结果转换为 Map<String, Map> 以便快速查找 (Key: esn, Value: 恢复事件) // 因为查询结果每个ESN最多只有一条，所以可以直接toMap Map<String, Map<String, Object>> eventsByEsn = recoveryEvents.stream() .collect(Collectors.toMap( event -> (String) event.get("esn"), event -> event, (existingValue, newValue) -> existingValue // 合并 如果key重复，保留已存在的值 )); // 5. 遍历目标行，用查询结果进行数据填充 for (Map<String, Object> targetRow : targets) { String esn = (String) targetRow.get("esn"); if (esn == null) { continue; } // 直接从Map中获取对应的恢复事件 Map<String, Object> recoveryEvent = eventsByEsn.get(esn); if (recoveryEvent != null) { // 注意：checkVoltageRecovery 已经在SQL中隐式执行了 (IS NOT NULL) // 如果还有更复杂的逻辑，可以在这里检查 // if (checkVoltageRecovery(recoveryEvent)) { ... } targetRow.put("powerontime", recoveryEvent.get("time")); // time是Timestamp对象 targetRow.put("alarm", "poweron"); } } } /** * 辅助方法：将停电类型代码转换为中文描述 * @param typeCode 停电类型代码 * @return 对应的中文描述 */ private String convertPoweroffType(String typeCode) { switch (typeCode) { case "01": return "计划停电"; case "02": return "故障停电"; case "06": return "有序停电"; case "07": return "临时停电"; case "08": return "超电网供电能力停电"; case "10": return "表箱（计）作业停电"; default: return "其他"; } } <select id="getPwoTgInfo" resultType="java.util.Map" parameterType="java.util.Map"> SELECT final.esn, final.mrid, final.name, final.latest_alarm AS alarm, final.lineId, final.lineName, final.orgNo, final.source, final.type, final.latest_event_time AS eventTime, final.dayEventCnt, final.latest_time AS time, CASE WHEN final.latest_alarm = 'poweron' THEN final.latest_event_time ELSE NULL END AS powerOnTime, CASE WHEN final.latest_alarm = 'poweroff' THEN final.latest_event_time::varchar WHEN final.latest_alarm = 'poweron' THEN ( SELECT t.event_time::varchar FROM power_off_tg t WHERE t.mrid = final.mrid AND t.alarm = 'poweroff' AND t.event_time &lt; final.latest_event_time AND t.event_time > (final.latest_event_time - interval '10 minutes') ORDER BY t.event_time DESC LIMIT 1 ) ELSE NULL END AS powerOffTime FROM ( SELECT mrid, MAX(CASE WHEN ts_rank = 1 THEN esn END) AS esn, MAX(CASE WHEN ts_rank = 1 THEN name END) AS name, MAX(CASE WHEN ts_rank = 1 THEN lineId END) AS lineId, MAX(CASE WHEN ts_rank = 1 THEN lineName END) AS lineName, MAX(CASE WHEN ts_rank = 1 THEN orgNo END) AS orgNo, MAX(CASE WHEN ts_rank = 1 THEN source END) AS source, MAX(CASE WHEN ts_rank = 1 THEN type END) AS type, MAX(CASE WHEN ts_rank = 1 THEN eventTime END) AS latest_event_time, MAX(CASE WHEN ts_rank = 1 THEN alarm END) AS latest_alarm, MAX(CASE WHEN ts_rank = 1 THEN time END) AS latest_time, MAX(dayEventCnt) AS dayEventCnt FROM ( SELECT base_events.*, DENSE_RANK() OVER (PARTITION BY mrid ORDER BY eventTime DESC) AS ts_rank, COUNT(1) OVER (PARTITION BY mrid) AS dayEventCnt FROM ( SELECT time, esn, mrid, name, alarm, line_id AS lineId, line_name AS lineName, org_no AS orgNo, source, type, event_time::timestamp AS eventTime FROM power_off_tg WHERE event_time::timestamp >= #{lookbackDateStr}::timestamp AND org_no LIKE CONCAT(#{orgCode}, '%') AND source = #{source} ) AS base_events WHERE eventTime >= #{startDateStr}::timestamp AND eventTime &lt; #{endDateStr}::timestamp ) AS ranked_events WHERE ts_rank = 1 GROUP BY mrid ) AS final ORDER BY final.latest_event_time DESC; </select> <select id="getPlansForLinesByDay" resultType="java.util.Map"> SELECT pow.poweroff_type poweroffType, pow.poweroff_start_time poweroffStartTime, pow.poweroff_end_time poweroffEndTime, line.ztxlzy_id lineId FROM poweroutage_info POW INNER JOIN line_info line ON pow.line_id = line.yxxl_id WHERE DATE(poweroff_start_time) = STR_TO_DATE(#{dateStr}, '%Y%m%d') AND line.ztxlzy_id IN <foreach item="item" collection="lineIds" open="(" separator="," close=")"> #{item} </foreach> </select> <select id="findEarliestRecoveryEvents" resultType="java.util.Map"> SELECT j1.esn, j1.time, j1.phv_phsa, j1.phv_phsb, j1.phv_phsc FROM jiaocai j1 INNER JOIN ( SELECT esn, MIN(time) as earliest_recovery_time FROM jiaocai WHERE <if test="criteriaList != null and !criteriaList.isEmpty()"> ( <foreach collection="criteriaList" item="c" separator=" OR "> (esn = #{c.esn} AND time > #{c.startTime, jdbcType=TIMESTAMP}) </foreach> ) </if> AND phv_phsa IS NOT NULL AND phv_phsb IS NOT NULL AND phv_phsc IS NOT NULL GROUP BY esn ) AS earliest_times ON j1.esn = earliest_times.esn AND j1.time = earliest_times.earliest_recovery_time WHERE j1.phv_phsa IS NOT NULL AND j1.phv_phsb IS NOT NULL AND j1.phv_phsc IS NOT NULL </select> 当前这个接口逻辑较复杂 查询的数据库的表是两个不同的库来回查询 power_off_tg和jiaocai是postgreSQL消费kafka的数据 poweroutage_info是从mysql中的数据 现在需要实现实时查询的需求 直接查询power_off_tg的表太慢了