关于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。