目录
一,统计图用GROUP BY 给日期分组的缺陷
像下方这种以日期为X轴的 折线 统计图我们该如何给前端提供数据呢,我们在统计数据的时候通常都是用 GROUP BY 来进行分组统计,但是 GROUP BY 用来给日期分组统计会有一个缺陷,就是如果有些日期没有数据,就不会有这天的分组,直接把数据提供给前端,出来的统计图日期就不是连续的。
二,解决GROUP BY 日期分组的缺陷问题
解决这个问题其实很简单,只要生成完整的日期,再把查出来的数据放入相应的日期里,没有数据的日期填充 0 ,因为每个月的天数都不是一样的,所以要用到日历类Calendar 来生成日期。
三,代码流程图
四,各层代码
1.目录结构
2.Controller层代码
import com.example.datavisualization.practises.model.req.StatisticsReq;
import com.example.datavisualization.practises.service.StatisticsService;
import com.example.datavisualization.response.ResponseData;
import com.example.datavisualization.response.SuccessResponseData;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.HashMap;
/**
* @author XiaoXiongQAQ
* @Date 2022/6/17
*/
@RestController
@RequestMapping("home")
public class StatisticsController {
@Resource
private StatisticsService statisticsService;
/**
* 入参传入字段 startDate,endDate
* 入参格式
* {
* "startDate": "2022-06-14 19:46:16",
* "endDate": "2022-06-24 19:46:16"
* }
* @param statisticsReq
* @return SuccessResponseData
*/
@PostMapping("/getStatistics")
public ResponseData addHhh(@RequestBody StatisticsReq statisticsReq){
HashMap<Object, Object> statistics = statisticsService.statistics(statisticsReq);
return new SuccessResponseData(statistics);
}
}
3.service层代码
import com.example.datavisualization.practises.mapper.StatisticsMapper;
import com.example.datavisualization.practises.model.dto.StatisticsDto;
import com.example.datavisualization.practises.model.req.StatisticsReq;
import com.example.datavisualization.util.DateTimeFormatUtil;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @author XiaoXiongQAQ
* @Date 2022/6/14
*/
@Service
public class StatisticsService {
@Resource
private StatisticsMapper statisticsMapping;
public HashMap<Object, Object> statistics(StatisticsReq statisticsReq){
//拿到时间轴完整集合(时间顺序倒序)
List<String> days = DateTimeFormatUtil.getTwoDaysDayAsc(statisticsReq.getStartDate(), statisticsReq.getEndDate());
//查询到的数据集合
List<StatisticsDto> statisticsList = statisticsMapping.getStatisticsList(statisticsReq);
//将数据集合放入Map中方便通过时间去查找相应的数据
HashMap<String, Integer> statisticsmap = new HashMap<>();
for (StatisticsDto statistics : statisticsList) {
String date = statistics.getDateTime();
Integer value = statistics.getUserQuantity();
statisticsmap.put(date, value);
}
//写法1,未优化,简单易懂
//新建一个List存放数量,
List<Integer> valueList = new ArrayList<>();
for (String day : days) {
Integer value = statisticsmap.get(day);
if (value != null) {
valueList.add(value);
} else {
valueList.add(0);
}
}
HashMap<Object, Object> hashMap = new HashMap<>();
hashMap.put("date",days);
hashMap.put("value",valueList);
return hashMap;
//写法2,优化后,代码简洁干净
// List<Integer> valueList = new ArrayList<Integer>() {{
// days.forEach(day -> add(statisticsmap.get(day) == null ? 0 : statisticsmap.get(day)));
// }};
// return new HashMap<Object, Object>(2) {{
// put("date",days);
// put("value",valueList);
// }};
}
}
4.日期工具类
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class DateTimeFormatUtil {
public static List<String> getTwoDaysDayAsc(String dateStart, String dateEnd) {
List<String> dateList = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
//将String类型转为Date类型
Date dateOne = sdf.parse(dateStart);
Date dateTwo = sdf.parse(dateEnd);
//设置日历时间
Calendar calendar = Calendar.getInstance();
calendar.setTime(dateTwo);
//将最后一天放入集合
dateList.add(sdf.format(dateTwo));
//将结束的日历时间循环减去 1天 并存入集合,当日历时间等于开始时间就结束循环
while (calendar.getTime().after(dateOne)) {
calendar.add(Calendar.DAY_OF_MONTH, -1);
dateList.add(sdf.format(calendar.getTime()));
}
} catch (Exception e) {
e.printStackTrace();
}
Collections.reverse(dateList);
return dateList;
}
}
5.Dto层
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author XiaoXiongQAQ
* @Date 2022/6/15
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class StatisticsDto {
/**
* 数量
*/
private Integer userQuantity;
/**
* 日期
*/
private String dateTime;
}
6.req
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author XiaoXiongQAQ
* @Date 2022/6/17
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class StatisticsReq {
/**
* 开始时间
*/
private String startDate;
/**
* 结束时间
*/
private String endDate;
}
7.mapper层
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.datavisualization.practises.model.dto.StatisticsDto;
import com.example.datavisualization.practises.model.req.StatisticsReq;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author XiaoXiongQAQ
* @Date 2022/6/14
*/
@Mapper
public interface StatisticsMapper extends BaseMapper<StatisticsDto> {
/**
* 查询每天数量
* @param statisticsReq
* @return List
*/
List<StatisticsDto> getStatisticsList(@Param("statisticsReq") StatisticsReq statisticsReq);
}
8.XML
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.datavisualization.practises.mapper.StatisticsMapper">
<select id="getStatisticsList" resultType="com.example.datavisualization.practises.model.dto.StatisticsDto">
select
DATE_FORMAT(create_time,'%Y-%m-%d') dateTime,
count(*) userQuantity
from staff
<where>
<if test="statisticsReq.startDate != null and statisticsReq.startDate != ''">
and DATE_FORMAT(create_time, '%Y-%m-%d') >=#{statisticsReq.startDate}
</if>
<if test="statisticsReq.endDate != null and statisticsReq.endDate != ''">
and DATE_FORMAT(create_time, '%Y-%m-%d') <=#{statisticsReq.endDate}
</if>
</where>
GROUP BY dateTime
ORDER BY dateTime
</select>
</mapper>
五,代码执行效果图
六,gitee 项目地址
项目已上传到 gitee 有需要可以点击下方连接查看(数据库表SQL也放在里面 staff.sql )
https://gitee.com/bear-qaq/data-visualizationhttps://gitee.com/bear-qaq/data-visualization