需求:
动态查询某一统计字段在一段时间内的统计折线图信息
- controller层
@ApiOperation(value = "getStatisticDetail", notes = "统计折线图")
@GetMapping("/detail")
@ResponseStatus(HttpStatus.OK)
@AccessLogAnnotation(ignoreRequestArgs = "loginUser")
@ApiImplicitParams({
@ApiImplicitParam(name = "startTime", value = "开始时间", dataTypeClass = String.class),
@ApiImplicitParam(name = "endTime", value = "结束时间", dataTypeClass = String.class),
@ApiImplicitParam(name = "taskType", value = "查询类型", required = true, dataTypeClass = Enum.class)
})
public Result getStatisticDetail(@ApiIgnore @RequestAttribute(value = Constants.SESSION_USER) User loginUser,
@RequestParam(value = "taskType", required = true) String taskType,
@RequestParam(value = "startTime", required = true) String startTime,
@RequestParam(value = "endTime", required = true) String endTime) {
return statisticAnalysisService.getStatisticDetail(startTime, endTime, Long.valueOf(projectCode), taskType);
}
- service层biz
public Result<StatisticDetailResponse> getStatisticDetail( String startTime, String endTime String taskType) {
Result<StatisticDetailResponse> result = new Result<>();
StatisticDetailResponse statisticDetailResponse = new StatisticDetailResponse();
//计算时间天数跨度
String[] days = new String[getDayDiffer(startTime, endTime)];
startTime = "'" + startTime + "'";
endTime = "'" + endTime + "'";
StatisticDetailResponse responseList = getStatisticTasks(statisticDetailResponse, startTime, endTime,Objects.requireNonNull(StatisticDetailType.getStatisticDetailByType(taskType)), days);
result.setData(responseList);
putMsg(result, Status.SUCCESS);
return result;
}
private StatisticDetailResponse getStatisticTasks(StatisticDetailResponse statisticDetailResponse, String startTime, String endTime, StatisticDetailType statisticDetailType, String[] days) {
String filed = statisticDetailType.getFeildName();
List<StatisticAnalysis> details = statisticAnalysisMapper.getStatisticDetail(startTime, endTime, filed, days);
List<String> xAxisData = new ArrayList<>(12);
List<StatisticTask> statisticTaskList = new ArrayList<>(12);
if (ObjectUtils.isNotEmpty(statisticDetailResponse.getList())) {
statisticTaskList = statisticDetailResponse.getList();
}
List data = new ArrayList<>(12);
for (StatisticAnalysis detail : details) {
String key = detail.getName();
Object value = detail.getNameValue();
xAxisData.add(key.replaceAll(String.valueOf(Constants.SUBTRACT_CHAR), ""));
data.add(value);
}
StatisticTask statisticTask = new StatisticTask();
statisticTask.setData(data);
statisticTask.setName(statisticDetailType.getType());
statisticTaskList.add(statisticTask);
statisticDetailResponse.setXAxisData(xAxisData);
statisticDetailResponse.setList(statisticTaskList);
return statisticDetailResponse;
}
2.1. 查询开始时间——结束时间的间隔天数
/**
* 计算开始时间,结束时间,间隔天数
*
* @param startTime 开始时间
* @param endTime 结束时间
* @return 间隔天数
*/
public static int getDayDiffer(String startTime, String endTime) {
SimpleDateFormat formatter = new SimpleDateFormat(Constants.DATE_PATTERN1);
long ts2 = 0;
try {
Date date1 = null;
Date date = formatter.parse(startTime);
long ts = date.getTime();
date1 = formatter.parse(endTime);
long ts1 = date1.getTime();
ts2 = ts1 - ts;
} catch (ParseException e) {
e.printStackTrace();
}
int totalTime = 0;
totalTime = (int) (ts2 / (24 * 3600 * 1000) + 1);
return totalTime;
}
2.2. taskType枚举类型
public enum StatisticDetailType {
/**
* 当日存储剩余空间(单位P)
*/
freeSpace("freeSpace", "free_space"),
/**
* 当日空间使用比例(单位%)
*/
useRatio("useRatio", "use_ratio");
private String type;
private String feildName;
StatisticDetailType() {
}
StatisticDetailType(String type, String feildName) {
this.type = type;
this.feildName = feildName;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getFeildName() {
return feildName;
}
public void setFeildName(String feildName) {
this.feildName = feildName;
}
/**
* 根据枚举值返回枚举对象
*
* @param key
* @return
*/
public static StatisticDetailType getStatisticDetailByType(String key) {
for (StatisticDetailType statisticDetailType : values()) {
if (Objects.equals(statisticDetailType.type, key)) {
return statisticDetailType;
}
}
return null;
}
}
- Mapper
/**
* @param startTime 结束时间
* @param endTime 开始时间
* @param filed 查询字段
* @param days 查询天数
* @return 查询结果
*/
List<StatisticAnalysis> getStatisticDetail(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("filed") String filed, @Param("days") String[] days);
- Mapper.xml
<select id="getStatisticDetail" resultType="entity.StatisticAnalysis">
select d.name, IFNULL(T.${filed},0) nameValue
from (
select date_add(${startTime},interval @i:=@i+1 day) as name
from (
select 1
<foreach item="index" collection="days">
union all select 1
</foreach>
) as tmp,(select @i:= -1) t
) d
left join (
select ${filed},DATE_FORMAT(statistic_date,'%Y-%m-%d') statistic_date
from s_statistic_analysis
where statistic_date >= ${startTime} and statistic_date <=${endTime}
)T on T.statistic_date = d.name
</select>
- entity
public class StatisticDetailResponse {
private List<String> xAxisData;
private List<StatisticTask> list;
}
public class StatisticTask {
private String name;
private List data;
}
- 结果
{
"code": 0,
"msg": "success",
"data": {
"list": [
{
"name": "freeSpace",
"data": [
"0.00",
"0.00",
"0.00",
"0.00",
"0.00"
]
}
],
"xaxisData": [
"20231023",
"20231024",
"20231025",
"20231026",
"20231027"
]
},
"success": true,
"failed": false
}