From 63486c527b01c459110a88930d9cda1ded633aee Mon Sep 17 00:00:00 2001
From: “djh” <“3298565835@qq.com”>
Date: Tue, 11 Nov 2025 10:32:30 +0800
Subject: [PATCH] 修改新增气象数据统计
---
src/main/java/com/gkhy/fourierSpecialGasMonitor/repository/GasConcentrationRepository.java | 36 ++++++++++++++++++++++++++++++++++++
1 files changed, 36 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/gkhy/fourierSpecialGasMonitor/repository/GasConcentrationRepository.java b/src/main/java/com/gkhy/fourierSpecialGasMonitor/repository/GasConcentrationRepository.java
index a90d38e..d43c1b6 100644
--- a/src/main/java/com/gkhy/fourierSpecialGasMonitor/repository/GasConcentrationRepository.java
+++ b/src/main/java/com/gkhy/fourierSpecialGasMonitor/repository/GasConcentrationRepository.java
@@ -3,8 +3,14 @@
import com.gkhy.fourierSpecialGasMonitor.entity.GasCategory;
import com.gkhy.fourierSpecialGasMonitor.entity.GasConcentration;
+import com.gkhy.fourierSpecialGasMonitor.entity.SummaryStats;
+import com.gkhy.fourierSpecialGasMonitor.entity.req.SummaryStatsReqDTO;
+import com.gkhy.fourierSpecialGasMonitor.entity.resp.SummaryStatsPageRespDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
+import org.springframework.security.core.parameters.P;
import org.springframework.stereotype.Repository;
import javax.persistence.OrderBy;
@@ -18,4 +24,34 @@
GasConcentration findTopByOrderByDataReceivingTimeDesc();
List<GasConcentration> findAllByDataReceivingTimeBetweenOrderByDataReceivingTimeDesc(LocalDateTime startTime, LocalDateTime endTime);
+
+ //分别查询temp,humidity,windSpeed,windDirection,pressure的平均值，最小值，最大值
+ @Query("SELECT new com.gkhy.fourierSpecialGasMonitor.entity.SummaryStats(MIN(g.temp), MAX(g.temp), FUNCTION('ROUND', AVG(g.temp), 2)," +
+ "MIN(g.humidity), MAX(g.humidity), FUNCTION('ROUND', AVG(g.humidity), 2), " +
+ "MIN(g.windSpeed), MAX(g.windSpeed), FUNCTION('ROUND', AVG(g.windSpeed), 2), " +
+ "MIN(g.windDirection), MAX(g.windDirection), FUNCTION('ROUND', AVG(g.windDirection), 2), " +
+ "MIN(g.pressure), MAX(g.pressure), FUNCTION('ROUND', AVG(g.pressure), 2) " +
+ ") FROM GasConcentration g WHERE g.time BETWEEN :startTime AND :endTime")
+ SummaryStats findStats(@Param("startTime") LocalDateTime startTime,@Param("endTime") LocalDateTime endTime);
+
+
+ @Query("SELECT new com.gkhy.fourierSpecialGasMonitor.entity.resp.SummaryStatsPageRespDTO( " +
+ "MAX(CASE WHEN :type = 'temp' THEN g.temp " +
+ "WHEN :type = 'humidity' THEN g.humidity " +
+ "WHEN :type = 'windSpeed' THEN g.windSpeed " +
+ "WHEN :type = 'windDirection' THEN g.windDirection " +
+ "WHEN :type = 'pressure' THEN g.pressure END), " +
+ "MIN(CASE WHEN :type = 'temp' THEN g.temp " +
+ "WHEN :type = 'humidity' THEN g.humidity " +
+ "WHEN :type = 'windSpeed' THEN g.windSpeed " +
+ "WHEN :type = 'windDirection' THEN g.windDirection " +
+ "WHEN :type = 'pressure' THEN g.pressure END), " +
+ "FUNCTION('ROUND', AVG(CASE WHEN :type = 'temp' THEN g.temp " +
+ "WHEN :type = 'humidity' THEN g.humidity " +
+ "WHEN :type = 'windSpeed' THEN g.windSpeed " +
+ "WHEN :type = 'windDirection' THEN g.windDirection " +
+ "WHEN :type = 'pressure' THEN g.pressure END), 2) " +
+ ") FROM GasConcentration g " +
+ "WHERE g.time BETWEEN :startTime AND :endTime")
+ SummaryStatsPageRespDTO findSummaryStatsByTypeAndTimeRange(@Param("type")String type,@Param("startTime") LocalDateTime startTime,@Param("endTime") LocalDateTime endTime);
}
--
Gitblit v1.9.2