From 9f20477f45b8e15a3b456fb183a5aab4b42eb9a5 Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期二, 04 八月 2026 15:53:06 +0800
Subject: [PATCH] 大屏数据缓存
---
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java | 269 +++++++++++++++++++++---
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java | 93 ++++++++
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java | 10
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java | 29 ++
sql/large_screen_stat.sql | 91 ++++++++
ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | 124 +++++++++++
6 files changed, 578 insertions(+), 38 deletions(-)
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
index cf2ff8b..dfb9a2b 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
@@ -207,6 +207,35 @@
Map<String, Object> getLargeScreenSummary(@Param("siteIds") List<Integer> siteIds);
+ // ==================== 大屏预聚合表 t_large_screen_site_stat ====================
+ // 任务用: 按月切片聚合(与 getLargeScreenSummary 相同口径, 按站点分组)
+ List<Map<String, Object>> monthSliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
+
+ // 任务用: 按日切片聚合(与 getLargeScreenChargingPower 相同口径, 按站点分组)
+ List<Map<String, Object>> daySliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
+
+ // 任务用: 注册用户总数
+ Long globalRegisteredUsers();
+
+ // 任务用: 批量写入聚合行(REPLACE INTO)
+ int replaceSiteStatBatch(@Param("rows") List<Map<String, Object>> rows);
+
+ // 任务用: 表行数(判断是否需要启动回填)
+ Long statTableCount();
+
+ // 接口用: 累计指标(M 行汇总)
+ Map<String, Object> summaryFromStat(@Param("siteIds") List<Integer> siteIds);
+
+ // 接口用: 近 N 月收入趋势(M 行按 period 分组)
+ List<SixChargingDto> incomeTrendFromStat(@Param("siteIds") List<Integer> siteIds, @Param("sixBefore") String sixBefore);
+
+ // 接口用: 日功率曲线(D 行按 period 分组)
+ List<Map<String, Object>> powerFromStat(@Param("siteIds") List<Integer> siteIds,
+ @Param("dayStart") String dayStart, @Param("dayEnd") String dayEnd);
+
+ // 接口用: 注册用户数(G 行)
+ Long registeredFromStat();
+
SixChargingDto getLargeScreenSettlementIncome(@Param("siteIdsCsv") String siteIdsCsv,
@Param("startTime") String startTime,
@Param("endTime") String endTime);
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java
index c06525b..88d409f 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java
@@ -320,6 +320,16 @@
List<LargeChargingPowerVo> getLargeScreenChargingPower(List<Integer> siteIds, LocalDateTime startTime, LocalDateTime endTime);
+ /**
+ * 重建大屏预聚合表(每日全量: 全部月份切片 + 近90天日切片 + 全局注册用户数)
+ */
+ void rebuildLargeScreenStat();
+
+ /**
+ * 刷新大屏预聚合表(每10分钟: 当月切片 + 今日日切片 + 全局注册用户数)
+ */
+ void refreshLargeScreenStat();
+
List<Map<String, Object>> getHourType(List<Integer> siteIds, ChargingDetailQueryDto statisticsQueryDto);
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
index 02c93a4..992e9c3 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
@@ -103,6 +103,7 @@
import org.springframework.transaction.support.TransactionSynchronization;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.transaction.support.TransactionTemplate;
+import org.springframework.util.DigestUtils;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
@@ -111,6 +112,7 @@
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
+import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.*;
@@ -144,6 +146,12 @@
private static final int ENTERPRISE_INVOICE_BATCH_SIZE = 500;
private static final int ENTERPRISE_ORDER_ID_BATCH_SIZE = 500;
private static final int CHARGING_PERIOD_ACCOUNTING_BATCH_SIZE = 500;
+ private static final String LARGE_SCREEN_CACHE_PREFIX = "large:screen:";
+ private static final long LARGE_SCREEN_CACHE_SECONDS = 60L;
+ private static final LocalDate LARGE_SCREEN_STAT_DATA_START = LocalDate.of(2020, 1, 1);
+ private static final DateTimeFormatter LARGE_SCREEN_MONTH_FMT = DateTimeFormatter.ofPattern("yyyy-MM");
+ private static final DateTimeFormatter LARGE_SCREEN_DAY_FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd");
+ private static final DateTimeFormatter LARGE_SCREEN_SQL_FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
@Override
public ReservationRecommendationVO getReservationRecommendation(Integer siteId) {
@@ -6693,58 +6701,243 @@
@Override
public Map<String, Object> getLargeScreenSummary(List<Integer> siteIds) {
- return this.baseMapper.getLargeScreenSummary(siteIds);
+ String cacheKey = largeScreenCacheKey("summary", siteIds);
+ Map<String, Object> cached = redisService.getCacheObject(cacheKey);
+ if (cached != null) {
+ return cached;
+ }
+ Map<String, Object> result = new HashMap<>();
+ try {
+ Map<String, Object> summary = this.baseMapper.summaryFromStat(siteIds);
+ if (summary != null) {
+ result.putAll(summary);
+ }
+ Long registered = this.baseMapper.registeredFromStat();
+ result.put("registeredUserCount", registered == null ? 0L : registered);
+ } catch (Exception e) {
+ log.warn("大屏累计指标读取预聚合表失败, siteIds={}", siteIds, e);
+ }
+ Arrays.asList("validOrderCount", "chargingUserCount", "registeredUserCount",
+ "totalChargingCapacity", "electricityFee", "serviceFee", "totalIncome")
+ .forEach(key -> result.putIfAbsent(key, 0));
+ redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS);
+ return result;
}
@Override
public List<SixChargingDto> getLargeScreenIncomeTrend(List<Integer> siteIds, LocalDate sixBefore) {
- String siteIdsCsv = toSiteIdsCsv(siteIds);
- List<SixChargingDto> result = new ArrayList<>();
- LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
- LocalDate month = sixBefore.withDayOfMonth(1);
- while (!month.isAfter(currentMonth)) {
- LocalDate nextMonth = month.plusMonths(1);
- SixChargingDto income = this.baseMapper.getLargeScreenCurrentMonthIncome(
- siteIdsCsv, month.toString(), nextMonth.toString());
- if (income == null) {
- income = new SixChargingDto();
- }
- income.setMonth(String.format("%02d", month.getMonthValue()));
- if (income.getElectrovalence() == null) {
- income.setElectrovalence(BigDecimal.ZERO);
- }
- if (income.getServiceCharge() == null) {
- income.setServiceCharge(BigDecimal.ZERO);
- }
- result.add(income);
- month = nextMonth;
+ String cacheKey = largeScreenCacheKey("incomeTrend", siteIds);
+ List<SixChargingDto> cached = redisService.getCacheObject(cacheKey);
+ if (cached != null) {
+ return cached;
}
+ LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
+ List<SixChargingDto> result = new ArrayList<>();
+ try {
+ String sixBeforeMonth = sixBefore.withDayOfMonth(1).format(LARGE_SCREEN_MONTH_FMT);
+ List<SixChargingDto> incomeList = this.baseMapper.incomeTrendFromStat(siteIds, sixBeforeMonth);
+ Map<String, SixChargingDto> incomeByMonth = new HashMap<>();
+ if (incomeList != null) {
+ for (SixChargingDto income : incomeList) {
+ if (income != null && income.getMonth() != null) {
+ incomeByMonth.put(income.getMonth(), income);
+ }
+ }
+ }
+ LocalDate month = sixBefore.withDayOfMonth(1);
+ while (!month.isAfter(currentMonth)) {
+ SixChargingDto income = incomeByMonth.get(month.format(LARGE_SCREEN_MONTH_FMT));
+ if (income == null) {
+ income = new SixChargingDto();
+ }
+ income.setMonth(String.format("%02d", month.getMonthValue()));
+ if (income.getElectrovalence() == null) {
+ income.setElectrovalence(BigDecimal.ZERO);
+ }
+ if (income.getServiceCharge() == null) {
+ income.setServiceCharge(BigDecimal.ZERO);
+ }
+ result.add(income);
+ month = month.plusMonths(1);
+ }
+ } catch (Exception e) {
+ log.warn("大屏收入趋势读取预聚合表失败, siteIds={}, sixBefore={}", siteIds, sixBefore, e);
+ LocalDate month = sixBefore.withDayOfMonth(1);
+ while (!month.isAfter(currentMonth)) {
+ SixChargingDto income = new SixChargingDto();
+ income.setMonth(String.format("%02d", month.getMonthValue()));
+ income.setElectrovalence(BigDecimal.ZERO);
+ income.setServiceCharge(BigDecimal.ZERO);
+ result.add(income);
+ month = month.plusMonths(1);
+ }
+ }
+ redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS);
return result;
}
@Override
public List<LargeChargingPowerVo> getLargeScreenChargingPower(List<Integer> siteIds, LocalDateTime startTime, LocalDateTime endTime) {
- DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
- String siteIdsCsv = toSiteIdsCsv(siteIds);
- List<LargeChargingPowerVo> result = new ArrayList<>();
- LocalDateTime dayStart = startTime;
- while (dayStart.isBefore(endTime)) {
- LocalDateTime dayEnd = dayStart.plusDays(1);
- Map<String, Object> aggregate = this.baseMapper.getLargeScreenChargingPower(
- siteIdsCsv, dayStart.format(formatter), dayEnd.format(formatter));
- BigDecimal totalPower = decimalValue(aggregate, "total_power");
- BigDecimal orderCount = decimalValue(aggregate, "order_count");
- BigDecimal power = orderCount.signum() == 0 ? BigDecimal.ZERO
- : totalPower.divide(orderCount, 0, RoundingMode.HALF_UP);
- LargeChargingPowerVo item = new LargeChargingPowerVo();
- item.setTime(dayStart.toLocalDate().toString());
- item.setPower(power == null ? BigDecimal.ZERO : power);
- result.add(item);
- dayStart = dayEnd;
+ String cacheKey = largeScreenCacheKey("power", siteIds);
+ List<LargeChargingPowerVo> cached = redisService.getCacheObject(cacheKey);
+ if (cached != null) {
+ return cached;
}
+ List<LargeChargingPowerVo> result = new ArrayList<>();
+ try {
+ String dayStart = startTime.toLocalDate().format(LARGE_SCREEN_DAY_FMT);
+ String dayEnd = endTime.toLocalDate().format(LARGE_SCREEN_DAY_FMT);
+ List<Map<String, Object>> dayAggs = this.baseMapper.powerFromStat(siteIds, dayStart, dayEnd);
+ if (dayAggs != null) {
+ for (Map<String, Object> agg : dayAggs) {
+ BigDecimal totalPower = decimalValue(agg, "total_power");
+ BigDecimal orderCount = decimalValue(agg, "order_count");
+ BigDecimal power = orderCount.signum() == 0 ? BigDecimal.ZERO
+ : totalPower.divide(orderCount, 0, RoundingMode.HALF_UP);
+ LargeChargingPowerVo item = new LargeChargingPowerVo();
+ Object day = agg.get("day");
+ item.setTime(day == null ? null : String.valueOf(day));
+ item.setPower(power);
+ result.add(item);
+ }
+ }
+ } catch (Exception e) {
+ log.warn("大屏功率曲线读取预聚合表失败, siteIds={}", siteIds, e);
+ }
+ redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS);
return result;
}
+ @Override
+ public void rebuildLargeScreenStat() {
+ log.info("大屏预聚合表全量重建开始");
+ long start = System.currentTimeMillis();
+ // 1. 月度切片全量(数据起始月 ~ 当前月), 每次切片一次查询, 避免单条大查询超时
+ LocalDate month = LARGE_SCREEN_STAT_DATA_START.withDayOfMonth(1);
+ LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
+ while (!month.isAfter(currentMonth)) {
+ writeMonthSlice(month, month.plusMonths(1));
+ month = month.plusMonths(1);
+ }
+ // 2. 近90天日切片
+ LocalDate day = LocalDate.now().minusDays(89);
+ LocalDate today = LocalDate.now();
+ while (!day.isAfter(today)) {
+ writeDaySlice(day);
+ day = day.plusDays(1);
+ }
+ // 3. 全局注册用户数
+ writeGlobal();
+ log.info("大屏预聚合表全量重建完成, 耗时 {}ms", System.currentTimeMillis() - start);
+ }
+
+ @Override
+ public void refreshLargeScreenStat() {
+ long start = System.currentTimeMillis();
+ LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
+ writeMonthSlice(currentMonth, currentMonth.plusMonths(1));
+ writeDaySlice(LocalDate.now());
+ writeGlobal();
+ log.info("大屏预聚合表刷新完成, 耗时 {}ms", System.currentTimeMillis() - start);
+ }
+
+ /**
+ * 写入一个月度切片聚合(M 行)
+ */
+ private void writeMonthSlice(LocalDate monthStart, LocalDate monthEnd) {
+ List<Map<String, Object>> rows = this.baseMapper.monthSliceAgg(
+ monthStart.atStartOfDay().format(LARGE_SCREEN_SQL_FMT),
+ monthEnd.atStartOfDay().format(LARGE_SCREEN_SQL_FMT));
+ if (rows == null || rows.isEmpty()) {
+ return;
+ }
+ List<Map<String, Object>> statRows = new ArrayList<>();
+ for (Map<String, Object> row : rows) {
+ if (row == null || row.get("site_id") == null) {
+ continue;
+ }
+ BigDecimal electricFee = decimalValue(row, "electric_fee");
+ BigDecimal serviceFee = decimalValue(row, "service_fee");
+ Map<String, Object> stat = new HashMap<>();
+ stat.put("statType", "M");
+ stat.put("period", monthStart.format(LARGE_SCREEN_MONTH_FMT));
+ stat.put("siteId", row.get("site_id"));
+ stat.put("orderCount", decimalValue(row, "order_count"));
+ stat.put("userCount", decimalValue(row, "user_count"));
+ stat.put("capacity", decimalValue(row, "capacity"));
+ stat.put("electricFee", electricFee);
+ stat.put("serviceFee", serviceFee);
+ stat.put("income", electricFee.add(serviceFee));
+ stat.put("totalPower", BigDecimal.ZERO);
+ stat.put("registeredUserCount", 0L);
+ statRows.add(stat);
+ }
+ if (!statRows.isEmpty()) {
+ this.baseMapper.replaceSiteStatBatch(statRows);
+ }
+ }
+
+ /**
+ * 写入一个日切片聚合(D 行)
+ */
+ private void writeDaySlice(LocalDate day) {
+ List<Map<String, Object>> rows = this.baseMapper.daySliceAgg(
+ day.atStartOfDay().format(LARGE_SCREEN_SQL_FMT),
+ day.plusDays(1).atStartOfDay().format(LARGE_SCREEN_SQL_FMT));
+ if (rows == null || rows.isEmpty()) {
+ return;
+ }
+ List<Map<String, Object>> statRows = new ArrayList<>();
+ for (Map<String, Object> row : rows) {
+ if (row == null || row.get("site_id") == null) {
+ continue;
+ }
+ Map<String, Object> stat = new HashMap<>();
+ stat.put("statType", "D");
+ stat.put("period", day.format(LARGE_SCREEN_DAY_FMT));
+ stat.put("siteId", row.get("site_id"));
+ stat.put("orderCount", decimalValue(row, "order_count"));
+ stat.put("userCount", BigDecimal.ZERO);
+ stat.put("capacity", BigDecimal.ZERO);
+ stat.put("electricFee", BigDecimal.ZERO);
+ stat.put("serviceFee", BigDecimal.ZERO);
+ stat.put("income", BigDecimal.ZERO);
+ stat.put("totalPower", decimalValue(row, "total_power"));
+ stat.put("registeredUserCount", 0L);
+ statRows.add(stat);
+ }
+ if (!statRows.isEmpty()) {
+ this.baseMapper.replaceSiteStatBatch(statRows);
+ }
+ }
+
+ /**
+ * 写入全局注册用户数(G 行)
+ */
+ private void writeGlobal() {
+ Long registered = this.baseMapper.globalRegisteredUsers();
+ Map<String, Object> stat = new HashMap<>();
+ stat.put("statType", "G");
+ stat.put("period", "ALL");
+ stat.put("siteId", 0);
+ stat.put("orderCount", 0L);
+ stat.put("userCount", 0L);
+ stat.put("capacity", BigDecimal.ZERO);
+ stat.put("electricFee", BigDecimal.ZERO);
+ stat.put("serviceFee", BigDecimal.ZERO);
+ stat.put("income", BigDecimal.ZERO);
+ stat.put("totalPower", BigDecimal.ZERO);
+ stat.put("registeredUserCount", registered == null ? 0L : registered);
+ this.baseMapper.replaceSiteStatBatch(Collections.singletonList(stat));
+ }
+
+ private String largeScreenCacheKey(String endpoint, List<Integer> siteIds) {
+ String idPart = siteIds == null ? "all" : DigestUtils.md5DigestAsHex(
+ siteIds.stream().sorted().map(String::valueOf).collect(Collectors.joining(","))
+ .getBytes(StandardCharsets.UTF_8));
+ return LARGE_SCREEN_CACHE_PREFIX + endpoint + ":" + idPart;
+ }
+
private BigDecimal decimalValue(Map<String, Object> values, String key) {
if (values == null) {
return BigDecimal.ZERO;
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java
new file mode 100644
index 0000000..539f587
--- /dev/null
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java
@@ -0,0 +1,93 @@
+package com.ruoyi.order.util.task;
+
+import com.ruoyi.order.mapper.TChargingOrderMapper;
+import com.ruoyi.order.service.TChargingOrderService;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.boot.context.event.ApplicationReadyEvent;
+import org.springframework.boot.web.context.WebServerInitializedEvent;
+import org.springframework.context.ApplicationListener;
+import org.springframework.context.event.EventListener;
+import org.springframework.scheduling.annotation.Scheduled;
+import org.springframework.stereotype.Component;
+
+import javax.annotation.Resource;
+
+/**
+ * 大屏预聚合表 t_large_screen_site_stat 定时任务
+ *
+ * 背景: /large-screen/* 接口原 SQL 在 MyCat 分表下按 site_id 过滤会广播扫描
+ * 50 张 t_charging_order 物理表且跨分片 join, 导致读超时。
+ * 本任务按"月/日/全局"切片把聚合结果写入预聚合表(全局表), 接口改为读该表:
+ * 1. 每日 00:30 全量重建(按月切片逐片查询, 单次查询有界, 规避 MyCat 读超时)
+ * 2. 每 10 分钟刷新当月/当日/全局行, 保证大屏当前月、当日数据近实时
+ * 3. 启动时检测表为空则异步全量回填, 避免发布后接口无数据
+ */
+@Slf4j
+@Component
+public class LargeScreenStatJob implements ApplicationListener<WebServerInitializedEvent> {
+
+ private Integer port;
+
+ @Resource
+ private TChargingOrderService chargingOrderService;
+
+ @Resource
+ private TChargingOrderMapper chargingOrderMapper;
+
+ @Override
+ public void onApplicationEvent(WebServerInitializedEvent event) {
+ port = event.getWebServer().getPort();
+ }
+
+ // 每日 00:30 全量重建
+ @Scheduled(cron = "0 30 0 * * ?")
+ public void fullRebuild() {
+ if (!TaskUtil.isScheduledTaskPort(port)) {
+ return;
+ }
+ try {
+ chargingOrderService.rebuildLargeScreenStat();
+ } catch (Exception e) {
+ log.error("大屏预聚合表全量重建失败", e);
+ }
+ }
+
+ // 每 10 分钟刷新当月/当日/全局
+ @Scheduled(fixedRate = 1000 * 60 * 10)
+ public void refresh() {
+ if (!TaskUtil.isScheduledTaskPort(port)) {
+ return;
+ }
+ try {
+ chargingOrderService.refreshLargeScreenStat();
+ } catch (Exception e) {
+ log.error("大屏预聚合表刷新失败", e);
+ }
+ }
+
+ // 启动时表空则异步全量回填, 不阻塞启动
+ @EventListener(ApplicationReadyEvent.class)
+ public void backfillIfEmpty() {
+ if (!TaskUtil.isScheduledTaskPort(port)) {
+ return;
+ }
+ try {
+ Long count = chargingOrderMapper.statTableCount();
+ if (count != null && count > 0) {
+ return;
+ }
+ log.info("大屏预聚合表为空, 启动异步全量回填");
+ Thread backfill = new Thread(() -> {
+ try {
+ chargingOrderService.rebuildLargeScreenStat();
+ } catch (Exception e) {
+ log.error("大屏预聚合表启动回填失败", e);
+ }
+ }, "large-screen-stat-backfill");
+ backfill.setDaemon(true);
+ backfill.start();
+ } catch (Exception e) {
+ log.error("大屏预聚合表启动回填检查失败", e);
+ }
+ }
+}
diff --git a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
index ecfb877..c67c34e 100644
--- a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
+++ b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
@@ -3120,4 +3120,128 @@
AND site_id IN (${siteIdsCsv})
</if>
</select>
+
+ <!-- ==================== 大屏预聚合表 t_large_screen_site_stat ==================== -->
+
+ <!-- 任务用: 按月切片聚合, 口径与 getLargeScreenSummary 完全一致(按月边界裁剪) -->
+ <select id="monthSliceAgg" resultType="java.util.Map">
+ SELECT charging_order.site_id AS site_id,
+ COUNT(DISTINCT charging_order.id) AS order_count,
+ COUNT(DISTINCT charging_order.app_user_id) AS user_count,
+ COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS capacity,
+ COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electric_fee,
+ COALESCE(ROUND(SUM(CASE
+ WHEN charging_order.order_source IN (0, 1) THEN accounting.period_service_price
+ ELSE accounting.period_service_price * 0.8
+ END), 2), 0) AS service_fee
+ FROM t_charging_order charging_order
+ LEFT JOIN t_charging_order_accounting_strategy accounting
+ ON accounting.charging_order_id = charging_order.id
+ WHERE charging_order.del_flag = 0
+ AND charging_order.status = 5
+ AND charging_order.recharge_payment_status = 2
+ AND charging_order.create_time >= #{startTime}
+ AND charging_order.create_time < #{endTime}
+ GROUP BY charging_order.site_id
+ </select>
+
+ <!-- 任务用: 按日切片聚合, 口径与 getLargeScreenChargingPower 一致(无 status 过滤) -->
+ <select id="daySliceAgg" resultType="java.util.Map">
+ SELECT site_id AS site_id,
+ COUNT(1) AS order_count,
+ COALESCE(SUM(IFNULL(power, 0)), 0) AS total_power
+ FROM t_charging_order
+ WHERE del_flag = 0
+ AND create_time >= #{startTime}
+ AND create_time < #{endTime}
+ GROUP BY site_id
+ </select>
+
+ <!-- 任务用: 注册用户总数 -->
+ <select id="globalRegisteredUsers" resultType="java.lang.Long">
+ SELECT COUNT(*)
+ FROM charging_pile_account.t_app_user
+ WHERE del_flag = 0
+ </select>
+
+ <!-- 任务用: 批量写入聚合行 -->
+ <insert id="replaceSiteStatBatch">
+ REPLACE INTO t_large_screen_site_stat
+ (stat_type, period, site_id, order_count, user_count, capacity, electric_fee,
+ service_fee, income, total_power, registered_user_count, update_time)
+ VALUES
+ <foreach collection="rows" item="row" separator=",">
+ (#{row.statType}, #{row.period}, #{row.siteId}, #{row.orderCount}, #{row.userCount},
+ #{row.capacity}, #{row.electricFee}, #{row.serviceFee}, #{row.income},
+ #{row.totalPower}, #{row.registeredUserCount}, NOW())
+ </foreach>
+ </insert>
+
+ <!-- 任务用: 表行数(启动回填判断) -->
+ <select id="statTableCount" resultType="java.lang.Long">
+ SELECT COUNT(*) FROM t_large_screen_site_stat
+ </select>
+
+ <!-- 接口用: 累计指标(M 行汇总) -->
+ <select id="summaryFromStat" resultType="java.util.Map">
+ SELECT COALESCE(SUM(order_count), 0) AS validOrderCount,
+ COALESCE(SUM(user_count), 0) AS chargingUserCount,
+ COALESCE(SUM(capacity), 0) AS totalChargingCapacity,
+ COALESCE(SUM(electric_fee), 0) AS electricityFee,
+ COALESCE(SUM(service_fee), 0) AS serviceFee,
+ COALESCE(SUM(income), 0) AS totalIncome
+ FROM t_large_screen_site_stat
+ WHERE stat_type = 'M'
+ <if test="siteIds != null and siteIds.size() > 0">
+ AND site_id IN
+ <foreach collection="siteIds" item="siteId" open="(" separator="," close=")">
+ #{siteId}
+ </foreach>
+ </if>
+ </select>
+
+ <!-- 接口用: 近 N 月收入趋势(M 行按 period 分组, period 为 yyyy-MM 字典序可比) -->
+ <select id="incomeTrendFromStat" resultType="com.ruoyi.order.dto.SixChargingDto">
+ SELECT period AS month,
+ COALESCE(SUM(electric_fee), 0) AS electrovalence,
+ COALESCE(SUM(service_fee), 0) AS service_charge
+ FROM t_large_screen_site_stat
+ WHERE stat_type = 'M'
+ AND period >= #{sixBefore}
+ <if test="siteIds != null and siteIds.size() > 0">
+ AND site_id IN
+ <foreach collection="siteIds" item="siteId" open="(" separator="," close=")">
+ #{siteId}
+ </foreach>
+ </if>
+ GROUP BY period
+ </select>
+
+ <!-- 接口用: 日功率曲线(D 行按 period 分组, period 为 yyyy-MM-dd 字典序可比) -->
+ <select id="powerFromStat" resultType="java.util.Map">
+ SELECT period AS day,
+ COALESCE(SUM(total_power), 0) AS total_power,
+ COALESCE(SUM(order_count), 0) AS order_count
+ FROM t_large_screen_site_stat
+ WHERE stat_type = 'D'
+ AND period >= #{dayStart}
+ AND period < #{dayEnd}
+ <if test="siteIds != null and siteIds.size() > 0">
+ AND site_id IN
+ <foreach collection="siteIds" item="siteId" open="(" separator="," close=")">
+ #{siteId}
+ </foreach>
+ </if>
+ GROUP BY period
+ </select>
+
+ <!-- 接口用: 注册用户数(G 行) -->
+ <select id="registeredFromStat" resultType="java.lang.Long">
+ SELECT registered_user_count
+ FROM t_large_screen_site_stat
+ WHERE stat_type = 'G'
+ AND period = 'ALL'
+ AND site_id = 0
+ LIMIT 1
+ </select>
</mapper>
diff --git a/sql/large_screen_stat.sql b/sql/large_screen_stat.sql
new file mode 100644
index 0000000..e189f9d
--- /dev/null
+++ b/sql/large_screen_stat.sql
@@ -0,0 +1,91 @@
+-- ============================================================================
+-- 大屏 /large-screen/* 慢查询优化 SQL 脚本
+-- 背景: /large-screen/summary 在 MyCat 分表下按 site_id 过滤会广播扫描 50 张
+-- t_charging_order 物理表(无索引、无时间边界、跨分片 join),导致超时。
+-- 方案: 新建站点级预聚合表 t_large_screen_site_stat(全局表),由后端定时任务
+-- 按"月/日/全局"切片汇总写入,大屏接口改为读该表;本脚本为 50 张分表
+-- 补充 create_time 索引,供聚合任务按月/按日切片范围扫描使用。
+--
+-- 执行方式: 在 MySQL 192.168.110.74 上直接执行(不要走 MyCat)。
+-- 幂等: 可重复执行,不会报错。
+-- ============================================================================
+
+-- 1. 预聚合表(全局表,不分区)
+CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_site_stat` (
+ `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
+ `stat_type` char(1) NOT NULL COMMENT '统计粒度:D=日 M=月 G=全局',
+ `period` varchar(16) NOT NULL COMMENT 'D:yyyy-MM-dd / M:yyyy-MM / G:ALL',
+ `site_id` int NOT NULL COMMENT '站点ID, G行固定为0',
+ `order_count` bigint NOT NULL DEFAULT 0 COMMENT '订单数',
+ `user_count` bigint NOT NULL DEFAULT 0 COMMENT '充电用户数(按站点统计, 跨站充电会重复计数)',
+ `capacity` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '充电量(kWh)',
+ `electric_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '电费',
+ `service_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '服务费(含三方0.8折算)',
+ `income` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '收入(电费+服务费)',
+ `total_power` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '功率合计(仅D行使用)',
+ `registered_user_count` bigint NOT NULL DEFAULT 0 COMMENT '注册用户数(仅G行使用)',
+ `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `uk_stat_period_site` (`stat_type`,`period`,`site_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏站点统计预聚合表';
+
+-- 2. 给 50 张 t_charging_order 物理分表补 create_time 索引(幂等,已存在则跳过)
+DROP PROCEDURE IF EXISTS `charging_pile_order`.`add_large_screen_create_time_index`;
+DELIMITER $$
+CREATE PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`()
+BEGIN
+ DECLARE i INT DEFAULT 0;
+ DECLARE tbl_name VARCHAR(64);
+ DECLARE idx_exists INT;
+ WHILE i < 50 DO
+ SET tbl_name = CONCAT('t_charging_order_', i);
+ SELECT COUNT(*) INTO idx_exists
+ FROM information_schema.statistics
+ WHERE table_schema = 'charging_pile_order'
+ AND table_name = tbl_name
+ AND index_name = 'idx_create_time';
+ IF idx_exists = 0 THEN
+ SET @ddl = CONCAT('ALTER TABLE `charging_pile_order`.`', tbl_name,
+ '` ADD KEY idx_create_time (create_time)');
+ PREPARE stmt FROM @ddl;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+ SET i = i + 1;
+ END WHILE;
+END$$
+DELIMITER ;
+CALL `charging_pile_order`.`add_large_screen_create_time_index`();
+DROP PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`;
+
+-- 3. 索引结果核对(应返回 50 行)
+SELECT table_name, index_name
+FROM information_schema.statistics
+WHERE table_schema = 'charging_pile_order'
+ AND table_name LIKE 't_charging_order\_%'
+ AND index_name = 'idx_create_time'
+ORDER BY table_name;
+
+-- ============================================================================
+-- 上线后数据一致性对比(只读,可在线执行;对比新老口径差值,应≈0)
+-- 老口径: 现接口 getLargeScreenSummary 的原始 SQL(挑少量站点对比,避免生产压力)
+-- ============================================================================
+-- SELECT COUNT(DISTINCT charging_order.id) AS validOrderCount,
+-- COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS totalChargingCapacity,
+-- COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electricityFee,
+-- COALESCE(ROUND(SUM(CASE WHEN charging_order.order_source IN (0,1)
+-- THEN accounting.period_service_price ELSE accounting.period_service_price * 0.8 END), 2), 0) AS serviceFee
+-- FROM charging_pile_order.t_charging_order charging_order
+-- LEFT JOIN charging_pile_order.t_charging_order_accounting_strategy accounting
+-- ON accounting.charging_order_id = charging_order.id
+-- WHERE charging_order.del_flag = 0
+-- AND charging_order.status = 5
+-- AND charging_order.recharge_payment_status = 2
+-- AND charging_order.site_id IN (101, 102);
+--
+-- SELECT SUM(order_count) AS validOrderCount,
+-- SUM(capacity) AS totalChargingCapacity,
+-- SUM(electric_fee) AS electricityFee,
+-- SUM(service_fee) AS serviceFee
+-- FROM charging_pile_order.t_large_screen_site_stat
+-- WHERE stat_type = 'M' AND site_id IN (101, 102);
--
Gitblit v1.7.1