SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据

@[TOC]

摘要

1、今日数据： 以2h为间隔，左间隔时间=< “条数” <当下最近时间右间隔时间，比如，[00：00, 02:00)
2、过去一天数据：以2h为间隔，左间隔时间=< ”条数“ <右间隔时间 （注意查询的是过去一整天的数据不包括今天的）
3、过去7天数据： 以1天/单位，记录当天的预警信息总条数截至到当前时间,比如，从今天往前推6天记录每天总条数
4、过去30天数据：以5天/单位，左间隔时间=< “条数” <右间隔时间 比如，[2020-03-19, 2020-03-24)
5、过去半年数据：以每个月/单位，查询当月往前推半年数据，比如，2020-03-24查询的是“2019-10 -> 2020-03”
6、过去1年数据： 以每个月/单位，查询当前月往前推一年数据截止到上个月数据，比如2020-03-24查询的是“2019-03 -> 2020-02”

图片案例

代码

描述：代码没有重新中文整理思路，看着有点乱，敬请谅解

整理思路：

第一步：根据查询的范围类型优先构建list，包含 list初始化大小，x：时间字符串， y：数量条数，默认为0

第二步：查询SQL，对应填充list，返回前端即可

工具类：==DateTool==

package com.geespace.microservices.data.metrics.config.server.common.tools;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.geespace.microservices.data.metrics.config.server.bean.dto.ReportDto;
import com.geespace.microservices.data.metrics.config.server.common.constant.NumberDictionary;
import com.geespace.microservices.data.metrics.config.server.common.enums.ListEnum;

import lombok.extern.slf4j.Slf4j;

/**
 * 日期工具类
 * @Author: liudz
 * @Date: 2019-12-12
 **/
