Java 同环比计算相关逻辑

原创已于 2022-01-20 15:17:31 修改 · 2.4k 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

工作总结

于 2021-05-25 21:35:12 首次发布

本文介绍了如何在Java中实现同比和环比计算，避免在不同数据库中进行此类计算。通过排序和缓冲策略，分别计算了按月分组的年同比和月环比，详细展示了代码实现过程，并给出了SQL查询方式作为参考。

Java 同环比计算相关逻辑

相关概念
背景
实现思路
具体代码
SQL实现方式[Mysql]
- 按月进行分组同比 2022年/2020年各月数据同比
- 按月进行分组同比各月数据环比

背景

项目中使用了不同种数据库,为了减少工作量,不在数据库层进行同环比计算(需要查看不同数据库函数语法).所以决定在代码层面实现

实现思路

使用标记,或者缓冲避免多次循环数据
环比
1.降序排序
2.环比计算
循环结果集（不连续时间区间）
取标记位数据（标记位默认为1）使用下标
根据日期判断是不是相邻区间
如果是->直接计算，标志位➕1
如果不是->结果为0，标志位不变
时间复杂度O（n）

同比
1.升序排序
2.同比计算（不连续时间区间）
设置缓冲时间区间数据List<Map（以年，不同月为key）>
与缓冲区间判断年是否是相邻年（默认比较相邻年）
如果是->取对应的月数据
取到数据，计算
取不到，结果为0
如果不是->本年度所有月同比为0
时间复杂度为O（n）

具体代码

方法无法直接使用,需要结合自己逻辑改进


