经验点:统计小时数,根据map去重
数据库日期函数得出每天的日期
24小时:以小时为统计单位 ,当前时间的前24个小时
首先计算要查询的总时间段
LocalDate localDate = LocalDate.now();
LocalTime localTime = LocalTime.now();
LocalDateTime localEndTime = LocalDateTime.of(localDate, localTime);
LocalDateTime localStartTime= localEndTime.minusHours(24L);
根据时间段查出区间内所有的时间
然后根据小时归纳:把日期加小时数当作key加入map,就可以得到所有的日期及时间单位
List<LocalDateTime> allTimes = usableMapper.getAllTimes(reportId, localStartTime, localEndTime);
Map<LocalDateTime,LocalTime> map=new HashMap<>();
for (int i = 0; i <allTimes.size() ; i++) {
LocalDateTime localDateTime = allTimes.get(i);
LocalTime time=LocalTime.of(localDateTime.getHour(),0,0);
map.put(LocalDateTime.of(localDateTime.toLocalDate(),time),localDateTime.toLocalTime());
}
或者可以根据sql语句得到所有的时间点:
得到所有的小时数即横轴数据之后查询每个时间段的数据(例如:01:00:00~01:59:59)
for (LocalDateTime date:map.keySet()) {
LocalTime timeS=LocalTime.of(map.get(date).getHour(),0,0);
LocalTime timeE=LocalTime.of(map.get(date).getHour(),59,59);
LocalDateTime localtimeS=LocalDateTime.of(date.toLocalDate(),timeS);
LocalDateTime localtimeE=LocalDateTime.of(date.toLocalDate(),timeE);
double sumT = usableMapper.getSumTime(reportId, localtimeS, localtimeE);
Integer usableCount = usableMapper.getUsableCount(reportId, localtimeS, localtimeE);
Integer count = usableMapper.getCount(reportId, localtimeS, localtimeE);
UsableAvg usableAvg=UsableAvg
.builder()
.successAvg(Double.parseDouble(df.format(usableCount/(count*1.0))))
.accessTime(date.toString())
.responseTime(Double.parseDouble(df.format(sumT/count)))
.build();
list.add(usableAvg);
}
再进行计算封装
1周/2周/1个月:以天为统计单位,当前时间的前*
计算出总时间段
localStartTime= localEndTime.minusWeeks(1L);
localStartTime= localEndTime.minusMonths(1L);
利用数据库函数得到所有的天日期
//得到所有的天日期
@Select("<script> " +
" select DATE(access_time) from issue_usable u left join issue i on i.id= u.issue_id \n" +
" where report_id=#{reportId} and i.detect_category=1 and u.access_time between #{localStartTime} and #{localEndTime} GROUP BY DAY(access_time)\n" +
" </script>")
List<LocalDate> getAllDay(@Param(value = "reportId") String reportId, @Param(value = "localStartTime") LocalDateTime localStartTime, @Param(value = "localEndTime") LocalDateTime localEndTime);
再循环遍历得到一天之内的数据
LocalDate date= allDay.get(i);
LocalTime timeS=LocalTime.of(0,0,0);
LocalTime timeE=LocalTime.of(23,0,0);
再进行查询计算封装