前言

报表需要获取当前时间下的区间季度,不是常规的Q1，Q2这种
举例

     log.info(getQuarterByDate4("2021-2", "2022-2", 1).toString());
     log.info(getQuarterByDate4("2021-2", "2021-2", 1).toString());
     log.info(getQuarterByDate4("2021-4", "2021-8", 1).toString());
     log.info(getQuarterByDate4("2021-8", "2021-9", 1).toString());
     log.info(getQuarterByDate4("2021-3", "2022-3", 1).toString());

==>>输出为

 [2021-2~3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-1~2]
 [2021-2]
 [2021-Q2, 2021-7~8]
 [2021-8~9]
 [2021-3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-Q1]

工具类

 /**
     * 获取两个时间 的 区间季度
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param type 查询类型 1 季度 2半年 3 年
     * @return
     * @Author HezhezhiyuLe
     * @throws ParseException
     */
    public static List<String> getQuarterByDate4(String startTime, String endTime, Integer type) throws ParseException {
        //返回语句数组
        ArrayList quartersStr = new ArrayList();
        DateFormat df = new SimpleDateFormat("yyyy-MM");
        //获取开始结束的日历类
        Calendar cal = Calendar.getInstance();
        cal.setTime(df.parse(startTime));
        Calendar cal1 = Calendar.getInstance();
        cal1.setTime(df.parse(endTime));

        int startMonth = 0;
        //获取所有不重复月份对象数据
        List<TestDate> arr1 = new ArrayList<>();
        //比较时间
        while (cal1.getTime().after(cal.getTime()) || cal1.getTime().equals(cal.getTime())) {
            TestDate testDate = new TestDate();
            //程序中月份11月封顶
            startMonth = cal.get(Calendar.MONTH);
            testDate.setMonth(startMonth);
            testDate.setYear(cal.get(Calendar.YEAR));
            switch (startMonth) {
                case 0:
                case 1:
                case 2:
                    testDate.setQuarter(1);
                    break;
                case 3:
                case 4:
                case 5:
                    testDate.setQuarter(2);
                    testDate.setHalfyear(1);
                    break;
                case 6:
                case 7:
                case 8:
                    testDate.setQuarter(3);
                    break;
                case 9:
                case 10:
                case 11:
                    testDate.setQuarter(4);
                    testDate.setHalfyear(6);
                    break;
            }
            arr1.add(testDate);
            //递增一月
            cal.add(Calendar.MONTH, 1);
        }
        // 年 季度 月 表述关系
        Map<Integer, Map<Integer, List<TestDate>>> countsYears = new HashMap<>();
        //按季度分对象
        for (TestDate testDate : arr1) {
            //获取当前月所属季度
            int quarter = testDate.getQuarter();
            int year = testDate.getYear();
            //存储 季度 月
            Map<Integer, List<TestDate>> YMQ = countsYears.get(year);
            if (YMQ == null) {
                // 季度月
                Map<Integer, List<TestDate>> MQ = new HashMap<>();
                //单月
                List<TestDate> arr = new ArrayList<>();
                arr.add(testDate);
                MQ.put(quarter, arr);
                countsYears.put(year, MQ);
            } else {
                List<TestDate> testDates = YMQ.get(quarter);
                if (testDates == null) {
                    List<TestDate> arr = new ArrayList<>();
                    arr.add(testDate);
                    YMQ.put(quarter, arr);
                } else {
                    testDates.add(testDate);
                    YMQ.put(quarter, testDates);
                }
            }

        }
        //遍历当前年份 不重复
        for (Integer year : countsYears.keySet()) {
            //获得当前年的所有季度月
            Map<Integer, List<TestDate>> quarterMonths = countsYears.get(year);

            for (Integer quarter : quarterMonths.keySet()) {
                //获取当前季度 月份数组
                List<TestDate> testDates = quarterMonths.get(quarter);
                if (testDates == null) {
                    continue;
                }
                //当前月 不重复
                TestDate testDate = testDates.get(0);

                //替换为12进制
                int nowMonth = testDate.getMonth() + 1;
                //满3个月算一季度 Q1
                if (testDates.size() == 3) {
                    quartersStr.add(year + "-Q" + quarter);
                } else if (testDates.size() == 1) {
                    //只有一个月算当月 1
                    quartersStr.add(year + "-" + nowMonth);
                } else if (testDates.size() == 2) {
                    //一个季度中有两月算区间 2-3
                    quartersStr.add(year + "-" + nowMonth + "~" + (testDates.get(1).getMonth() + 1));
                }

            }
        }
        //返回组合语句 2021-2~3, 2021-Q2, 2021-Q3, 2021-Q4, 2022-1~2
        return quartersStr;
    }

SQL举例

 		<isEqual property="dateType" compareValue="quarter">
			select case 
			         when counts = 3 then year||'-Q'||quarter
			         when counts = 1 then year||'-'||month
			         else year||' '||minMonth||'-'||maxmonth
			       end showTitle,
			       dense_rank() over(order by YEAR,quarter) rn,
			       a.*
			from (
			  select YM, YEAR, MONTH, QUARTER,
			         count(1) over(partition by YEAR, QUARTER) counts,
			         max(month) over(partition by YEAR, QUARTER) maxMonth,
			         min(month) over(partition by YEAR, QUARTER) minMonth
			  from (
			      select distinct year||lpad(month,2,0) ym,year,month,
			             case 
			                when month between 1 and 3 then 1
			                when month between 4 and 6 then 2
			                when month between 7 and 9 then 3
			                when month between 10 and 12 then 4
			              end quarter
			      from dm_date
			      where year||lpad(month,2,0) between $beginDate$ and $endDate$
			  )
			) a
		</isEqual>