fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）

longz

2026-08-05 fbce3ff8d28f55fdc8e7c6e446534dcfa181f488

 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java

@@ -3150,66 +3150,217 @@

    @Override
    public R<PageInfo<EnterpriseChargingAnalysisVO>> enterpriseChargingAnalysis(EnterpriseChargingAnalysisQuery query) {
        PageInfo<EnterpriseChargingAnalysisVO> pageInfo = new PageInfo<>(query.getPageCurr(), query.getPageSize());
        List<EnterpriseChargingAnalysisVO> list = this.baseMapper.enterpriseChargingAnalysis(pageInfo, query);
        if (list == null || list.isEmpty()) {
        int pageCurr = query.getPageCurr() == null ? 1 : query.getPageCurr();
        int pageSize = query.getPageSize() == null ? 50 : query.getPageSize();
        PageInfo<EnterpriseChargingAnalysisVO> pageInfo = new PageInfo<>(pageCurr, pageSize);
        // 逆反查聚合（不再跨分片 JOIN，构建块见 TChargingOrderMapper.xml enterpriseInvoiceRows 等）：
        //   1) t_order_invoice 单表查企业发票（10 片按 app_user_id 分片，抬头级条件可下推）
        //   2) t_order_invoice_detail 按 order_invoice_id IN 路由查充电明细
        //   3) 命中订单级条件（站点/时间等）→ 命中订单 id
        //   4) t_charging_order 按 id IN 走主键取 start_time/site_id
        //   5) t_site 单表取站点名
        //   6) 按公司聚合 + 手工分页（不再传 IPage，避免分页拦截器生成无法下推的全量 COUNT）
        List<EnterpriseChargingAggRow.Invoice> invoices = this.baseMapper.enterpriseInvoiceRows(query);
        if (invoices == null || invoices.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<String> companyNames = list.stream()
                .map(EnterpriseChargingAnalysisVO::getCompanyName)
        Map<Long, List<EnterpriseChargingAggRow.Detail>> detailsByInvoice = new HashMap<>();
        Map<Long, EnterpriseChargingAggRow.Detail> detailByOrder = new LinkedHashMap<>();
        List<Long> invoiceIds = invoices.stream()
                .map(EnterpriseChargingAggRow.Invoice::getId)
                .filter(Objects::nonNull)
                .distinct()
                .collect(Collectors.toList());
        Map<String, List<Map<String, Object>>> topSitesByCompany = groupByCompany(
                this.baseMapper.enterpriseTopSitesByCompanies(query, companyNames));
        Map<String, List<Map<String, Object>>> chargingTimesByCompany = groupByCompany(
                this.baseMapper.enterpriseChargingTimesByCompanies(query, companyNames));
        for (EnterpriseChargingAnalysisVO row : list) {
            String companyKey = companyKey(row.getCompanyName(), row.getSocialCreditCode());
            List<Map<String, Object>> topSites = topSitesByCompany.getOrDefault(companyKey, Collections.emptyList());
            String topSiteNames = topSites.stream()
                    .sorted(Comparator.comparing((Map<String, Object> item) -> ((Number) item.getOrDefault("counts", 0)).longValue()).reversed())
                    .limit(3)
                    .map(item -> Objects.toString(item.get("siteName"), ""))
                    .filter(org.springframework.util.StringUtils::hasText)
                    .collect(Collectors.joining("、"));
            row.setTopSiteNames(topSiteNames);
            List<LocalDateTime> chargingTimes = chargingTimesByCompany.getOrDefault(companyKey, Collections.emptyList()).stream()
                    .map(item -> toLocalDateTime(item.get("startTime")))
                    .filter(Objects::nonNull)
                    .sorted()
                    .collect(Collectors.toList());
            row.setAverageChargingInterval(calculateAverageChargingInterval(chargingTimes));
        for (int start = 0; start < invoiceIds.size(); start += ENTERPRISE_INVOICE_BATCH_SIZE) {
            int end = Math.min(start + ENTERPRISE_INVOICE_BATCH_SIZE, invoiceIds.size());
            List<EnterpriseChargingAggRow.Detail> details =
                    this.baseMapper.enterpriseInvoiceDetailRows(invoiceIds.subList(start, end));
            if (details == null) {
                continue;
            }
            for (EnterpriseChargingAggRow.Detail detail : details) {
                if (detail.getOrderId() == null) {
                    continue;
                }
                detailsByInvoice.computeIfAbsent(detail.getInvoiceId(), k -> new ArrayList<>()).add(detail);
                detailByOrder.putIfAbsent(detail.getOrderId(), detail);
            }
        }
        pageInfo.setRecords(list);
        if (detailByOrder.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<Long> candidateOrderIds = new ArrayList<>(detailByOrder.keySet());
        boolean orderFilter = hasEnterpriseOrderFilter(query);
        Set<Long> matchedOrderIds = null;
        if (orderFilter) {
            matchedOrderIds = new HashSet<>();
            for (int start = 0; start < candidateOrderIds.size(); start += ENTERPRISE_ORDER_ID_BATCH_SIZE) {
                int end = Math.min(start + ENTERPRISE_ORDER_ID_BATCH_SIZE, candidateOrderIds.size());
                List<Long> matched = this.baseMapper.enterpriseAnalysisMatchingOrderIds(
                        candidateOrderIds.subList(start, end), query);
                if (matched != null) {
                    matchedOrderIds.addAll(matched);
                }
            }
            if (matchedOrderIds.isEmpty()) {
                pageInfo.setRecords(Collections.emptyList());
                return R.ok(pageInfo);
            }
        }
        List<Long> effectiveOrderIds = orderFilter ? new ArrayList<>(matchedOrderIds) : candidateOrderIds;
        Map<Long, EnterpriseChargingAggRow.Order> orderMap = new HashMap<>();
        for (int start = 0; start < effectiveOrderIds.size(); start += ENTERPRISE_ORDER_ID_BATCH_SIZE) {
            int end = Math.min(start + ENTERPRISE_ORDER_ID_BATCH_SIZE, effectiveOrderIds.size());
            List<EnterpriseChargingAggRow.Order> orders =
                    this.baseMapper.enterpriseAnalysisChargingOrders(effectiveOrderIds.subList(start, end));
            if (orders == null) {
                continue;
            }
            for (EnterpriseChargingAggRow.Order order : orders) {
                orderMap.put(order.getOrderId(), order);
            }
        }
        Set<Long> siteIds = orderMap.values().stream()
                .map(EnterpriseChargingAggRow.Order::getSiteId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        Map<Long, String> siteNameMap = new HashMap<>();
        if (!siteIds.isEmpty()) {
            List<EnterpriseChargingAggRow.Site> sites = this.baseMapper.enterpriseAnalysisSites(new ArrayList<>(siteIds));
            if (sites != null) {
                for (EnterpriseChargingAggRow.Site site : sites) {
                    siteNameMap.put(site.getSiteId(), site.getSiteName());
                }
            }
        }
        Map<String, EnterpriseCompanyAgg> companyAggs = new LinkedHashMap<>();
        for (EnterpriseChargingAggRow.Invoice invoice : invoices) {
            List<EnterpriseChargingAggRow.Detail> details =
                    detailsByInvoice.getOrDefault(invoice.getId(), Collections.emptyList());
            if (details.isEmpty()) {
                // 无充电明细（order_type=1）的发票不计，等同原 INNER JOIN tod.order_type=1 语义
                continue;
            }
            List<EnterpriseChargingAggRow.Order> invoiceOrders = new ArrayList<>();
            BigDecimal detailAmount = BigDecimal.ZERO;
            for (EnterpriseChargingAggRow.Detail detail : details) {
                if (orderFilter && !matchedOrderIds.contains(detail.getOrderId())) {
                    continue;
                }
                detailAmount = detailAmount.add(nullToZero(detail.getInvoiceAmount()));
                EnterpriseChargingAggRow.Order order = orderMap.get(detail.getOrderId());
                if (order != null) {
                    invoiceOrders.add(order);
                }
            }
            if (orderFilter && invoiceOrders.isEmpty()) {
                // 订单级筛选后该发票无命中订单 → 剔除，等同原 LEFT JOIN + 订单级 WHERE 语义
                continue;
            }
            BigDecimal invoiceAmount = detailAmount.signum() > 0 ? detailAmount : nullToZero(invoice.getTotalAmount());
            String key = companyKey(invoice.getCompanyName(), invoice.getSocialCreditCode());
            EnterpriseCompanyAgg agg = companyAggs.get(key);
            if (agg == null) {
                agg = new EnterpriseCompanyAgg(invoice.getCompanyName(), invoice.getSocialCreditCode(),
                        invoice.getCompanyAddress(), invoice.getCompanyPhone());
                companyAggs.put(key, agg);
            }
            agg.invoiceCount++;
            agg.invoiceAmount = agg.invoiceAmount.add(invoiceAmount);
            for (EnterpriseChargingAggRow.Order order : invoiceOrders) {
                if (order.getStartTime() != null) {
                    agg.startTimes.add(order.getStartTime());
                    if (agg.latestChargingTime == null || order.getStartTime().isAfter(agg.latestChargingTime)) {
                        agg.latestChargingTime = order.getStartTime();
                    }
                }
                if (order.getSiteId() != null) {
                    agg.siteOrderIds.computeIfAbsent(order.getSiteId(), k -> new HashSet<>()).add(order.getOrderId());
                }
            }
        }
        if (companyAggs.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<EnterpriseChargingAnalysisVO> all = companyAggs.values().stream()
                .sorted(Comparator.comparingLong((EnterpriseCompanyAgg agg) -> agg.invoiceCount).reversed())
                .map(agg -> buildEnterpriseAnalysisVO(agg, siteNameMap))
                .collect(Collectors.toList());
        pageInfo.setTotal(all.size());
        long offset = (long) (pageCurr - 1L) * pageSize;
        if (offset >= all.size()) {
            pageInfo.setRecords(Collections.emptyList());
        } else {
            int toIndex = (int) Math.min(offset + pageSize, all.size());
            pageInfo.setRecords(all.subList((int) offset, toIndex));
        }
        return R.ok(pageInfo);
    }

    private Map<String, List<Map<String, Object>>> groupByCompany(List<Map<String, Object>> rows) {
        if (rows == null || rows.isEmpty()) {
            return Collections.emptyMap();
    private boolean hasEnterpriseOrderFilter(EnterpriseChargingAnalysisQuery query) {
        return query.getSiteType() != null
                || query.getPartnerId() != null
                || query.getSiteId() != null
                || query.getCreateTime1() != null
                || query.getCreateTime2() != null
                || (query.getCode() != null && !query.getCode().isEmpty())
                || query.getOrderType() != null
                || (query.getPhone() != null && !query.getPhone().isEmpty())
                || query.getStatus() != null
                || (query.getPartnerName() != null && !query.getPartnerName().isEmpty())
                || query.getOrderSource() != null
                || query.getStartTime1() != null
                || query.getStartTime2() != null
                || query.getEndTime1() != null
                || query.getEndTime2() != null;
    }

    private EnterpriseChargingAnalysisVO buildEnterpriseAnalysisVO(EnterpriseCompanyAgg agg,
                                                                  Map<Long, String> siteNameMap) {
        EnterpriseChargingAnalysisVO vo = new EnterpriseChargingAnalysisVO();
        vo.setCompanyName(agg.companyName);
        vo.setSocialCreditCode(agg.socialCreditCode);
        vo.setCompanyAddress(agg.companyAddress);
        vo.setCompanyPhone(agg.companyPhone);
        vo.setInvoiceCount(agg.invoiceCount);
        vo.setInvoiceAmount(agg.invoiceAmount);
        vo.setLatestChargingTime(agg.latestChargingTime);
        String topSiteNames = agg.siteOrderIds.entrySet().stream()
                .sorted(Comparator.comparingInt((Map.Entry<Long, Set<Long>> e) -> e.getValue().size()).reversed())
                .limit(3)
                .map(e -> siteNameMap.getOrDefault(e.getKey(), ""))
                .filter(org.springframework.util.StringUtils::hasText)
                .collect(Collectors.joining("、"));
        vo.setTopSiteNames(topSiteNames);
        vo.setAverageChargingInterval(calculateAverageChargingInterval(new ArrayList<>(agg.startTimes)));
        return vo;
    }

    private static class EnterpriseCompanyAgg {
        private final String companyName;
        private final String socialCreditCode;
        private final String companyAddress;
        private final String companyPhone;
        private long invoiceCount;
        private BigDecimal invoiceAmount = BigDecimal.ZERO;
        private LocalDateTime latestChargingTime;
        private final Map<Long, Set<Long>> siteOrderIds = new HashMap<>();
        private final TreeSet<LocalDateTime> startTimes = new TreeSet<>();

        private EnterpriseCompanyAgg(String companyName, String socialCreditCode,
                                     String companyAddress, String companyPhone) {
            this.companyName = companyName;
            this.socialCreditCode = socialCreditCode;
            this.companyAddress = companyAddress;
            this.companyPhone = companyPhone;
        }
        return rows.stream().collect(Collectors.groupingBy(row -> companyKey(
                Objects.toString(row.get("companyName"), null),
                Objects.toString(row.get("socialCreditCode"), ""))));
    }

    private String companyKey(String companyName, String socialCreditCode) {
        return Objects.toString(companyName, "") + "\u0000" + Objects.toString(socialCreditCode, "");
    }

    private LocalDateTime toLocalDateTime(Object value) {
        if (value instanceof LocalDateTime) {
            return (LocalDateTime) value;
        }
        if (value instanceof java.sql.Timestamp) {
            return ((java.sql.Timestamp) value).toLocalDateTime();
        }
        if (value instanceof Date) {
            return LocalDateTime.ofInstant(((Date) value).toInstant(), ZoneId.systemDefault());
        }
        return null;
    }

    @Override