springboot+mybatis3.5.2动态查询某一字段在某一段时间内的统计信息（折线图）

需求：
动态查询某一统计字段在一段时间内的统计折线图信息

1. 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);
   }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. service层biz

    public Result getStatisticDetail( String startTime, String endTime String taskType) {
      Result 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 details = statisticAnalysisMapper.getStatisticDetail(startTime, endTime, filed, days);
      List xAxisData = new ArrayList<>(12);
      List 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;
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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;
   }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;
   }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

3. Mapper

    /**
    * @param startTime   结束时间
    * @param endTime     开始时间
    * @param filed       查询字段
    * @param days        查询天数
    * @return 查询结果
    */
   List getStatisticDetail(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("filed") String filed, @Param("days") String[] days);
1
2
3
4
5
6
7
8

4. Mapper.xml

    
       select d.name, IFNULL(T.${filed},0) nameValue
       from (
           select date_add(${startTime},interval @i:=@i+1 day) as name
            from (
                   select 1
                      
                        union all select 1
                      
                 ) 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
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5. entity

public class StatisticDetailResponse {

   private List xAxisData;

   private List list;

}

public class StatisticTask {

   private String name;

   private List data;


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6. 结果

{
   "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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27