使用mongoTemplate进行Aggregation聚合查询

Aggregation聚合查询

金山竹影几千秋,云索高飞水自流,万里长江飘玉带,一轮银月滚金球,远自湖北三千里,近到江南十六州,美景一时观不透,天缘有分画中游!

需求:在订单表中,根据buyerNick分组,统计每个buyerNick的电话、地址、支付总金额以及总商品数,返回结果是CustomerDetail。

/*
 * project:列出所有本次查询的字段,包括查询条件的字段和需要搜索的字段;
 * match:搜索条件criteria
 * unwind:某一个字段是集合,将该字段分解成数组
 * group:分组的字段,以及聚合相关查询
 *      sum:求和(同sql查询)
 *      count:数量(同sql查询)
 *      as:别名(同sql查询)
 *      addToSet:将符合的字段值添加到一个集合或数组中
 * sort:排序
 * skip&limit:分页查询
 */
public List<CustomerDetail> customerDetailList(Integer pageNum,String userId,String buyerNick,String itemId,List<String> phones) throws Exception{
    Criteria criteria = Criteria.where("userId").is(userId);
    Integer pageSize = 10;
    Integer startRows = (pageNum - 1) * pageSize;
    if(buyerNick != null && !"".equals(buyerNick)){
        criteria.and("buyerNick").is(buyerNick);
    }
    if(phones != null && phones.size() > 0){
        criteria.and("mobile").in(phoneList);
    }
    if(itemId != null && !"".equals(itemId)){
        criteria.and("orders.numIid").is(itemId);
    }
    Aggregation customerAgg = Aggregation.newAggregation(
            Aggregation.project("buyerNick","payment","num","tid","userId","address","mobile","orders"),
            Aggregation.match(criteria),
               Aggregation.unwind("orders"),
            Aggregation.group("buyerNick").first("buyerNick").as("buyerNick").first("mobile").as("mobile").
            first("address").as("address").sum("payment").as("totalPayment").sum("num").as("itemNum").count().as("orderNum"),
            Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.DESC, "totalPayment"))),
            Aggregation.skip(startRows),
            Aggregation.limit(pageSize)
            );
    List<CustomerDetail> customerList = tradeRepository.findAggregateList(new Query(criteria), userId, customerAgg,CustomerDetail.class);
    return customerList;
}
public <T> List<T> findAggregateList(Query query,String userNickName, Aggregation aggregation,Class<T> clazz) {
    AggregationResults<T> aggregate = this.mongoTemplate.aggregate(aggregation, collectionName, clazz);
    List<T> customerDetails = aggregate.getMappedResults();
    return customerDetails;
}

Trade表:

public class TradeInfo{
    private String tid;//订单id
    private Double payment;//支付金额
    private String buyerNick;//买家昵称
    private String address;//地址
    private String mobile;//手机号
    private Long num;//购买商品数量
    private List<order> orders;子订单
}

CustomerDetail:

public class CustomerDetail{
    private String buyerNick;//买家昵称
    private Double totalPayment;//订单金额
    private Integer orderNum;//订单数
    private Integer itemNum;//商品数
    private String address;//地址
}

聚合分组,先过滤字段为type=Constants.DataSetOpt.OPT_VIEW,通过dateSetId进行分组,并计算每组的个数,.first是需要显示的字段。

    //浏览分组
    private List<Document> flowGroup() {
        Criteria criteria = Criteria.where("type").is(Constants.DataSetOpt.OPT_VIEW);
        Aggregation agg = Aggregation.newAggregation(
                Aggregation.match(criteria),
                Aggregation.group("dataSetId")
                        .first("dataSetId").as("dataSetId")
                        .count()
                        .as("dataSetIdCount")
                        .first("dataSetType").as("dataSetType")

        );
        AggregationResults<Document> result = mongoTemplate.aggregate(agg,Access.class ,Document.class);
        return result.getMappedResults();
    }

也可以使用TypedAggregation的方式进行聚合分组,如下:

    @Test
    public void statTest(){
        TypedAggregation<Statistics> agg = Aggregation.newAggregation(Statistics.class,
                Aggregation.group("month")
                        .sum("totalVisit")
                        .as("sumTotalVisit")
                        .sum("totalDownload")
                        .as("sumTotalDownload"));
        AggregationResults<Document> result = mongoTemplate.aggregate(agg, Document.class);

//        return result.getMappedResults();
        result.getMappedResults().forEach(document -> System.out.println(document));
    }

addToSet使用方法示例

addToSet:将符合的字段值添加到一个集合或数组中\

聚合示例

    private List<Document> groupCountryOfOrigin() {
        Aggregation agg = Aggregation.newAggregation(
            Aggregation.group("countryOfOrigin")
                .first("countryOfOrigin").as("countryOfOrigin")
                .addToSet("portEntry").as("portEntryList")
                .addToSet("province").as("provinceList")
                .addToSet("customName").as("customNameList")
        );
        AggregationResults<Document> result = mongoTemplate.aggregate(agg,CertificationBatchInfo.class , Document.class);
        return result.getMappedResults();
    }

聚合内如获取

        List<Object> list = Lists.newArrayList();
        List<Document> documents = groupCountryOfOrigin();
        for (Document document : documents) {

            Map<String, Object> map = Maps.newHashMap();

            String countryOfOrigin = document.getString("countryOfOrigin");
            //这个进口国流向过哪些口岸
            List<String> portEntryList = document.getList("portEntryList", String.class);
            //这个进口国流向过哪些省市
            List<String> provinceList = document.getList("provinceList", String.class);
            //这个进口国流向国内哪些种商品
            List<String> customNameList = document.getList("customNameList", String.class);

            map.put("countryOfOrigin",countryOfOrigin);
            map.put("portEntryList",portEntryList);
            map.put("provinceList",provinceList);
            map.put("customNameList",customNameList);
            list.add(map);
        }

推荐阅读更多精彩内容