本文介绍了如何使用SpringBoot和Mybatis-Plus来处理用户活动参与数据，生成折线统计图。主要涉及两个需求：一是统计指定时间范围内（默认前七天）的参与人数和次数，对于无数据日期填充0；二是仅展示有数据的日期。通过数据库查询和Java后端处理，实现了数据的完整性和准确性。同时，文章还提到了前端时间格式处理和日期补0的问题，以及两种不同的查询策略和其优缺点。
1.背景、需求
公司的一个新需求：
需求一：
【对用户参与活动进行数据统计，生成一个折线统计图，根据给定的时间范围统计对应的参与人数和参与次数。默认查询前七天，若前端给定时间则根据给定时间查询。若当天日期下没有数据，需要赋予0值】（相对麻烦，这是我司需求），如下需要连续的日期
需求二：
同需求一，但是当天日期下没有数据不需要赋值0，只需要展示有数据的日期（那真的是太简单了）
效果如下
注意细节：
- 前端传递的时间是2022-08-23~2022-09-30 的格式，只有日期，没有时分秒，则补全时分秒应该是 2022-08-23 00:00:00 ~2022-09-30 00:00:00(因此会产生一些小问题，在下文2.2.1 方案一 的 2.1 细讲)
技术栈：
springboot+mybatis-plus
前端需要的数据格式
给我一个x轴 一个y轴
x轴的数据[‘08-01’,‘08-02’,‘08-03’,‘08-04’,‘08-05’]
y轴的数据{line1:[10,20,30,40,50],line2:[11,22,33,44,55]}
2. 解决方法
2.1 需求二方案：
直接查询根据日期分组
--查询参与次数
SELECT DATE_FORMAT(log.join_time,'%m-%d') joinTime, IFNULL(count(*),0) FROM activity_turntable_join_log log
where state = 1 and log.join_time BETWEEN "2022-09-02" AND "2022-09-30"
GROUP BY joinTime
-- 分组查询统计每天用户参与人数
SELECT res.days,IFNULL(COUNT(days),0) count FROM
(SELECT DATE_FORMAT(log.join_time,'%m-%d') days FROM activity_turntable_join_log log
where state = 1 and log.join_time BETWEEN "2022-09-02" AND "2022-09-22"
GROUP BY log.join_time, log.user_mark) res
GROUP BY res.days
结果如下，就是当天有数据的就有，没有的就没有日期
2.2 需求一方案：
解决思路
- 方案一：先跟需要二一样用mysql 根据日期group by一下，查询出数据，再用java给没有数据的日期补充0（推荐使用）
- 方案二：使用mysql生成日期，查询所有日期的数据，日期不存在的给0（弊端很大）
2.2.1 方案一
1.1 数据库查询- mapper(不重要)
@Mapper
public interface ActivityTurntableJoinLogMapper extends BaseMapper<ActivityTurntableJoinLogDO> {
/**
*@Description: 查询参与次数
* @author HuiLong.Ding
* @date 2022/9/25 16:28
*/
List<TurntableDailyDataResultBO> queryJoinTimes(String endTime, String startTime,String activityId);
/**
*@Description: 查询参与人数
* @author HuiLong.Ding
* @date 2022/9/26 15:48
*/
List<TurntableDailyDataResultBO> queryJoinPeopleCount(String endTime, String startTime,String activityId);
}
1.2 数据库查询-map.xml下面写两个查询方法用于查询数据库数据
<!-- 查询参与次数-->
<select id="queryJoinTimes" resultMap="turntableDailyDataResultBO">
SELECT DATE_FORMAT(log.join_time,'%m-%d') joinTime, IFNULL(count(*),0) count FROM activity_turntable_join_log log
where state = 1 AND activity_id = #{activityId} AND log.join_time BETWEEN #{startTime} AND #{endTime}
GROUP BY joinTime
</select>
<!-- 查询每日参与人数 -->
<select id="queryJoinPeopleCount" resultMap="turntableDailyDataResultBO">
SELECT res.days joinTime,IFNULL(COUNT(days),0) count FROM
(SELECT DATE_FORMAT(log.join_time,'%m-%d') days FROM activity_turntable_join_log log
where state = 1 AND activity_id = #{activityId} and log.join_time BETWEEN #{startTime} AND #{endTime}
GROUP BY log.join_time, log.user_mark) res
GROUP BY res.days
</select>
2.1 给不存在数据的日期进行补0
因为前端传递的日期是2022-08-23 ~2022-09-30 这种格式，默认是2022-08-23 00:00:00 ~2022-09-30 00:00:00，但是这样查询
会导致2022-09-30 当天的数据不存在，因为2022-09-30 00:00:00是早上0点，我们需要查询的截止时间应该是2022-09-30 23:59:59，所以我这边自己拼接时间，如果前端传递是时间有时分秒那就不存在这个问题。
/**
* @Description: 查询日常数据统计-折线统计图
* @author HuiLong.Ding
* @date 2022/9/25 16:08
*/
@Override
public TurntableDailyDataResultVO queryDailyDataStatistics(TurntableJoinLogQueryVO turntableJoinLogQueryVO) {
log.info("查询日常数据统计-折线统计图入参：turntableJoinLogQueryVO == {}", JSON.toJSONString(turntableJoinLogQueryVO));
// 1.条件校验
// 时间为空时，默认为前7天 包括第七天
Integer duringDays = 6;
String endTime = "",startTime = "";
// 前端传递时间则引用前端时间
if (null != turntableJoinLogQueryVO.getJoinTime()) {
// 拼接时间
startTime = turntableJoinLogQueryVO.getJoinTime().get(0) + " 00:00:00";
endTime = turntableJoinLogQueryVO.getJoinTime().get(1) + " 23:59:59";
// 计算时间区间的时间间隔
try {
duringDays = daysBetween(endTime, startTime);
} catch (ParseException e) {
throw new RuntimeException(e);
}
} else {// 前端不传时间则默认7天
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String now = simpleDateFormat.format(new Date());
endTime = now + " 23:59:59";
startTime = calculationTime(now, -6, "yyyy-MM-dd");
}
// 最多统计30天【包括首尾】
if (duringDays < 0) {
throw new BadRequestException("开始时间不能大于结束时间");
} else if (duringDays > 29) {
throw new BadRequestException("最多展示近30天数据");
}
// 2.数据查询
// to查询日期和参与次数
List<TurntableDailyDataResultBO> turntableDailyDataResultBOList = activityTurntableJoinLogMapper.queryJoinTimes(endTime, startTime, turntableJoinLogQueryVO.getActivityId());
// to查询日期和参与人数
List<TurntableDailyDataResultBO> joinPeopleCountList = activityTurntableJoinLogMapper.queryJoinPeopleCount(endTime, startTime, turntableJoinLogQueryVO.getActivityId());
// 3.日期不存在的补充0
List<TurntableDailyStatisticsDTO> dailyStatisticsDTOList = new ArrayList<>();
for (int i = 0; i <= duringDays; i++) {
TurntableDailyStatisticsDTO dailyStatisticsDTO = new TurntableDailyStatisticsDTO();
String resTime = calculationTime(endTime, -i, "MM-dd");
dailyStatisticsDTO.setJoinTime(resTime);
// 判断列表是否有该日期：日期存在则当前日期下一定同时存在参与次数和人数
boolean flag = false;
for (int j = 0; j < turntableDailyDataResultBOList.size(); j++) {
TurntableDailyDataResultBO item = turntableDailyDataResultBOList.get(j);
if (item.getJoinTime().equals(resTime)) {
flag = true;
// 参与次数
dailyStatisticsDTO.setJoinTimes(item.getCount());
// 参与人数
dailyStatisticsDTO.setJoinPeople(joinPeopleCountList.get(j).getCount());
}
}
// 当天不存在 赋值0
if (!flag) {
dailyStatisticsDTO.setJoinTimes(0);
dailyStatisticsDTO.setJoinPeople(0);
}
dailyStatisticsDTOList.add(dailyStatisticsDTO);
}
// 4.to封装结果数据
TurntableDailyDataResultVO turntableDailyDataResultVO = new TurntableDailyDataResultVO();
List<String> joinDateList = new ArrayList<>();
List<Integer> joinTimesList = new ArrayList<>();
List<Integer> peopleCountList = new ArrayList<>();
// 封装参与日期和参与次数 参与人数
dailyStatisticsDTOList.forEach(item -> {
joinDateList.add(item.getJoinTime());
joinTimesList.add(item.getJoinTimes());
peopleCountList.add(item.getJoinPeople());
});
// 赋值给结果vo
turntableDailyDataResultVO.setJoinDateList(joinDateList);
turntableDailyDataResultVO.setJoinTimesList(joinTimesList);
turntableDailyDataResultVO.setJoinPeopleList(peopleCountList);
return turntableDailyDataResultVO;
}
//-----------两个工具方法
/**
* @Description: 计算两个日期的相隔天数，采用四舍 退1 如 [2022-09-20 11:30，2022-09-22 15:21] 返回2 不是3
* @author HuiLong.Ding
* @date 2022/9/26 16:08
*/
private Integer daysBetween(String date1str, String date2str) throws ParseException {
log.info("计算两个日期的相隔天数入参：date1str = {},date2str = {} ", date1str, date2str);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date1 = format.parse(date1str);
Date date2 = format.parse(date2str);
return (int) ((date1.getTime() - date2.getTime()) / (1000 * 3600 * 24));
}
/**
* @Description: 推算时间
* @param timeStr 开始时间
* @param intervalTime 间隔时间 eg: -1 往前推1 前， 5 往后推5天
* @return
* @author HuiLong.Ding
* @date 2022/9/29 11:16
*/
private String calculationTime(String timeStr, Integer intervalTime,String formatStr) {
log.info("根据开始时间推算结束时间入参：timeStr = {},intervalTime = {} ", timeStr, intervalTime);
SimpleDateFormat sdf = new SimpleDateFormat(formatStr);
Date nowDate = DateUtil.parse(timeStr);
Calendar calendar = Calendar.getInstance();
calendar.setTime(nowDate);
// 推移
calendar.add(Calendar.DATE, intervalTime);
Date updateDate2 = calendar.getTime();
String resTime = sdf.format(updateDate2);
return resTime;
}
2.2.2 方案二
通过mysql生成连续的日期表，再和自己的表关联查询
分析代码
通过下面代码生成连续日期
SELECT
@date := DATE_ADD( @date, INTERVAL - 1 DAY ) days
FROM
( SELECT @date := DATE_ADD( now(), INTERVAL 1 DAY ) FROM activity_turntable_join_log LIMIT 7 ) time
效果如下
再和自己的表联表查询
SELECT
DATE_FORMAT(days.days,'%m-%d') days,
IFNULL(log.c,0) AS 参与人数
FROM
(
SELECT
@date := DATE_ADD( @date, INTERVAL - 1 DAY ) days
FROM
( SELECT @date := DATE_ADD( now(), INTERVAL 1 DAY ) FROM activity_turntable_join_log LIMIT 30 ) time
) AS days
LEFT JOIN (
SELECT count(user_mark) as c,log2.join_time FROM
(SELECT *,count(user_mark) FROM activity_turntable_join_log atjl where atjl.activity_id = 2 GROUP BY TO_DAYS( atjl.join_time ), atjl.user_mark) log2
GROUP BY join_time
) log ON TO_DAYS( log.join_time ) = TO_DAYS( days.days )
group by TO_DAYS(days.days) ORDER BY days desc;
弊端：
数据库需要数据才能实现，比如想生成20个日期，数据库至少需要20条数据，所以我猜这个生成日期是通过遍历实现的
mysql查出来就行，其他的vo封装和上面方案一类似
扫一扫
6019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
