關(guān)于java對查詢結(jié)果進行環(huán)比指標計算的代碼示例

需求
想要統(tǒng)計環(huán)比指標（（本期數(shù)量-上期數(shù)量）/上期數(shù)量*100%）假設(shè)下面是統(tǒng)計9月份的數(shù)據(jù) 如下所示

品牌	數(shù)量	環(huán)比
Bosh	1561	311.87%
Siemens	2278	-75.24%

問題
查詢的時候需要同時查詢8月的數(shù)據(jù) 統(tǒng)計出8月的數(shù)量然后才能進行環(huán)比指標的計算

{ "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" }

怎么轉(zhuǎn)換得到上圖的結(jié)果呢？即

{ "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 }

我以為挺好實現(xiàn)的沒想到還挺折騰的代碼如下

        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()));
        /**
         *  每組中第一條肯定是上一月的 找到上月的數(shù)目
         *  第二條記錄是本月的 找到本月的數(shù)據(jù)
         *  計算環(huán)比 本期記錄中添加環(huán)比
         *  同時刪除上一條記錄
          */

        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{
                // 不存在上期記錄 環(huán)比默認為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);

輸出結(jié)果如下

{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}

應(yīng)該不是我想的復雜了吧？應(yīng)該沒有更簡單的方案了吧？

回答

編輯回答

伴謊

剛剛看到你的私信。如果用MongoDB解決的話方法如下：

// 測試數(shù)據(jù)
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"]}}}
])
// 結(jié)果
{ "_id" : "Siemens", "ratio" : -0.7524451206259509 }
{ "_id" : "Bosch", "ratio" : 3.1187335092348283 }

為了最好的效果，需要添加一些索引以優(yōu)化查詢：

db.test.createIndex({month: 1});

另外建議你日期都用Date，不要用字符串，這是個良好的習慣。就算現(xiàn)在用起來沒什么區(qū)別，早晚也是會需要它是個日期的。

2018年1月7日 23:57

編輯回答

別瞎鬧

這個很適合java8的stream。

2017年1月21日 23:37

編輯回答

敢試

@Test
public void test02() {

    List<DemoEntity> list = Arrays.asList(
            new DemoEntity(379, "Bosch", "2017-08"),
            new DemoEntity(1561, "Bosch", "2017-09"),
            new DemoEntity(9202, "Siemens", "2017-08"),
            new DemoEntity(2278, "Siemens", "2017-09")
    );

    //按brand和month分組 key自己定義
    Map<String, Integer> map = list.stream()
            .collect(Collectors.toMap(o -> o.getBrand() + "||" + o.getMonth(), DemoEntity::getCount));

    list.forEach(entity -> {
        //獲取上月份的數(shù)量
        Integer count = map.get(entity.getBrand() + "||" + getPreMonth(entity.getMonth()));
        Optional.ofNullable(count)
                .map(o -> BigDecimal.valueOf(entity.getCount() - count)
                        .multiply(BigDecimal.valueOf(100))
                        .divide(BigDecimal.valueOf(count), 2, BigDecimal.ROUND_HALF_UP)
                        .doubleValue())
                .ifPresent(entity::setPercent);
    });

    //篩選打印某一月份
    list.stream()
            .filter(entity -> Objects.equals(entity.getMonth(), "2017-09"))
            .map(JSON::toJSONString)
            .forEach(System.out::println);
}

/**
 * 獲取指定月份的上一月日期
 */
private String getPreMonth(String month) {
    try {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        return sdf.format(DateUtils.addMonths(sdf.parse(month), -1));
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

控制臺輸出：
{"brand":"Bosch","count":1561,"month":"2017-09","percent":311.87}
{"brand":"Siemens","count":2278,"month":"2017-09","percent":-75.24}

如果需要排序，可以在最后的結(jié)果做一層排序。

2018年3月27日 21:05