-- 按小时统计数据
需要简历一张有24小时的辅助表t_hours 关联需要统计的数据即可
select hour_time,ifnull(t.nomalNum,0)nomalNum from t_hours left join
-- 这里是原本的统计数据进行关联
(
select
count(*) AS num,
count( CASE WHEN pass_able = 1 THEN 1 ELSE NULL END ) AS nomalNum,
date_format( create_time, '%H' ) AS timeZones
FROM t_visitor
WHERE to_days( create_time ) = to_days(now())-4
GROUP BY timeZones
)t on t_hours.hour_time =t.timeZones
-- 这里控制小时到目前的时间
WHERE hour_time<=date_format( now(), '%H' )
ORDER BY hour_time
-- 一周内数据统计
方案一、建立一个有7天时间的列表根据已有时间进行统计
public List<ReportChartVisitorVo> getSenvenDate(){
List<ReportChartVisitorVo> reportVisitorVos = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
for (int i = 1; i < 8; i++) {
Date date = DateUtils.addDays(new Date(),-i);
String formatDate= sdf.format(date);
ReportChartVisitorVo reportVisitorVo =new ReportChartVisitorVo();
reportVisitorVo.setDayTime(formatDate);
reportVisitorVos.add(reportVisitorVo);
}
return reportVisitorVos;
}
方案二、统计时就建立一周内时间进行统计
List<ReportChartTerminalVo> reportChartTerminalVos =new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
for (int i = 1; i < 8; i++) {
Date date = DateUtils.addDays(new Date(),-i);
String dayTime =sdf.format(date);
paramMap.put("dayTime",dayTime);
ReportChartTerminalVo reportChartTerminalVo = reportTerminalMapper.terminalForChart(paramMap);
reportChartTerminalVo.setDayTime(dayTime);
reportChartTerminalVos.add(reportChartTerminalVo);
}
return reportChartTerminalVos;