private List<Map<String, Object>> momYoyData(List<Map<String, Object>> obj,QueryDataSetBo bo){
        Map<String, Object> momCOUNTSet = bo.getMomCOUNTSet();
        //时间字段
        String fieldName = (String)momCOUNTSet.get("fieldName");
        String timeFieldKey=null;
        //时间字段类型【年，月，日，周，季，半年度】
        int type=(int)momCOUNTSet.get("type");
        switch (type){
            case 1:
                timeFieldKey="date_format(column, '%Y')".replace("column",fieldName);
                break;
            case 2:
                timeFieldKey="date_format(column, '%Y-%m')".replace("column",fieldName);
                break;
            case 3:
                timeFieldKey="date_format(column, '%Y-%m-%d')".replace("column",fieldName);
                break;
            case 4:
                timeFieldKey="date_format(column,'%Y-%u')".replace("column",fieldName);
                break;
            case 5:
                timeFieldKey="concat(date_format(column, '%Y'),'-',FLOOR((date_format(column, '%m')+2)/3))".replace("column",fieldName);
                break;
            case 6:
                timeFieldKey="concat(date_format(column, '%Y'),'-',CEIL((date_format(column, '%m'))/6))".replace("column",fieldName);
                break;
            default:
                break;
        }

        //数据字段
        List<Map<String, Object>> dataFieldName = (List<Map<String, Object>>)momCOUNTSet.get("dataFieldName");
        ArrayList<String> yoyfieldList = new ArrayList<>(); //同比字段list
        ArrayList<String> momfieldList = new ArrayList<>(); //环比字段list
        for (Map<String, Object> stringObjectMap : dataFieldName) {

            String value = String.valueOf(stringObjectMap.get("value"));
            String compute = String.valueOf(stringObjectMap.get("compute"));
            String dataFieldkey=value+"_"+compute;

            if ((int)stringObjectMap.get("momYoyType")==1){
                //同比
                yoyfieldList.add(dataFieldkey);
            }else if ((int)stringObjectMap.get("momYoyType")==2){
                //环比
                momfieldList.add(dataFieldkey);
            }
        }
        //同比
        //升序
        List<Map<String, Object>> yoyDataList =new ArrayList<>();
        List<Map<String, Object>> maps =new ArrayList<>(obj);
        String finalTimeFieldKey=timeFieldKey;
        Collections.sort(maps, new Comparator<Map<String, Object>>() {
                @Override
                public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                    String o11 = (String)o1.get(finalTimeFieldKey);
                    String o22 = (String)o2.get(finalTimeFieldKey);
                    return o11.compareTo(o22);
                }
            });
        if (yoyfieldList!=null&&yoyfieldList.size()>0){
            Map<String, Object> bufferMap = new HashMap<>();
            for (int i = 0; i < maps.size(); i++) {
                Map<String, Object> newMap=new HashMap<>();
                Map<String, Object> map=maps.get(i);
                newMap.putAll(map);
                String time = (String) map.get(finalTimeFieldKey);
                String[] split = time.split("-");
                String year=split[0];
                String time2=split[1];
                String lastYear = getLastYear(year);
                if (bufferMap.containsKey(lastYear)&&bufferMap.get(lastYear)!=null){
                    HashMap<String, Object > dataMap = (HashMap<String, Object>)bufferMap.get(lastYear);
                    for (String yoyfield : yoyfieldList) {
                        if (dataMap.containsKey(time2)||dataMap.get(time2)!=null){
                            Map<String, Object> databufferMap = (Map<String, Object>)dataMap.get(time2);
                            if (map.containsKey(yoyfield)&&map.get(yoyfield)!=null&&databufferMap.containsKey(yoyfield)&&databufferMap.get(yoyfield)!=null){
                                BigDecimal p = new BigDecimal((Double) map.get(yoyfield));
                                BigDecimal q = new BigDecimal((Double) databufferMap.get(yoyfield));
                                BigDecimal rate = new BigDecimal(0.000000);
                                rate=(p).divide(q,3, RoundingMode.HALF_UP);
                                newMap.put(yoyfield+"_"+"yoy",Double.valueOf(rate.toString()));
                            }else {
                                newMap.put(yoyfield+"_"+"yoy",0);
                            }
                        }else {
                            newMap.put(yoyfield+"_"+"yoy",0);
                        }
                    }

                }else {
                    for (String yoyfield : yoyfieldList) {
                        newMap.put(yoyfield+"_"+"yoy",0);
                    }
                }

                yoyDataList.add(newMap);
                //操作缓冲MaP
                if (i==0){
                    Map<String, Object> dataMap=new HashMap<>();
                    dataMap.put(time2,map);
                    bufferMap.put(year,dataMap);
                }else{
                    if (bufferMap.containsKey(year)&&bufferMap.get(year)!=null){
                        HashMap<String, Object > dataMap = (HashMap<String, Object>)bufferMap.get(year);
                        dataMap.put(time2,map);
                        bufferMap.put(year,dataMap);
                    }else {
                        Map<String, Object> dataMap=new HashMap<>();
                        dataMap.put(time2,map);
                        bufferMap.put(year,dataMap);
                    }
                }
            }
        }


            //环比
            //升序
            List<Map<String, Object>> maps2 =new ArrayList<>(obj);
            Collections.sort(maps2, new Comparator<Map<String, Object>>() {
                @Override
                public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                    String o11 = (String)o1.get(finalTimeFieldKey);
                    String o22 = (String)o2.get(finalTimeFieldKey);
                    return o22.compareTo(o11);
                }
            });
            List<Map<String, Object>> momDataList =new ArrayList<>();
            if (momfieldList!=null&&momfieldList.size()>0){
                for (int i = 0; i < maps2.size(); i++) {
                    Map<String, Object> map=maps2.get(i);

                    Map<String, Object> newMap=new HashMap<>();
                    newMap.putAll(map);
                    if (i==maps2.size()-1){
                        for (String momfield : momfieldList) {
                            newMap.put(momfield+"_"+"mom",0);
                        }
                    }else {
                        Map<String, Object> nextMap=maps2.get(i+1);
                        String time = (String) map.get(finalTimeFieldKey);
                        String nextTime = (String) nextMap.get(finalTimeFieldKey);

                        if (isContinuous(time,nextTime,type)) {
                            for (String momfield : momfieldList) {
                                if (map.containsKey(momfield)&&map.get(momfield)!=null&&nextMap.containsKey(momfield)&&nextMap.get(momfield)!=null){
                                    BigDecimal p = new BigDecimal((Double) map.get(momfield));
                                    BigDecimal q = new BigDecimal((Double) nextMap.get(momfield));
                                    BigDecimal rate = new BigDecimal(0.000000);
                                    rate=(p).divide(q,3, RoundingMode.HALF_UP);
                                    newMap.put(momfield+"_"+"mom",Double.valueOf(rate.toString()));
                                }else {
                                    newMap.put(momfield+"_"+"mom",0);
                                }
                            }
                        }else {
                            for (String momfield : momfieldList) {
                                newMap.put(momfield+"_"+"mom",0);
                            }
                        }
                    }
                    momDataList.add(newMap);
                }
            }


        obj.addAll(momDataList);
        obj.addAll(yoyDataList);
        List<Map<String, Object>> merge = merge(obj, timeFieldKey);
        Collections.sort(merge, new Comparator<Map<String, Object>>() {
            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                String o11 = (String)o1.get(finalTimeFieldKey);
                String o22 = (String)o2.get(finalTimeFieldKey);
                return o11.compareTo(o22);
            }
        });
        return merge;
    }

    private String getLastYear(String year){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy");
        try {
            Date parse = simpleDateFormat.parse(year);
            Calendar cal=Calendar.getInstance();
            cal.setTime(parse);
            cal.add(Calendar.YEAR,-1);
            Date time = cal.getTime();
            return simpleDateFormat.format(time);
        } catch (ParseException e) {
            return null;
        }
    }

    private String getlastDay(String daystr){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date parse = simpleDateFormat.parse(daystr);
            Calendar c = Calendar.getInstance();
            c.setTime(parse);
            int day=c.get(Calendar.DATE);
            c.set(Calendar.DATE,day-1);
            return new SimpleDateFormat("yyyy-MM-dd").format(c.getTime());

        } catch (Exception e) {
            return null;
        }
    }

    private boolean isContinuous(String time,String nextTime,Integer type){

        boolean isContinuous=false;

        String[] split = time.split("-");
        String yearTime=split[0];
        String timeTime=split[1];
        String[] split2 = nextTime.split("-");
        String yearNextTime=split2[0];
        String timeNextTime=split2[1];

        switch (type){
            case 1:
                if (yearNextTime.equals(getLastYear(yearTime))){
                    isContinuous=true;
                }
                break;
            case 2:
                if (yearTime.equals(yearNextTime)){
                    if (Integer.valueOf(timeTime)-Integer.valueOf(timeNextTime)==1){
                        isContinuous=true;
                    }
                }if (yearNextTime.equals(getLastYear(yearTime))){
                    if (Integer.valueOf(timeTime)==1&&Integer.valueOf(timeNextTime)==12){
                        isContinuous=true;
                    }
                }
                break;
            case 3:
                if (getlastDay(time).equals(nextTime)){
                    isContinuous=true;
                }
                break;
            case 4:
                if (yearTime.equals(yearNextTime)){
                        if (Integer.valueOf(timeTime)-Integer.valueOf(timeNextTime)==1){
                            isContinuous=true;
                        }
                    }if (yearNextTime.equals(getLastYear(yearTime))){

                        if ((Integer.valueOf(timeTime)==1&&Integer.valueOf(timeNextTime)!=0&&(Integer.valueOf(timeNextTime)==52||Integer.valueOf(timeNextTime)==53))
                            ||(Integer.valueOf(timeTime)==0&&(Integer.valueOf(timeNextTime)==52||Integer.valueOf(timeNextTime)==53))
                        ){
                            isContinuous=true;
                        }
                    }
                break;
            case 5:
                if (yearTime.equals(yearNextTime)){
                        if (Integer.valueOf(timeTime)-Integer.valueOf(timeNextTime)==1){
                            isContinuous=true;
                        }
                    }if (yearNextTime.equals(getLastYear(yearTime))){
                    if (Integer.valueOf(timeTime)==1&&Integer.valueOf(timeNextTime)==4){
                        isContinuous=true;
                    }
                }
                break;
            case 6:
                if (yearTime.equals(yearNextTime)){
                        if (Integer.valueOf(timeTime)-Integer.valueOf(timeNextTime)==1){
                            isContinuous=true;
                        }
                    }if (yearNextTime.equals(getLastYear(yearTime))){
                    if (Integer.valueOf(timeTime)==1&&Integer.valueOf(timeNextTime)==2){
                        isContinuous=true;
                    }
                }
                break;
            default:
                break;
        }
        return isContinuous;
    }

    public static List<Map<String, Object>> merge(List<Map<String, Object>> m1,String mergeKey){
        Set<String> set = new HashSet<>();
        System.out.println("m1的数据格式是:"+m1);
        return m1.stream()
                .filter(map->map.get(mergeKey)!=null)
                .collect(Collectors.groupingBy(o->{
                    //暂存所有key
                    set.addAll(o.keySet());
                    //按mergeKey分组
                    return o.get(mergeKey).toString();
                }))
                .entrySet().stream().map(o->{
                    //合并
                    Map<String, Object> map = o.getValue().stream().flatMap(m->{
                        return m.entrySet().stream();
                    }).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (a,b)->b));
                    //为没有的key赋值0
                    set.stream().forEach(k->{
                        if(!map.containsKey(k)) map.put(k, 0);
                    });
                    return map;
                }).collect(Collectors.toList());

    }

