From 77a9f19e6532fef315d22f52008ef9c73dc69bc8 Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期二, 04 八月 2026 17:12:04 +0800
Subject: [PATCH] fix: 大屏充电用户数
---
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java | 117 +++++++++++++++++++++++------
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java | 5
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java | 15 +++
sql/large_screen_stat.sql | 11 ++
ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | 44 +++++++++++
5 files changed, 166 insertions(+), 26 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 cc9c611..48c8db1 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
@@ -210,6 +210,21 @@
// 任务用: 按月切片聚合(与 getLargeScreenSummary 相同口径, 按站点分组)
List<Map<String, Object>> monthSliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
+ // 任务用: 按月切片统计充电用户数(工作台口径: 所有订单的去重用户, 不区分状态/删除标记)
+ List<Map<String, Object>> monthSliceUserAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
+
+ // 任务用: 按时间切片取 (site_id, app_user_id) 对(所有订单)
+ List<Map<String, Object>> userSitePairsAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
+
+ // 任务用: 批量写入用户-站点对(幂等)
+ int insertUserSitePairs(@Param("rows") List<Map<String, Object>> rows);
+
+ // 接口用: 充电用户数精确去重(对表)
+ Long userCountFromStat(@Param("siteIds") List<Integer> siteIds);
+
+ // 任务用: 对表行数(启动回填判断)
+ Long userSiteTableCount();
+
// 任务用: 按日切片聚合(与 getLargeScreenChargingPower 相同口径, 按站点分组)
List<Map<String, Object>> daySliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime);
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 978b52a..4a5563a 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
@@ -6711,6 +6711,14 @@
if (summary != null) {
result.putAll(summary);
}
+ // 充电用户数精确去重(用户-站点对表, 跨站不重复); 对表未回填时回退到按站点求和
+ Long pairCount = this.baseMapper.userSiteTableCount();
+ if (pairCount != null && pairCount > 0) {
+ Long exactUserCount = this.baseMapper.userCountFromStat(siteIds);
+ if (exactUserCount != null) {
+ result.put("chargingUserCount", exactUserCount);
+ }
+ }
Long registered = this.baseMapper.registeredFromStat();
result.put("registeredUserCount", registered == null ? 0L : registered);
} catch (Exception e) {
@@ -6836,6 +6844,7 @@
LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
while (!month.isAfter(currentMonth)) {
writeMonthSlice(month, month.plusMonths(1));
+ writeUserSitePairs(month, month.plusMonths(1));
month = month.plusMonths(1);
}
// 2. 近90天日切片
@@ -6856,40 +6865,100 @@
LocalDate currentMonth = LocalDate.now().withDayOfMonth(1);
writeMonthSlice(currentMonth, currentMonth.plusMonths(1));
writeDaySlice(LocalDate.now());
+ writeUserSitePairs(LocalDate.now(), LocalDate.now().plusDays(1));
writeGlobal();
log.info("大屏预聚合表刷新完成, 耗时 {}ms", System.currentTimeMillis() - start);
}
/**
- * 写入一个月度切片聚合(M 行)
+ * 写入时间切片内的 (site_id, app_user_id) 对(充电用户精确去重用, INSERT IGNORE 幂等)
*/
- 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()) {
+ private void writeUserSitePairs(LocalDate sliceStart, LocalDate sliceEnd) {
+ List<Map<String, Object>> pairs = this.baseMapper.userSitePairsAgg(
+ sliceStart.atStartOfDay().format(LARGE_SCREEN_SQL_FMT),
+ sliceEnd.atStartOfDay().format(LARGE_SCREEN_SQL_FMT));
+ if (pairs == null || pairs.isEmpty()) {
return;
}
- List<Map<String, Object>> statRows = new ArrayList<>();
- for (Map<String, Object> row : rows) {
- if (row == null || row.get("site_id") == null) {
+ List<Map<String, Object>> rows = new ArrayList<>();
+ for (Map<String, Object> pair : pairs) {
+ if (pair == null || pair.get("site_id") == null || pair.get("app_user_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);
+ Map<String, Object> row = new HashMap<>();
+ row.put("siteId", pair.get("site_id"));
+ row.put("appUserId", pair.get("app_user_id"));
+ rows.add(row);
+ }
+ if (!rows.isEmpty()) {
+ this.baseMapper.insertUserSitePairs(rows);
+ }
+ }
+
+ /**
+ * 写入一个月度切片聚合(M 行)
+ * 金额/订单数取 monthSliceAgg(已结束+已支付口径);
+ * 充电用户数取 monthSliceUserAgg(工作台口径: 该站点所有订单的去重用户, 不区分状态/删除标记)
+ */
+ private void writeMonthSlice(LocalDate monthStart, LocalDate monthEnd) {
+ String startTime = monthStart.atStartOfDay().format(LARGE_SCREEN_SQL_FMT);
+ String endTime = monthEnd.atStartOfDay().format(LARGE_SCREEN_SQL_FMT);
+ List<Map<String, Object>> rows = this.baseMapper.monthSliceAgg(startTime, endTime);
+ List<Map<String, Object>> userRows = this.baseMapper.monthSliceUserAgg(startTime, endTime);
+ if ((rows == null || rows.isEmpty()) && (userRows == null || userRows.isEmpty())) {
+ return;
+ }
+ Map<Object, BigDecimal> userCountBySite = new HashMap<>();
+ if (userRows != null) {
+ for (Map<String, Object> userRow : userRows) {
+ if (userRow != null && userRow.get("site_id") != null) {
+ userCountBySite.put(userRow.get("site_id"), decimalValue(userRow, "user_count"));
+ }
+ }
+ }
+ String period = monthStart.format(LARGE_SCREEN_MONTH_FMT);
+ List<Map<String, Object>> statRows = new ArrayList<>();
+ Map<Object, Boolean> written = new HashMap<>();
+ if (rows != null) {
+ 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", period);
+ stat.put("siteId", row.get("site_id"));
+ stat.put("orderCount", decimalValue(row, "order_count"));
+ stat.put("userCount", userCountBySite.getOrDefault(row.get("site_id"), BigDecimal.ZERO));
+ 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);
+ written.put(row.get("site_id"), Boolean.TRUE);
+ }
+ }
+ // 当月仅有充电用户、暂无已结束订单的站点, 补一行保用户数(金额为0)
+ for (Map.Entry<Object, BigDecimal> entry : userCountBySite.entrySet()) {
+ if (!written.containsKey(entry.getKey())) {
+ Map<String, Object> stat = new HashMap<>();
+ stat.put("statType", "M");
+ stat.put("period", period);
+ stat.put("siteId", entry.getKey());
+ stat.put("orderCount", BigDecimal.ZERO);
+ stat.put("userCount", entry.getValue());
+ 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", 0L);
+ statRows.add(stat);
+ }
}
if (!statRows.isEmpty()) {
this.baseMapper.replaceSiteStatBatch(statRows);
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
index 539f587..08aa194 100644
--- 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
@@ -73,10 +73,11 @@
}
try {
Long count = chargingOrderMapper.statTableCount();
- if (count != null && count > 0) {
+ Long userSiteCount = chargingOrderMapper.userSiteTableCount();
+ if (count != null && count > 0 && userSiteCount != null && userSiteCount > 0) {
return;
}
- log.info("大屏预聚合表为空, 启动异步全量回填");
+ log.info("大屏预聚合表或用户-站点对表为空(stat={}, userSite={}), 启动异步全量回填", count, userSiteCount);
Thread backfill = new Thread(() -> {
try {
chargingOrderService.rebuildLargeScreenStat();
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 abc920d..31d28ae 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
@@ -3143,6 +3143,50 @@
GROUP BY charging_order.site_id
</select>
+ <!-- 任务用: 按月切片统计充电用户数(工作台口径: 所有订单的去重用户, 不区分状态/删除标记,
+ 与 countAllUserData 同定义; 按站点求和会有跨站重复计数) -->
+ <select id="monthSliceUserAgg" resultType="java.util.Map">
+ SELECT site_id AS site_id,
+ COUNT(DISTINCT app_user_id) AS user_count
+ FROM t_charging_order
+ WHERE create_time >= #{startTime}
+ AND create_time < #{endTime}
+ GROUP BY site_id
+ </select>
+
+ <!-- 任务用: 按时间切片取 (site_id, app_user_id) 对(所有订单, 与工作台 countAllUserData 同口径) -->
+ <select id="userSitePairsAgg" resultType="java.util.Map">
+ SELECT DISTINCT site_id AS site_id, app_user_id AS app_user_id
+ FROM t_charging_order
+ WHERE create_time >= #{startTime}
+ AND create_time < #{endTime}
+ AND app_user_id IS NOT NULL
+ </select>
+
+ <!-- 任务用: 批量写入用户-站点对(幂等) -->
+ <insert id="insertUserSitePairs">
+ INSERT IGNORE INTO t_large_screen_user_site (site_id, app_user_id)
+ VALUES
+ <foreach collection="rows" item="row" separator=",">
+ (#{row.siteId}, #{row.appUserId})
+ </foreach>
+ </insert>
+
+ <!-- 接口用: 充电用户数精确去重(对表, 选中站点全局去重) -->
+ <select id="userCountFromStat" resultType="java.lang.Long">
+ SELECT COUNT(DISTINCT app_user_id)
+ FROM t_large_screen_user_site
+ WHERE site_id IN
+ <foreach collection="siteIds" item="siteId" open="(" separator="," close=")">
+ #{siteId}
+ </foreach>
+ </select>
+
+ <!-- 任务用: 对表行数(启动回填判断) -->
+ <select id="userSiteTableCount" resultType="java.lang.Long">
+ SELECT COUNT(*) FROM t_large_screen_user_site
+ </select>
+
<!-- 任务用: 按日切片聚合, 口径与 getLargeScreenChargingPower 一致(无 status 过滤) -->
<select id="daySliceAgg" resultType="java.util.Map">
SELECT site_id AS site_id,
diff --git a/sql/large_screen_stat.sql b/sql/large_screen_stat.sql
index e189f9d..7f67059 100644
--- a/sql/large_screen_stat.sql
+++ b/sql/large_screen_stat.sql
@@ -66,6 +66,17 @@
AND index_name = 'idx_create_time'
ORDER BY table_name;
+-- 1.2 用户-站点对表(充电用户数精确去重)
+-- 背景: 预聚合表的 user_count 按站点求和, 跨站充电用户会重复计数。
+-- 本表记录"每个站点出现过哪些用户"(所有订单口径, 与工作台 countAllUserData 一致),
+-- 接口对选中站点做 COUNT(DISTINCT app_user_id) 精确去重。
+CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_user_site` (
+ `site_id` int NOT NULL COMMENT '站点ID',
+ `app_user_id` bigint NOT NULL COMMENT '用户ID',
+ `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ PRIMARY KEY (`site_id`,`app_user_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏充电用户-站点对(精确去重)';
+
-- ============================================================================
-- 上线后数据一致性对比(只读,可在线执行;对比新老口径差值,应≈0)
-- 老口径: 现接口 getLargeScreenSummary 的原始 SQL(挑少量站点对比,避免生产压力)
--
Gitblit v1.7.1