From dfac5c686a3e076034abd46349d60aeca0457f4a Mon Sep 17 00:00:00 2001
From: dengzedong <dengzedong@email>
Date: 星期二, 01 四月 2025 14:44:40 +0800
Subject: [PATCH] 派生指标 月周期开始日
---
iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java | 158 ++++++++++++++++++++++++++++++++++++++++++----------
1 files changed, 127 insertions(+), 31 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 54c1faa..8de5cd4 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
@@ -5,14 +5,20 @@
import com.iailab.module.data.common.enums.IndTimeLimitEnum;
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;
import com.iailab.module.data.ind.item.service.IndItemAtomService;
import com.iailab.module.data.ind.item.service.IndItemDerService;
+import com.iailab.module.data.ind.item.service.IndItemService;
import com.iailab.module.data.ind.item.vo.IndItemAtomVO;
import com.iailab.module.data.ind.item.vo.IndItemDerVO;
import com.iailab.module.data.ind.value.dto.QuerySourceValueDTO;
+import org.apache.commons.lang3.StringUtils;
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;
@@ -31,6 +37,9 @@
private IndDataSetService indDataSetService;
@Autowired
+ private IndItemService indItemService;
+
+ @Autowired
private IndItemDerService indItemDerService;
public final static String PATTERN_YEAR = "yyyy";
@@ -45,13 +54,16 @@
if (indItem == null) {
return result;
}
+ IndItemEntity itemEntity = indItemService.get(itemId);
+ result.setItemNo(itemEntity.getItemNo());
+ result.setIndItemAtom(indItem);
IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
if (dataSet == null) {
return result;
}
result.setDataSource(indItem.getDataSource());
StringBuilder selectSql = new StringBuilder();
- IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(indItem.getStatFunc());
+ IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc()) ? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
switch (statFun) {
case AVG:
selectSql.append("AVG(");
@@ -68,7 +80,7 @@
case SUM:
selectSql.append("SUM(");
break;
- default:
+ case DEFAULT:
selectSql.append("(");
break;
}
@@ -85,16 +97,24 @@
if (indItem == null) {
return result;
}
+ IndItemEntity itemEntity = indItemService.get(itemId);
+ result.setItemNo(itemEntity.getItemNo());
result = getAtomSql(indItem.getAtomItemId());
// 拼接SELECT
StringBuilder selectSql = new StringBuilder();
- selectSql.append(indItem.getDimension());
- selectSql.append(", ");
- selectSql.append(result.getSelectSql());
- selectSql.append(", ");
- selectSql.append(indItem.getTimeLabel());
- selectSql.append(" data_time");
+ if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
+ selectSql.append(indItem.getDimension());
+ selectSql.append(", ");
+ selectSql.append(result.getSelectSql());
+ } else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
+ selectSql.append(result.getSelectSql());
+ } else {
+ selectSql.append(result.getSelectSql());
+ selectSql.append(", ");
+ selectSql.append(indItem.getTimeLabel());
+ selectSql.append(" data_time");
+ }
result.setSelectSql(selectSql.toString());
// 拼接WHERE
@@ -122,7 +142,9 @@
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- whereSql.append("' AND >= '");
+ 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("'");
@@ -131,7 +153,9 @@
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- whereSql.append("' AND >= '");
+ 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("'");
@@ -139,23 +163,39 @@
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:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("' < '");
+ 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:
- calendar.set(Calendar.MONTH, 11);
+ whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
+ calendar.set(Calendar.MONTH, 11);
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
- whereSql.append("' AND >= '");
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
calendar.set(Calendar.MONTH, 0);
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
whereSql.append("'");
@@ -173,12 +213,29 @@
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
whereSql.append("'");
break;
+ case LAST_HOUR_24:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.HOUR_OF_DAY, -24);
+ whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
+ whereSql.append("'");
+ break;
case CUSTOM:
+ whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
- whereSql.append("' AND >= '");
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
whereSql.append("'");
+ break;
+ case ALL:
+ whereSql.append("1 = 1");
break;
default:
break;
@@ -187,15 +244,39 @@
// 拼接GROUP
StringBuilder groupSql = new StringBuilder();
- groupSql.append(" GROUP BY ");
- groupSql.append(indItem.getDimension());
+ if (StringUtils.isNotBlank(indItem.getDimension())) {
+ groupSql.append(indItem.getDimension());
+ }
result.setGroupSql(groupSql.toString());
// 拼接ORDER
StringBuilder orderBySql = new StringBuilder();
- orderBySql.append(indItem.getTimeLabel());
- result.setOrderBySql(orderBySql.toString());
+ if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
+ orderBySql.append(indItem.getTimeLabel());
+ 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) {
@@ -206,11 +287,20 @@
}
result = getAtomSql(indItem.getAtomItemId());
+ IndItemEntity itemEntity = indItemService.get(itemId);
+ result.setItemNo(itemEntity.getItemNo());
+
// 拼接SELECT
StringBuilder selectSql = new StringBuilder();
- selectSql.append(indItem.getDimension());
- selectSql.append(", ");
- selectSql.append(result.getSelectSql());
+ if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
+ selectSql.append(indItem.getDimension());
+ selectSql.append(", ");
+ selectSql.append(result.getSelectSql());
+ } else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
+ selectSql.append(result.getSelectSql());
+ } else {
+ selectSql.append(result.getSelectSql());
+ }
selectSql.append(", ");
selectSql.append(indItem.getTimeLabel());
selectSql.append(" data_time");
@@ -221,22 +311,28 @@
whereSql.append(" ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(endTime, PATTERN_MON));
- whereSql.append("' AND >= '");
- whereSql.append(DateUtils.format(startTime, PATTERN_MON));
+ whereSql.append(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
whereSql.append("'");
result.setWhereSql(whereSql.toString());
// 拼接GROUP
StringBuilder groupSql = new StringBuilder();
- groupSql.append(" GROUP BY ");
- groupSql.append(indItem.getDimension());
+ if (StringUtils.isNotBlank(indItem.getDimension())) {
+ groupSql.append(indItem.getDimension());
+ }
result.setGroupSql(groupSql.toString());
// 拼接ORDER
StringBuilder orderBySql = new StringBuilder();
- orderBySql.append(indItem.getTimeLabel());
- result.setOrderBySql(orderBySql.toString());
+ if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
+ orderBySql.append(indItem.getTimeLabel());
+ result.setOrderBySql(orderBySql.toString());
+ }
+
return result;
}
}
\ No newline at end of file
--
Gitblit v1.9.3