mongodb aggregate 實踐

這段時間公司的專案業務改動，原來用的資料庫是mysql，最近打算把部分業務相關的資料存在mongodb，我需要做的工作，第一點是資料遷移，在這裡就不說了，寫段小程式就遷移過去了，然後還要把這塊業務的資料訪問層，重寫一版mongodb的實現。

下面分別是mysql和mongodb不同實現的查詢，統計某一天內，賬單型別為1010（業務含義就不說了），累積賬單金額大於10000的使用者

mysql：

SELECT pl.user_id, IFNULL(round(sum(pl.amount), 2), 0) sum_amount
FROM ys_profit_log pl
WHERE pl.profit_type = 1010
AND pl.is_profit = 0
AND DATE_FORMAT(pl.create_time, '%Y-%m-%d') = DATE_SUB('2017-02-05', interval 1 DAY)
GROUP BY pl.user_id
HAVING sum_amount >= 10000;

mongodb：

db.ys_profit_log.aggregate([
    {$match: {
        profit_type: 1010,
        is_profit: false,
        create_time: {$gte: new Date('2017-02-04'), $lt: new Date('2017-02-05')}
    }},
    {$group: {_id: '$user_id', sum_amount: {$sum: '$amount'}}},
    {$sort : {_id: 1}},
    {$match: {sum_amount: {$gte: 10000}}}
]);

需要說明的一點，mongodb分組查詢後，不會像mysql一樣預設按分組的列進行升序排序，為了保證和之前mysql的實現一樣，這裡用$sort在分組後又進行了排序

Spring data mongodb 實現程式碼片段

package cn.yskj.timer.dao.impl;

import cn.yskj.common.constant.Constant;
import cn.yskj.common.constant.ProfitLogEnum;
import com.mongodb.BasicDBObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.query.Criteria;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 功能描述: 賬單
 *
 * 作者: Yzw
 * 日期: 2017-04-06 10:27:28
 */
public class ProfitLogDaoImpl {

    private static final Logger log = LoggerFactory.getLogger(ProfitLogDaoImpl.class);

    @Autowired
    private MongoTemplate mongoTemplate;

    public List<Long> getXLGBUserIds(Map<String, Date> dateLimit) {
        List<Long> userIdList = new ArrayList<>();

        Date beginDate = dateLimit.get("begin");
        Date endDate = dateLimit.get("end");

        Criteria where = new Criteria();
        where.and("profit_type").is(ProfitLogEnum.PROFIT_1010.getProfitType());
        where.and("is_profit").is(false);
        where.and("create_time").gte(beginDate).lt(endDate);

        Criteria having = new Criteria();
        having.and("sum_amount").gte(Constant.LGB_REQUIRE_NUM);

        Aggregation aggr = Aggregation.newAggregation(
                Aggregation.match(where),
                Aggregation.group("user_id").sum("amount").as("sum_amount"),
                Aggregation.sort(new Sort("_id")),
                Aggregation.match(having)
        );

        AggregationResults<BasicDBObject> aggrResults = mongoTemplate.aggregate(aggr, "ys_profit_log", BasicDBObject.class);
        List<BasicDBObject> mappedResults =  aggrResults.getMappedResults();
        if (mappedResults != null && mappedResults.size() > 0) {
            log.info("查詢結果 => 使用者數量: {}", mappedResults.size());
            mappedResults.forEach(item -> {
                userIdList.add(item.getLong("_id"));
            });
        }

        return userIdList;
    }

}