spring-boot MongoDB按时间排序统计数据

spring-boot MongoDB按时间排序统计数据

需求：spring-boot使用MongoDB统计每天订单的订单营业额。

本文章不对MongoDB配置使用进行讲解

MongoDB里面的订单数据格式

{
    "_id" : ObjectId("5dcb84d8f5df2c3f489e380d"),
    "userName" : "2018013022337",
    "realName" : "谭**",
    "putTime" : ISODate("2019-05-26T17:58:18.000+08:00"),
    "amount" : 482,
    "type" : "化妆品",
    "productName" : "口红",
    "_class" : "****"
}

相关service方法

import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.TypedAggregation;
import org.springframework.data.mongodb.core.query.Criteria;

 /**
     *
     * @param type 商品类型
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param timeType year:按每年、month按每月、day按每天、hour按每小时

     * @return
     */
    public Object selectStatistic(String type, Date startTime,Date endTime,String timeType) {
        Criteria criteria = new Criteria();
        if (StringUtil.isNotEmpty(type)) { //这可以只针对某商品类型进行统计
            criteria.and("type").is(type);
        }

        if (startTime != null && endTime != null) { //筛选出时间
            criteria.andOperator(Criteria.where("putTime").gte(startTime),
                    Criteria.where("putTime").lte(endTime));
        }


        String dateFormat = "{$dateToString:{ format:'%Y-%m-%d',date:{$add:{'$putTime',8*60*60000}}}}";  //时间统计时把时间调成北京时区 8*60*60000
        if (StringUtil.isNotEmpty(timeType)) {
            if (timeType.equals("year")) {
                dateFormat = "{$dateToString:{ format:'%Y',date:{$add:{'$putTime',8*60*60000}}}}";//不同的时间统计类型使用不同的时间统计格式
            }
            if (timeType.equals("month")) {
                dateFormat = "{$dateToString:{ format:'%Y-%m',date:{$add:{'$putTime',8*60*60000}}}}";
            }
            if (timeType.equals("day")) {
                dateFormat = "{$dateToString:{ format:'%Y-%m-%d',date:{$add:{'$putTime',8*60*60000}}}}";
            }
            if (timeType.equals("hour")) {
                dateFormat = "{$dateToString:{ format:'%Y-%m-%d %H',date:{$add:{'$putTime',8*60*60000}}}}";
            }
        }

        TypedAggregation<DisposalRecord> agg = Aggregation.newAggregation(DisposalRecord.class,
                Aggregation.match(criteria),//条件筛选    
                Aggregation.project("amount").andExpression(dateFormat).as("day"),//根据上面的时间格式分组
                Aggregation.group("day").count().as("count")
                        .sum("amount").as("sumAmount"),//根据时间分组后统计amount
                Aggregation.sort(new Sort(Sort.Direction.DESC, "_id"))//倒叙排序
        );

        AggregationResults<DisposalRecord> result = mongoTemplate.aggregate(agg, DisposalRecord.class); //进行统计
//        System.out.println(JSONUtil.toJsonPrettyStr(result));

        List<DisposalTimeStatistics> list = new ArrayList<>();
        JSONArray jsonArray = JSONUtil.parseArray(result.getRawResults().get("results"));//拿到结果后分析获取所需数据
        for (Object o : jsonArray) {
            JSONObject json = JSONUtil.parseObj(JSONUtil.toJsonStr(o));
            DisposalTimeStatistics disposalTimeStatistics = new DisposalTimeStatistics();
            disposalTimeStatistics.setSumAmount(json.getLong("amount"));
            disposalTimeStatistics.setCount(json.getLong("count"));
            disposalTimeStatistics.setDate(json.getStr("_id"));
            disposalTimeStatistics.setHour(0);
            if (timeType.equals("hour")) {
                disposalTimeStatistics.setHour(Integer.parseInt(disposalTimeStatistics.getDate().substring(disposalTimeStatistics.getDate().length()-2,disposalTimeStatistics.getDate().length())));
                disposalTimeStatistics.setDate(disposalTimeStatistics.getDate().substring(0,disposalTimeStatistics.getDate().length()-2));

            }
            list.add(disposalTimeStatistics);
//            System.out.println(JSONUtil.toJsonPrettyStr(disposalTimeStatistics));
        }
        if (StringUtil.isNotEmpty(timeType)) {

            if (timeType.equals("day")) {
                list = getTradeStatisticsListDays(list,startTime,endTime); //如果某天没数据时是跳过的，所有要加上数据令时间数据连续
            }
            if (timeType.equals("hour")) {
                list = getTradeStatisticsListHour(list,startTime,endTime);
            }
        }
        return list;
    }


    /**
     * 按小时拿数据
     *
     * @param tradeStatisticsList
     * @param startTime
     * @param endTime
     * @return
     */
    private List<DisposalTimeStatistics> getTradeStatisticsListHour(List<DisposalTimeStatistics> tradeStatisticsList, Date startTime, Date endTime) {
        List<DisposalTimeStatistics> list = new ArrayList<>();
        Date now = startTime;
        while (now.before(endTime)) {
            list.add(getTradeStatistics(tradeStatisticsList, DateUtil.formatDate(now), now.getHours()));
            now = DateUtil.offsetHour(now, 1);
        }
        return list;
    }

    /**
     * 按天数拿数据
     *
     * @param tradeStatisticsList
     * @param startTime
     * @param endTime
     * @return
     */
    private List<DisposalTimeStatistics> getTradeStatisticsListDays(List<DisposalTimeStatistics> tradeStatisticsList, Date startTime, Date endTime) {
        List<DisposalTimeStatistics> list = new ArrayList<>();
        Date now = startTime;
        while (now.before(endTime)) {
            list.add(getTradeStatistics(tradeStatisticsList, DateUtil.formatDate(now), 0));
            now = DateUtil.offsetDay(now, 1);
        }
        return list;
    }

    /**
     * 获取相关数据项
     *
     * @param tradeStatisticsList
     * @param date
     * @param hour
     * @return
     */
    private DisposalTimeStatistics getTradeStatistics(List<DisposalTimeStatistics> tradeStatisticsList, String date, Integer hour) {
        for (DisposalTimeStatistics tradeStatistics : tradeStatisticsList) {
            if (tradeStatistics.getDate().contains(date) && hour == tradeStatistics.getHour()) {
                return tradeStatistics;
            }
        }
        DisposalTimeStatistics tradeStatistics = new DisposalTimeStatistics();
        tradeStatistics.setDate(date);
        tradeStatistics.setHour(hour);
        tradeStatistics.setCount(0L);
        tradeStatistics.setSumAmount(0L);
        return tradeStatistics;
    }