echarts按时间连续统计

旧城等待， 2023-01-12 10:06 241阅读 0赞

按时间统计,如果只是简单的group by来查询可能结果是不连续的.

解决方法有两种,一种是写复杂的sql去查询,第二种就是在代码中去统计.

本文讨论的是第二种方法.

提供一个工具类,依赖hutool架包

import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author jiangli
     * @since 2021/1/28 8:43
     */
    public class DateCoder {
    
        /**
         * 获取当前时间点的前N个小时的时间点,用于按小时统计
         */
        public static List<Date> byHour(Integer count) {
            List<Date> list = new ArrayList<>();
            Date date = DateUtil.date();
            for (int i = 0; i < count; i++) {
                list.add(DateUtil.offsetHour(date, -i));
            }
            return list;
        }
    
        /**
         * 获取当前小时的开始时间
         */
        public static Date beginOfHour(Date date) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.set(Calendar.MINUTE, 0);
            calendar.set(Calendar.SECOND, 0);
            return DateUtil.date(calendar.getTime());
        }
    
        /**
         * 获取当前小时的开始时间
         */
        public static Date endOfHour(Date date) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.set(Calendar.MINUTE, 59);
            calendar.set(Calendar.SECOND, 59);
            return DateUtil.date(calendar.getTime());
        }
    
        /**
         * 获取当前时间点前的N天时间点,用于按天统计
         */
        public static List<Date> byDay(Integer count) {
            List<Date> list = new ArrayList<>();
            Date date = DateUtil.date();
            for (int i = 0; i < count; i++) {
                list.add(DateUtil.offsetDay(date, -i));
            }
            return list;
        }
    
        /**
         * 获取当前时间的前N个月的时间点,用于按月统计
         */
        public static List<Date> byMonth(Integer count) {
            List<Date> list = new ArrayList<>();
            Date date = DateUtil.date();
            for (int i = 0; i < count; i++) {
                list.add(DateUtil.offsetMonth(date, -i));
            }
            return list;
        }
    
    }

按时、天、月进行统计

/**
         * 根据时间范围查询数据
         */
        @Override
        public Map<String, Object> getChartData(String type) {
            Map<String, Object> map = new HashMap<>();
            List<String> timeList = new ArrayList<>();
            List<Integer> inDataList = new ArrayList<>();
            List<Integer> outDataList = new ArrayList<>();
    
            if ("hour".equals(type)) {
                List<Date> dates = DateCoder.byHour(6);
                for (Date date : dates) {
                    timeList.add(DateUtil.format(date, "yyyy/MM/dd HH"));
                    // 进场统计
                    inDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getInsertDate, DateCoder.beginOfHour(date))
                            .le(HealthSummary::getInsertDate, DateCoder.endOfHour(date))
                    ));
                    // 出场统计
                    outDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getOutTime, DateCoder.beginOfHour(date))
                            .le(HealthSummary::getOutTime, DateCoder.endOfHour(date))
                    ));
                }
            } else if ("day".equals(type)) {
                List<Date> dates = DateCoder.byDay(7);
                for (Date date : dates) {
                    timeList.add(DateUtil.format(date, "yyyy/MM/dd"));
                    // 进场统计
                    inDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getInsertDate, DateUtil.beginOfDay(date))
                            .le(HealthSummary::getInsertDate, DateUtil.endOfDay(date))
                    ));
                    // 出场统计
                    outDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getOutTime, DateUtil.beginOfDay(date))
                            .le(HealthSummary::getOutTime, DateUtil.endOfDay(date))
                    ));
                }
            } else {
                List<Date> dates = DateCoder.byMonth(12);
                for (Date date : dates) {
                    timeList.add(DateUtil.format(date, "yyyy/MM"));
                    // 进场统计
                    inDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getInsertDate, DateUtil.beginOfMonth(date))
                            .le(HealthSummary::getInsertDate, DateUtil.endOfMonth(date))
                    ));
                    // 出场统计
                    outDataList.add(mapper.selectCount(new LambdaQueryWrapper<HealthSummary>().eq(HealthSummary::getDeleted, 0)
                            .ge(HealthSummary::getOutTime, DateUtil.beginOfMonth(date))
                            .le(HealthSummary::getOutTime, DateUtil.endOfMonth(date))
                    ));
                }
            }
    
            map.put("timeList", timeList);
            map.put("inDataList", inDataList);
            map.put("outDataList", outDataList);
            return map;
        }

最后的结果

{
        "success": true,
        "msg": "操作成功",
        "data": {
            "outDataList": [
                0,
                0,
                0,
                0,
                0,
                0,
                0
            ],
            "timeList": [
                "2021/01/28",
                "2021/01/27",
                "2021/01/26",
                "2021/01/25",
                "2021/01/24",
                "2021/01/23",
                "2021/01/22"
            ],
            "inDataList": [
                1,
                16,
                14,
                16,
                11,
                51,
                22
            ]
        }
    }

timeList作为echarts的横轴坐标,inDataList和outDataList作为对应的数据,即可展示了.