SQL实现方式[Mysql]

在这里插入图片描述

-- 年【普通年】/【闰年】
SELECT
	date_format(time1, '%Y'),
	round( sum( time1_data ) / 1, 1 ) 
FROM
	time_test 
	WHERE time1  is not null
GROUP BY
date_format(time1, '%Y')


-- 月【普通年】/【闰年】
SELECT
	date_format(time1, '%Y-%m'),
	round( sum( time1_data ) / 1, 1 ) 
FROM
	time_test 
	WHERE time1  is not null
GROUP BY
date_format(time1, '%Y-%m')


-- 日【普通年】/【闰年】
SELECT
	date_format(time1, '%Y-%m-%d'),
	round( sum( time1_data ) / 1, 1 ) 
FROM
	time_test 
	WHERE time1  is not null
GROUP BY
date_format(time1, '%Y-%m-%d')



-- 季度 【普通年】/【闰年】 
SELECT
	concat(date_format(time1, '%Y'),'--',FLOOR((date_format(time1, '%m')+2)/3)),
	round( sum( time1_data ) / 1, 1 ) 
FROM
	time_test
WHERE time1  is not null	
GROUP BY
concat(date_format(time1, '%Y'),'--',FLOOR((date_format(time1, '%m')+2)/3))



-- 半年度 【普通年】/【闰年】
SELECT
	concat(date_format(time1, '%Y'),'--',CEIL((date_format(time1, '%m'))/6)),
	round( sum( time1_data ) / 1, 1 ) 
