From 063a25c9cf4e38496cf5395569856b67c80cdc53 Mon Sep 17 00:00:00 2001
From: 潘志宝 <979469083@qq.com>
Date: 星期一, 28 四月 2025 19:11:08 +0800
Subject: [PATCH] 查询最大值
---
iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java | 117 +++++++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 90 insertions(+), 27 deletions(-)
diff --git a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
index e33849c..a2e3f09 100644
--- a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
+++ b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
@@ -3,6 +3,7 @@
import com.iailab.framework.common.util.date.DateUtils;
import com.iailab.module.data.common.enums.IndStatFuncEnum;
import com.iailab.module.data.common.enums.IndTimeLimitEnum;
+import com.iailab.module.data.enums.TimeGranularityEnum;
import com.iailab.module.data.ind.data.dto.IndDataSetDTO;
import com.iailab.module.data.ind.data.service.IndDataSetService;
import com.iailab.module.data.ind.item.entity.IndItemEntity;
@@ -16,6 +17,9 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
+import java.text.SimpleDateFormat;
+import java.time.YearMonth;
+import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
@@ -43,7 +47,11 @@
public final static String PATTERN_MON = "yyyy-MM";
- public final static String PATTERN_DATE = "yyyy-MM-dd";
+ public final static String PATTERN_DAY = "yyyy-MM-dd";
+
+ public final static String PATTERN_HOUR = "yyyy-MM-dd HH";
+
+ public static final String PATTERN_MIN = "yyyy-MM-dd HH:mm:ss";
public QuerySourceValueDTO getAtomSql(String itemId) {
QuerySourceValueDTO result = new QuerySourceValueDTO();
@@ -83,6 +91,7 @@
}
selectSql.append(indItem.getUsingField());
selectSql.append(") data_value");
+ //selectSql.append(") \"data_value\"");
result.setSelectSql(selectSql.toString());
result.setViewSql(dataSet.getQuerySql());
return result;
@@ -121,105 +130,138 @@
calendar.set(Calendar.MILLISECOND, 0);
calendar.set(Calendar.SECOND, 0);
IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
+ // 根据时间粒度确定 时间格式
+ String datePattern = PATTERN_MIN;
+ if (StringUtils.isNotBlank(itemEntity.getTimeGranularity())) {
+ switch (TimeGranularityEnum.getEumByCode(itemEntity.getTimeGranularity())){
+ case TG_MIN:
+ datePattern = PATTERN_MIN;
+ break;
+ case TG_HOUR:
+ datePattern = PATTERN_HOUR;
+ break;
+ case TG_DAY:
+ datePattern = PATTERN_DAY;
+ break;
+ case TG_MON:
+ datePattern = PATTERN_MON;
+ break;
+ default:
+ datePattern = PATTERN_YEAR;
+ }
+ }
switch (timeLimit) {
case TODAY:
whereSql.append(indItem.getTimeLabel());
whereSql.append("='");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case YESTERDAY:
calendar.add(Calendar.DAY_OF_YEAR, -1);
whereSql.append(indItem.getTimeLabel());
whereSql.append("='");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case LAST_DAY_7:
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
calendar.add(Calendar.DAY_OF_YEAR, -7);
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case LAST_DAY_30:
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
calendar.add(Calendar.DAY_OF_YEAR, -30);
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case THIS_MONTH:
whereSql.append(indItem.getTimeLabel());
whereSql.append("='");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
whereSql.append("'");
break;
case LAST_MONTH:
- calendar.add(Calendar.MONTH, -1); // 将当前日期减去一个月，得到上个月的日期
- whereSql.append(indItem.getTimeLabel());
- whereSql.append(" >= '");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON)); // 上个月的第一天
- calendar.add(Calendar.MONTH, 1); // 再加一个月，回到当前月
- whereSql.append("' AND '");
whereSql.append(indItem.getTimeLabel());
whereSql.append("' < '");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON)); // 当前月
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.MONTH, -1);
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
whereSql.append("'");
break;
case LAST_MONTH_12:
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
- whereSql.append("' AND '");
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append("' >= '");
calendar.add(Calendar.MONTH, -12);
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
whereSql.append("'");
break;
case THIS_YEAR_MONTH:
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
calendar.set(Calendar.MONTH, 11);
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
calendar.set(Calendar.MONTH, 0);
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case LAST_YEAR:
calendar.add(Calendar.YEAR, -1);
whereSql.append(indItem.getTimeLabel());
whereSql.append("='");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case THIS_YEAR:
whereSql.append(indItem.getTimeLabel());
whereSql.append("='");
- whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("'");
+ break;
+ case LAST_HOUR_24:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.HOUR_OF_DAY, -24);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case CUSTOM:
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
+ whereSql.append(DateUtils.format(indItem.getTimeEnd(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
- whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
+ whereSql.append(DateUtils.format(indItem.getTimeStart(), datePattern));
whereSql.append("'");
+ break;
+ case ALL:
+ whereSql.append("1 = 1");
break;
default:
break;
@@ -240,6 +282,27 @@
result.setOrderBySql(orderBySql.toString());
}
return result;
+ }
+
+ public static String getYearMonth(Calendar now,Integer monthStart) {
+ int day = now.get(Calendar.DAY_OF_MONTH);
+
+ // 当前月
+ if (monthStart > 0) {
+ if (day < monthStart) {
+ now.add(Calendar.MONTH, -1);
+ }
+ }
+
+ if (monthStart < 0) {
+ if (day >= Math.abs(monthStart)) {
+ now.add(Calendar.MONTH, 1);
+ }
+ }
+ // 定义日期格式化对象，指定格式为 yyyy-MM
+ SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_MON);
+ // 格式化日期并返回结果
+ return sdf.format(now.getTime());
}
public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
@@ -263,10 +326,10 @@
selectSql.append(result.getSelectSql());
} else {
selectSql.append(result.getSelectSql());
- selectSql.append(", ");
- selectSql.append(indItem.getTimeLabel());
- selectSql.append(" data_time");
}
+ selectSql.append(", ");
+ selectSql.append(indItem.getTimeLabel());
+ selectSql.append(" data_time");
result.setSelectSql(selectSql.toString());
// 拼接WHERE
--
Gitblit v1.9.3