@Slf4j
 public final class DateTool {
    /**
     * 构造函数
     */
    private DateTool() {}

    /**
       *  检测一个字符串是否是时间格式
       * @param str 请求字符串
       * @author liudz
       * @date 2019/12/17
       * @return 执行结果
       **/
    public static boolean isValidDate(String str) {
        boolean convertSuccess = true;
        // 指定日期格式为四位年/两位月份/两位日期，注意yyyy/MM/dd区分大小写；设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01
        SimpleDateFormat format = new SimpleDateFormat("HH:mm");
        try {
            format.setLenient(false);
            format.parse(str);
        } catch (Exception e) {
            convertSuccess = false;
        }
        return convertSuccess;
    }

    /**
     *  accord( a dateStr and number and type) return a number ago of a newDateStr
     * @param dateStr dateStr
     * @param number number
     * @param type type
     * @author liudz
     * @date 2020/3/17
     * @return newDateStr
     **/
    public static String returnANumberAgoDate(String dateStr, Integer number, int type) {
        String newDateStr = "";
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date;
            date = simpleDateFormat.parse(dateStr);
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            if (type == ListEnum.ONE.getValue() || type == ListEnum.TWO.getValue() || type == ListEnum.THREE.getValue() || type == ListEnum.FOUR.getValue()) {
                calendar.add(Calendar.DATE, number);
            } else if (type == ListEnum.FIVE.getValue()) {
                calendar.add(Calendar.MONTH, number);
            }  else if (type == ListEnum.SIX.getValue()) {
                calendar.add(Calendar.YEAR, number);
                calendar.add(Calendar.MONTH, 1);
            }
            date = calendar.getTime();
            newDateStr = simpleDateFormat.format(date);
        } catch (ParseException e) {
            log.error(e.getMessage());
        }
        return newDateStr;
    }
    
    /**
     *  according a dateStr returns a map containing dateStrKey
     * @param type type
     * @author liudz
     * @date 2020/3/17
     * @return map
     **/
    public static List<ReportDto> returnDateMap(int type) {
        List<ReportDto> list = new ArrayList<>();
        if (type == ListEnum.ONE.getValue()) {
            list = returnDateArrType1(list);
        }  else if (type == ListEnum.TWO.getValue()) {
            list = returnDateArrType2(list);
        } else if (type == ListEnum.THREE.getValue()) {
            list = returnDateArrType3(list, type);
        } else if (type == ListEnum.FOUR.getValue()) {
            list = returnDateArrType4(list, type);
        } else if (type == ListEnum.FIVE.getValue()) {
            list = returnDateArrType5(list, type);
        } else {
            list = returnDateArrType6(list, type);
        }
        return list;
    }

    /**
     * 报表3-1，type=1,查询今天数据
     * @param list list
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType1(List<ReportDto> list) {
        int count = 1;
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("HH");
        String format = simpleDateFormat.format(date);
        int arrayLength = Integer.valueOf(format) / NumberDictionary.TWO + NumberDictionary.ONE;
        do {
            joinListParam(count, list);
            count++;
        } while (count <= arrayLength);

        return list;
    }
    /**
     *  joinListParam
     * @param arrayLength arrayLength
     * @param list list
     * @author liudz
     * @date 2020/8/27
     **/
    public static void joinListParam(int arrayLength, List<ReportDto> list) {
        ReportDto reportDto = new ReportDto();
        switch (arrayLength) {
            case NumberDictionary.ONE:
                reportDto.setDateStr("00:00-02:00");
                break;
            case NumberDictionary.TWO:
                reportDto.setDateStr("02:00-04:00");
                break;
            case NumberDictionary.THREE:
                reportDto.setDateStr("04:00-06:00");
                break;
            case NumberDictionary.FOUR:
                reportDto.setDateStr("06:00-08:00");
                break;
            case NumberDictionary.FIVE:
                reportDto.setDateStr("08:00-10:00");
                break;
            case NumberDictionary.SIX:
                reportDto.setDateStr("10:00-12:00");
                break;
            case NumberDictionary.SEVEN:
                reportDto.setDateStr("12:00-14:00");
                break;
            case NumberDictionary.EIGHT:
                reportDto.setDateStr("14:00-16:00");
                break;
            case NumberDictionary.NINE:
                reportDto.setDateStr("16:00-18:00");
                break;
            case NumberDictionary.TEN:
                reportDto.setDateStr("18:00-20:00");
                break;
            case NumberDictionary.ELEVEN:
                reportDto.setDateStr("20:00-22:00");
                break;
            case NumberDictionary.TWELVE:
                reportDto.setDateStr("22:00-23:59");
                break;
            default:
                throw new RuntimeException("no suck this arrayLength!");
        }
        list.add(reportDto);
    }
    /**
     * 报表3-1，type=2,查询昨天数据
     * @param list list
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType2(List<ReportDto> list) {
        int count = 1;
        do {
            joinListParam(count, list);
            count++;
        } while (count <= NumberDictionary.TWELVE);
        return list;
    }
    /**l
     * 报表3-3，type=3,查询过去7天数据
     * @param list list
     * @param type type
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType3(List<ReportDto> list, int type) {
        int count = 0;
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = DateTool.returnANumberAgoDate(simpleDateFormat.format(date), ListEnum.NEGATIVE_SIX.getValue(), type);
        do {
            try {
                ReportDto reportDto = new ReportDto();
                reportDto.setDateStr(dateStr);
                list.add(reportDto);
                date = simpleDateFormat.parse(dateStr);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                date = calendar.getTime();
                dateStr = simpleDateFormat.format(date);
                count++;
            } catch (ParseException e) {
                log.error(e.getMessage());
            }
        } while (count < ListEnum.SEVEN.getValue());
        return list;
    }
    /**
     * 报表3-4，type=4,查询过去30天数据
     * @param list list
     * @param type type
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType4(List<ReportDto> list, int type) {
        int count = 0;
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = DateTool.returnANumberAgoDate(simpleDateFormat.format(date), ListEnum.NEGATIVE_TWENTYNINE.getValue(), type);
        do {
            try {
                date = simpleDateFormat.parse(dateStr);
                String oldDateStr = dateStr;
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, ListEnum.FOUR.getValue());
                date = calendar.getTime();
                dateStr = simpleDateFormat.format(date);
                ReportDto reportDto = new ReportDto();
                reportDto.setDateStr(oldDateStr + "~" +  simpleDateFormat.format(date));
                list.add(reportDto);
                date = simpleDateFormat.parse(dateStr);
                calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, ListEnum.ONE.getValue());
                date = calendar.getTime();
                dateStr = simpleDateFormat.format(date);
                count++;
            } catch (ParseException e) {
                log.error(e.getMessage());
            }
        } while (count < ListEnum.SIX.getValue());
        return list;
    }
    /**
     * 报表3-5，type=5, 查询过去半年数据
     * @param list list
     * @param type type
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType5(List<ReportDto> list, int type) {
        int count = 0;
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM");
        String dateStr = DateTool.returnANumberAgoDate(simpleDateFormat.format(date), ListEnum.NEGATIVE_FIVE.getValue(), type).substring(0, ListEnum.SEVEN.getValue());
        do {
            try {
                ReportDto reportDto = new ReportDto();
                reportDto.setDateStr(dateStr);
                list.add(reportDto);
                date = simpleDateFormat2.parse(dateStr);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.MONTH, 1);
                date = calendar.getTime();
                dateStr = simpleDateFormat.format(date).substring(0, ListEnum.SEVEN.getValue());
                count++;
            } catch (ParseException e) {
                log.error(e.getMessage());
            }
        } while (count < ListEnum.SIX.getValue());
        return list;
    }
    /**
     * 报表3-6，type=6, 查询过去一年数据
     * @param list list
     * @param type type
     * @author liudz
     * @date 2020/3/24
     * @return list
     **/
    public static List<ReportDto> returnDateArrType6(List<ReportDto> list, int type) {
        int count = 0;
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM");
        String dateStr = DateTool.returnANumberAgoDate(simpleDateFormat.format(date), ListEnum.NEGATIVE_ONE.getValue(), type).substring(0, ListEnum.SEVEN.getValue());
        do {
            try {
                ReportDto reportDto = new ReportDto();
                reportDto.setDateStr(dateStr);
                list.add(reportDto);
                date = simpleDateFormat2.parse(dateStr);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.MONTH, 1);
                date = calendar.getTime();
                dateStr = simpleDateFormat.format(date).substring(0, ListEnum.SEVEN.getValue());
                count++;
            } catch (ParseException e) {
                log.error(e.getMessage());
            }
        } while (count < ListEnum.TWELVE.getValue());
        return list;
    }
}

