日统计:
利用GROUP BY对日期进行分组,查询用户的登录量和访问量
SELECT DATE_FORMAT(access_time, '%Y-%m-%d') as statisticsDate,
count(id) as browseStatisticsNum,
count(DISTINCT user_id) as visitorStatisticsNum
FROM mbr_login_log
WHERE status = 1
AND DATE_FORMAT(access_time, '%Y-%m-%d') BETWEEN #{param.startDate} AND #{param.endDate}
GROUP BY DATE_FORMAT(access_time, '%Y-%m-%d');
实际效果:
周统计:
MySQL数据库使用YEARWEEK()函数,它将返回一个形如 YYYYWW 的字符串,其中 YYYY 是年份,WW 是一年中第几周(从 01 到 53),并且周日被视为一周的第一天。
如果您希望指定不同的周开始日,可以在 YEARWEEK() 函数中使用第二个可选参数 mode 来改变计算方式。例如,如果您希望以周一作为一周的开始,可以使用 YEARWEEK(date, 1)。
代码如下:
java代码将 YYYYWW的格式转换为yyyy/MM/dd - yyyy/MM/dd的日期范围格式
private List<LoginLogStatisticsDto> weeklyStatistics(LoginLogStatisticsParam param) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DateUtils.SIMPLE_DATE_FORMAT2);
List<LoginLogStatisticsDto> dtoList = this.baseMapper.weeklyStatistics(param);
dtoList.forEach(loginLogStatisticsDto -> {
String statisticsDate = loginLogStatisticsDto.getStatisticsDate();
//年份
String year = statisticsDate.substring(0, 4);
//月份
String week = statisticsDate.substring(4);
LocalDate startOfWeek = LocalDate.now()
.withYear(Integer.parseInt(year))
.with(WeekFields.ISO.weekOfYear(), Long.parseLong(week))
.with(WeekFields.ISO.dayOfWeek(), 1);
LocalDate endOfWeek = startOfWeek.plusDays(6);
loginLogStatisticsDto.setStatisticsDate(startOfWeek.format(formatter) + "-" + endOfWeek.format(formatter));
});
return dtoList;
}
sql将开始日期和结束日期转换为 YYYYWW的格式并进行分组,得到每周的数据量
SELECT YEARWEEK(access_time, 1) AS statisticsDate,
COUNT(id) AS browseStatisticsNum,
COUNT(DISTINCT user_id) AS visitorStatisticsNum
FROM mbr_login_log
WHERE status = 1
AND YEARWEEK(access_time, 1) BETWEEN YEARWEEK(#{param.startDate}, 1) AND YEARWEEK(#{param.endDate}, 1)
GROUP BY YEARWEEK(access_time, 1);
实际效果:
Oracle数据库可以使用to_char()函数中的IW,WW 周别显示
可参考关于Oracle to_char()函数中的IW,WW 周别显示_to_char iw-CSDN博客
月统计:
使用DATE_FORMAT()函数,将日期转换为%Y-%m格式,对月进行分组
SELECT DATE_FORMAT(access_time, '%Y-%m') as statisticsDate,
count(id) as browseStatisticsNum,
count(DISTINCT user_id) as visitorStatisticsNum
FROM mbr_login_log
WHERE status = 1
AND DATE_FORMAT(access_time, '%Y-%m') BETWEEN DATE_FORMAT(#{param.startDate}, '%Y-%m') AND DATE_FORMAT(#{param.endDate}, '%Y-%m')
GROUP BY DATE_FORMAT(access_time, '%Y-%m');
实际效果: