H5W3
当前位置:H5W3 > 其他技术问题 > 正文

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

需求
想要统计环比指标((本期数量-上期数量)/上期数量*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}

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

回答:

@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 -> {
        //获取上月份的数量
        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}

如果需要排序,可以在最后的结果做一层排序。

回答:

刚刚看到你的私信。如果用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,不要用字符串,这是个良好的习惯。就算现在用起来没什么区别,早晚也是会需要它是个日期的。

回答:

这个很适合java8的stream。

本文地址:H5W3 » 关于java对查询结果进行环比指标计算的代码示例

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址