==AlarmMessageServiceImpl==

/**
     *  报表数据3，其中（type=1,今日数据 type=2，过去一天数据 type=3，过去7填数据 type=4，过去30数据 type=5，过去半年数据 type=6，过去一年数据 ）
     * @param reportParam reportParam
     * @author liudz
     * @date 2019/12/23
     * @return list
     **/
    public List<ReportDto> getReportDataPartThree(ReportParam reportParam) {
        log.debug("--AlarmMessageServiceImpl--getReportDataPartThree--begin--reportParam:{}", reportParam);
        List<ReportDto> list = new ArrayList<>();
        if (reportParam.getType() == 1) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree1(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType1(list, result);
        } else if (reportParam.getType() == ListEnum.TWO.getValue()) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree2(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType1(list, result);
        } else if (reportParam.getType() == ListEnum.THREE.getValue()) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree3(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType3(list, result);
        } else if (reportParam.getType() == ListEnum.FOUR.getValue()) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree4(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType4(list, result);
        } else if (reportParam.getType() == ListEnum.FIVE.getValue()) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree5(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType5(list, result);
        } else if (reportParam.getType() == ListEnum.SIX.getValue()) {
            List<ReportDataParamThree> result = alarmMessageMapper.getReportDataPartThree6(reportParam);
            list = DateTool.returnDateMap(reportParam.getType());
            list = fillMapType6(list, result);
        }
        log.debug("--AlarmMessageServiceImpl--getReportDataPartThree--end--result:{}", reportParam);
        return list;
    }
    /**
     * 报表3-1，type=1,查询今天数据 或 报表3-2，type=2,查询昨天数据
     * @param list  list
     * @param result result
     * @author liudz
     * @date 2020/3/24
     * @return fillMap
     **/
    public List<ReportDto> fillMapType1(List<ReportDto> list, List<ReportDataParamThree> result) {
        ReportDto reportDto;
        for (int i = 0; i < list.size(); i++) {
            for (int j = 0; j < result.size(); j++) {
                String[] split = list.get(i).getDateStr().split("-");
                if (result.get(j).getDateStr().compareTo(split[0]) >= 0 && result.get(j).getDateStr().compareTo(split[1]) < 0) {
                    reportDto = new ReportDto();
                    reportDto.setDateStr(list.get(i).getDateStr());
                    reportDto.setCount(list.get(i).getCount() + result.get(j).getCount());
                    list.set(i, reportDto);
                }
            }
        }
        return list;
    }
    /**
     * 报表3-3，type=3,查询过去7天数据
     * @param list  list
     * @param result result
     * @author liudz
     * @date 2020/3/24
     * @return fillMap
     **/
    public List<ReportDto> fillMapType3(List<ReportDto> list, List<ReportDataParamThree> result) {
        for (ReportDto item : list) {
            for (ReportDataParamThree r : result) {
                if (item.getDateStr().equals(r.getDateStr())) {
                    item.setCount(r.getCount());
                    list.set(list.indexOf(item), item);
                }
            }
        }
        return list;
    }
    /**
     * 报表3-4，type=4,查询过去30天数据
     * @param list  list
     * @param result result
     * @author liudz
     * @date 2020/3/24
     * @return fillMap
     **/
    public List<ReportDto> fillMapType4(List<ReportDto> list, List<ReportDataParamThree> result) {
        ReportDto reportDto;
        for (int i = 0; i < list.size(); i++) {
            for (int j = 0; j < result.size(); j++) {
                String[] split = list.get(i).getDateStr().split("~");
                if (result.get(j).getDateStr().compareTo(split[0]) >= 0 && result.get(j).getDateStr().compareTo(split[1]) <= 0) {
                    reportDto = new ReportDto();
                    reportDto.setDateStr(list.get(i).getDateStr());
                    reportDto.setCount(list.get(i).getCount() + result.get(j).getCount());
                    list.set(i, reportDto);
                }
            }
        }
        return list;
    }
    /**
     * 报表3-5，type=5, 查询过去半年数据
     * @param list  list
     * @param result result
     * @author liudz
     * @date 2020/3/24
     * @return fillMap
     **/
    public List<ReportDto> fillMapType5(List<ReportDto> list, List<ReportDataParamThree> result) {
        for (ReportDto item : list) {
            for (ReportDataParamThree r : result) {
                if (item.getDateStr().equals(r.getDateStr())) {
                    item.setCount(r.getCount());
                    list.set(list.indexOf(item), item);
                }
            }
        }
        return list;
    }
    /**
     * 报表3-6，type=6, 查询过去一年数据
     * @param list  list
     * @param result result
     * @author liudz
     * @date 2020/3/24
     * @return fillMap
     **/
    public List<ReportDto> fillMapType6(List<ReportDto> list, List<ReportDataParamThree> result) {
        for (ReportDto item : list) {
            for (ReportDataParamThree r : result) {
                if (item.getDateStr().equals(r.getDateStr())) {
                    item.setCount(r.getCount());
                    list.set(list.indexOf(item), item);
                }
            }
        }
        return list;
    }

