关于java对查询结果进行环比指标计算的代码示例

Question

需求
想要统计环比指标（（本期数量-上期数量）/上期数量*100%） 假设下面是统计9月份的数据 如下所示

品牌	数量	环比
Bosh	1561	311.87%
Siemens	2278	-75.24%

问题
查询的时候 需要同时查询8月的数据 统计出8月的数量 然后才能进行环比指标的计算

{ "count" : 379.0, "brand" : "Bosch", "month" : "2017-08" } 
{ "count" : 1561.0, "brand" : "Bosch", "month" : "2017-09" }
{ "count" : 9202.0, "brand" : "Siemens", "month" : "2017-08" }
{ "count" : 2278.0, "brand" : "Siemens", "month" : "2017-09" }

怎么转换得到上图的结果呢？ 即

{ "count" : 379.0, "brand" : "Bosch", "month" : "2017-08" } 
{ "count" : 1561.0, "brand" : "Bosch", "month" : "2017-09" }
{ "count" : 9202.0, "brand" : "Siemens", "month" : "2017-08" }
{ "count" : 2278.0, "brand" : "Siemens", "month" : "2017-09" }
==>
{ "count" : 1561.0, "brand" : "Bosch", "month" : "2017-09","huanbi": 311.87 }
{ "count" : 2278.0, "brand" : "Siemens", "month" : "2017-09","huanbi":-75.24 }

我以为挺好实现的 没想到还挺折腾的 代码如下

        Map<String,Object> record1 = new HashMap(ImmutableMap.of("count", 379, "brand", "Bosch", "month", "2017-08"));
        Map<String,Object> record2 = new HashMap(ImmutableMap.of("count", 1561, "brand", "Bosch", "month", "2017-09"));

        Map<String,Object> record3 = new HashMap(ImmutableMap.of("count", 9202, "brand", "Siemens", "month", "2017-08"));
        Map<String,Object> record4 = new HashMap(ImmutableMap.of("count", 2278, "brand", "Siemens", "month", "2017-09"));

        Map<String,Object> record5 = new HashMap(ImmutableMap.of("count", 2278, "brand", "foo", "month", "2017-09"));

        List<Map<String, Object>> queryResult = Lists.newArrayList(record1, record4, record3, record2, record5);

        // 先按品牌和日期排序
        queryResult.sort((o1,o2)->{
            int result = 0;
            String[] keys = {"brand", "month"};
            for (String key : keys) {
                String val1 = o1.get(key).toString();
                String val2 = o2.get(key).toString();
                result = val1.compareTo(val2);
                if(result != 0){
                    return result;
                }
            }
            return result;
        });

        // 再按品牌分组
        Map<String, List<Map<String, Object>>> brand2ListMap = queryResult.stream().collect(groupingBy(m -> m.get("brand").toString(), toList()));
        /**
         *  每组中第一条肯定是上一月的 找到上月的数目
         *  第二条记录是本月的 找到本月的数据
         *  计算环比 本期记录中添加环比
         *  同时删除上一条记录
          */

        for (String key : brand2ListMap.keySet()) {
            List<Map<String, Object>> recordList = brand2ListMap.get(key);
            if (recordList.size() > 1) {
                Map<String, Object> prevRecord = recordList.get(0);
                Map<String, Object> currentRecord = recordList.get(1);
                Integer prevCount = (Integer) prevRecord.get("count");
                Integer currentCount = (Integer) currentRecord.get("count");

                BigDecimal huanbi = BigDecimal.valueOf((currentCount - prevCount) * 100).divide(BigDecimal.valueOf(prevCount), 2, ROUND_HALF_DOWN);
                currentRecord.put("huanbi", huanbi);

                recordList.remove(0);
            }else{
                // 不存在上期记录 环比默认为0
                recordList.get(0).put("huanbi", 0);
            }
        }

        // 生成一个新的List 只包含本期记录
        List<Map<String, Object>> processedResult = new ArrayList(brand2ListMap.values().stream().flatMap(list->list.stream()).collect(toList()));
        // 按照品牌排序
        processedResult.sort(Comparator.comparing(o -> o.get("brand").toString()));
        processedResult.forEach(System.out::println);

输出结果如下

{count=1561, month=2017-09, brand=Bosch, huanbi=311.87}
{count=2278, month=2017-09, brand=Siemens, huanbi=-75.24}
{count=2278, month=2017-09, brand=foo, huanbi=0}

应该不是我想的复杂了吧？应该没有更简单的方案了吧？

Answer 1

刚刚看到你的私信。如果用MongoDB解决的话方法如下： // 测试数据 db.test.insert([ { "count" : 379.0, "brand" : "Bosch", "month" : "2017-08" }, { "count" : 1561.0, "brand" : "Bosch", "month" : "2017-09" }, { "count" : 9202.0, "brand" : "Siemens", "month" : "2017-08" }, { "count" : 2278.0, "brand" : "Siemens", "month" : "2017-09" } ]); // 运算方法 db.test.aggregate([ {$match: {month: {$in: ["2017-08", "2017-09"]}}}, {$sort: {month: 1}}, {$group: {_id: "$brand", lastMonth: {$first: "$count"}, thisMonth: {$last: "$count"}, month: {$last: "$month"}}}, {$project: {brand: 1, ratio: {$divide: [{$subtract: ["$thisMonth", "$lastMonth"]}, "$lastMonth"]}}} ]) // 结果 { "_id" : "Siemens", "ratio" : -0.7524451206259509 } { "_id" : "Bosch", "ratio" : 3.1187335092348283 } 为了最好的效果，需要添加一些索引以优化查询： db.test.createIndex({month: 1}); 另外建议你日期都用Date，不要用字符串，这是个良好的习惯。就算现在用起来没什么区别，早晚也是会需要它是个日期的。

Answer 2

这个很适合java8的stream。