时间范围内按某个刻度取各个刻度间的数据

最新推荐文章于 2022-03-28 15:02:03 发布

Megamind_HL

最新推荐文章于 2022-03-28 15:02:03 发布

阅读量257

点赞数

分类专栏： java Sql 文章标签：时间处理刻度查询数据查询 SQL 时间工具类

本文链接：https://blog.csdn.net/Megamind_HL/article/details/119891244

版权

Sql 同时被 2 个专栏收录

21 篇文章 0 订阅

订阅专栏

java

19 篇文章 1 订阅

订阅专栏

	在实际开发中我们可能会遇到取时间范围内刻度下各个刻度的数据，首先，定义刻度类型

@Data
public class RainQueryParamVo implements Serializable{
	//分钟
	public static final String BUSI_TYPE_MINUTE = "MIN";
	//小时
	public static final String BUSI_TYPE_HOUR = "H";
	//天
	public static final String BUSI_TYPE_DAY = "D";
	//旬
	public static final String BUSI_TYPE_XUN = "X";
	//月
	public static final String BUSI_TYPE_MONTH = "M";
	//年
	public static final String BUSI_TYPE_YEAR = "Y";
	private Date start;
	private Date end;
	//查询类型
	private String busiType;
	//分钟值:5、10、20、30...等
	private Integer mtph;
	//小时值:1、3、6、12...等
	private Integer tph;	
}

	其次：处理时间工具类

 private Map<String, Object> setQueryRainsRainParam(RainQueryParamVo paramVo, Map<String, Object> queryParam) {
        Calendar caS = Calendar.getInstance();
        Calendar caE = Calendar.getInstance();
        caS.setTime(paramVo.getStart());
        caE.setTime(paramVo.getEnd() == null ? paramVo.getStart() : paramVo.getEnd());
        List<Date> stmList = new ArrayList<>();
        List<Date> etmList = new ArrayList<>();
        String busiType = paramVo.getBusiType();
        switch (busiType.toUpperCase()) {
            //按分查询
            case RainQueryParamVo.BUSI_TYPE_MINUTE:
                int minute = paramVo.getMtph(), min = 0;
                while (true) {
                    Date dNewStart = DateUtil.add(paramVo.getStart(), Calendar.MINUTE, min * minute);
                    Date dNewEnd = DateUtil.add(paramVo.getStart(), Calendar.MINUTE, minute * (min + 1));
                    min++;
                    if (dNewEnd.getTime() > paramVo.getEnd().getTime()) {
                        break;
                    }
                    stmList.add(dNewStart);
                    etmList.add(dNewEnd);
                }
                break;

            //按小时查询
            case RainQueryParamVo.BUSI_TYPE_HOUR:
                int hourStep = paramVo.getTph(), h = 0;
                while (true) {
                    Date dNewStart = DateUtil.add(paramVo.getStart(), Calendar.HOUR_OF_DAY, h * hourStep);
                    Date dNewEnd = DateUtil.add(paramVo.getStart(), Calendar.HOUR_OF_DAY, hourStep * (h + 1));
                    h++;
                    if (dNewEnd.getTime() > paramVo.getEnd().getTime()) {
                        break;
                    }
                    stmList.add(dNewStart);
                    etmList.add(dNewEnd);
                }
                break;
            //按天查询
            case RainQueryParamVo.BUSI_TYPE_DAY:
                Double intevalDay = DateUtil.getDiffForDay(caS.getTime(), caE.getTime(), true) + 1;
                for (int i = 0; i < intevalDay; i++) {
                    Date dNewStart = DateUtil.add(paramVo.getStart(), Calendar.DAY_OF_MONTH, i);
                    Calendar calStart = Calendar.getInstance();
                    calStart.setTime(dNewStart);
                    calStart.set(Calendar.HOUR_OF_DAY, 8);
                    Date dNewEnd = DateUtil.add(paramVo.getStart(), Calendar.DAY_OF_MONTH, i + 1);
                    Calendar calEnd = Calendar.getInstance();
                    calEnd.setTime(dNewEnd);
                    calEnd.set(Calendar.HOUR_OF_DAY, 8);
                    stmList.add(calStart.getTime());
                    etmList.add(calEnd.getTime());
                }
                break;
            //按旬查询
            case RainQueryParamVo.BUSI_TYPE_XUN:
                Double xIntervalDay = DateUtil.getDiffForDay(paramVo.getStart(), paramVo.getEnd(), true) + 1;
                for (int i = 0; i < xIntervalDay; i++) {
                    if (i == 0) {
                        stmList.add(paramVo.getStart());
                        if (xIntervalDay == 1) {
                            etmList.add(DateUtil.add(paramVo.getStart(), Calendar.DAY_OF_MONTH, 1));
                        }
                    } else if (i == xIntervalDay.intValue() - 1) {
                        etmList.add(paramVo.getEnd());
                    } else {
                        Calendar current = Calendar.getInstance();
                        current.setTime(DateUtil.add(paramVo.getStart(), Calendar.DAY_OF_MONTH, i));
                        int currentDate = current.get(Calendar.DAY_OF_MONTH);
                        if (currentDate == 1) {
                            etmList.add(current.getTime());
                            stmList.add(current.getTime());
                        } else if (currentDate == 10) {
                            etmList.add(current.getTime());
                            stmList.add(current.getTime());
                        } else if (currentDate == 20) {
                            etmList.add(current.getTime());
                            stmList.add(current.getTime());
                        }
                    }
                }
                break;
            //按月查询
            case RainQueryParamVo.BUSI_TYPE_MONTH:
                int intervalMonths = DateUtil.getDiffForMonth(paramVo.getStart(), paramVo.getEnd()) + 1;
                for (int i = 0; i < intervalMonths; i++) {
                    Date dNewStart = DateUtil.add(paramVo.getStart(), Calendar.MONTH, i);
                    Date dNewEnd = DateUtil.add(paramVo.getStart(), Calendar.MONTH, i + 1);
                    stmList.add(dNewStart);
                    etmList.add(dNewEnd);
                }
                break;
            default:
                break;
        }
        queryParam.put("stmList", stmList);
        queryParam.put("etmList", etmList);
        queryParam.put("stm", paramVo.getStart());
        queryParam.put("etm", etmList.size() == 0 ? paramVo.getEnd() : etmList.get(etmList.size() - 1));
        return queryParam;
    }

	最后在执行sql查询

    <select id="query" resultType="com.alibaba.fastjson.JSONObject">
        select * from
         ( select tabel.name, 
	        <foreach collection="params.stmList" item="item" index="index">
	            (select sum(字段) from table t1 where t1.字段= tt.字段 and tm > #{item} and tm <![CDATA[ <=  ]]>#{params.etmList[${index}]}) as new${index},
	        </foreach>
       	 (select sum (字段) from table t1  pptn where t1.字段= tt.字段 and tm > #{params.start} and tm <![CDATA[ <= ]]>#{params.end}) as total
        from table tt
   		)t 
    </select>