==AlarmMessageSqlProvider==

/**
     *  报表数据部分1，查询总览预警情况
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartOne(ReportParam reportParam) {
        SQL sql = new SQL();
        if (DictionaryEnum.ZERO.getFiledString().equals(reportParam.getCategory())) {
            sql.SELECT("device_key, category, COUNT(*)");
        } else {
            sql.SELECT("task_name, category, COUNT(*), task_id");
        }
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("date(ctime) = CURDATE()");
        if (DictionaryEnum.ZERO.getFiledString().equals(reportParam.getCategory())) {
            sql.GROUP_BY("device_key");
        } else {
            sql.GROUP_BY("task_name, task_id");
        }
        return sql.toString();
    }
    /**
     *  报表数据部分2，查询详细预警情况
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartTwo(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("id, task_name, task_id, content, time, product_key, device_key, ctime");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("date(ctime) = CURDATE()");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.ORDER_BY("ctime desc");
        return sql.toString();
    }
    /**
     *  报表数据3-1：type=1,今日数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree1(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("concat(DATE_FORMAT(ctime, '%H'), ':00') as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("date(ctime) = CURDATE()");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("concat(DATE_FORMAT(ctime, '%H'), ':00')");
        return sql.toString();
    }
    /**
     *  报表数据3-2：type=2,过去24小时数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree2(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("concat(DATE_FORMAT(ctime, '%H'), ':00') as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("TO_DAYS(NOW()) - TO_DAYS(ctime) = 1");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("concat(DATE_FORMAT(ctime, '%H'), ':00')");
        return sql.toString();
    }
    /**
     *  报表数据3-3：type=3,过去7天数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree3(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("SUBSTRING(ctime,1,10) as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(ctime)");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("SUBSTRING(ctime,1,10)");
        return sql.toString();
    }
    /**
     *  报表数据3-4：type=4,过去30天数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree4(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("SUBSTRING(ctime,1,10) as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(ctime)");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("SUBSTRING(ctime,1,10)");
        return sql.toString();
    }
    /**
     *  报表数据3-5：type=5,过去半年数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree5(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("DATE_FORMAT(ctime,'%Y-%m') as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("DATE_SUB(concat(SUBSTRING(CURDATE(),1,7), '-01'), INTERVAL 5 MONTH) <= date(ctime)");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("DATE_FORMAT(ctime,'%Y-%m')");
        return sql.toString();
    }
    /**
     *  报表数据3-6：type=6,过去1年数据
     * @param  reportParam 请求参数
     * @author liudz
     * @date 2019/12/2
     * @return 执行结果
     **/
    public String getReportDataPartThree6(ReportParam reportParam) {
        SQL sql = new SQL();
        sql.SELECT("DATE_FORMAT(ctime,'%Y-%m') as dateStr,count(*) as count");
        sql.FROM("ge_alarm_message");
        sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}");
        sql.WHERE("category = #{category,jdbcType=VARCHAR}");
        sql.WHERE("DATE_FORMAT(DATE_SUB(concat(SUBSTRING(CURDATE(),1,7), '-01') ,INTERVAL 1 year),'%Y-%m-%d') <= date(ctime)");
        if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) {
            sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}");
        }
        if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) {
            sql.WHERE("task_id = #{taskId}");
        }
        sql.GROUP_BY("DATE_FORMAT(ctime,'%Y-%m')");
        return sql.toString();
    }

重要信息

• 官网：https://ais.cn/u/vEbMBz