FROM
	time_test
WHERE time1  is not null	
GROUP BY
concat(date_format(time1, '%Y'),'--',CEIL((date_format(time1, '%m'))/6))


-- 周 【普通年】/【闰年】

SELECT
	DATE_FORMAT(time1,'%Y%u'),
	round( sum( time1_data ) / 1, 1 )
FROM
	time_test 
WHERE time1  is not null
GROUP BY
DATE_FORMAT(time1,'%Y%u')

按月进行分组同比 2022年/2020年各月数据同比

-- 按月进行分组同比 2022年/2020年各月数据同比
SELECT 
a1.time,
a1.`month`,
ifnull(round(a1.`DATA`/b1.`DATA`,2),0) AS 同比
FROM 
(
-- 2022年各月数据
SELECT 
time,
`DATA`,
SUBSTRING_INDEX(time,"-",-1) 	AS `month`
from (
SELECT
	date_format(time1, '%Y-%m') AS time,
	round( sum( time1_data ) / 1, 1 )	AS `DATA`
FROM
	time_test 
	WHERE time1  is not null 
	AND
	date_format(time1, '%Y')='2022'
GROUP BY
date_format(time1, '%Y-%m')
) a
) a1
LEFT JOIN
(
-- 2020年各月数据
SELECT 
time,
`DATA`,
SUBSTRING_INDEX(time,"-",-1) 	AS `month`
from (
SELECT
	date_format(time1, '%Y-%m') AS time,
	round( sum( time1_data ) / 1, 1 )	AS `DATA`
FROM
	time_test 
	WHERE time1  is not null 
	AND
	date_format(time1, '%Y')='2020'
	-- 非连续性数据
	 AND date_format(time1, '%Y-%m') !='2020-05'
GROUP BY
date_format(time1, '%Y-%m')
) b
) b1
ON a1.`month`=b1.`month`

按月进行分组同比各月数据环比

SELECT 
a1.time,
b1.time,
ifnull(round(a1.`DATA`/b1.`DATA`,2),0) AS 环比,
a1.`DATA`,
b1.`DATA`
FROM 
(
SELECT 
time,
`DATA`,
SUBSTRING_INDEX(time,"-",1) 	AS `year`,
SUBSTRING_INDEX(time,"-",-1) 	AS `month`
from (
SELECT
	date_format(time1, '%Y-%m') AS time,
	round( sum( time1_data ) / 1, 1 )	AS `DATA`
FROM
	time_test 
	WHERE time1  is not null 
GROUP BY
date_format(time1, '%Y-%m')
) a
) a1
LEFT JOIN
(
SELECT 
time,
`DATA`,
SUBSTRING_INDEX(time,"-",1) 	AS `year`,
SUBSTRING_INDEX(time,"-",-1) 	AS `month`
from (
SELECT
	date_format(time1, '%Y-%m') AS time,
	round( sum( time1_data ) / 1, 1 )	AS `DATA`
FROM
	time_test 
	WHERE time1  is not null 
GROUP BY
date_format(time1, '%Y-%m')
) b
) b1
ON a1.`year`=b1.`year` AND a1.`month`=b1.`month`+1
ORDER BY a1.time desc