ElasticSearch 分组条件过滤求和

需求点：以“投递岗位”维度进行分组统计出消耗了金币的金币总量，以及每个岗位的报名人数

例如：目前有岗位A、岗位B、岗位C，岗位A有3个人报名，每个报名消耗1个报名单；岗位B有5个人报名，每个报名消耗2个报名单，且其中有个报名是通过分享岗位进行报名的；岗位C有4个人报名，报名不消耗报名单；注意：当岗位是被分享报名时，无需消耗岗位报名单。

最终结果应该是：

岗位	报名总人数	消耗总报名单数
岗位A	3	3
岗位B	5	8
岗位C	2	0

 

 

 

 

 

ES索引结构：

字段	类型	描述
id	long	记录ID
partJobId	long	岗位ID
partJobTitle	string	岗位标题
expendGoodsCount	integer	消耗报名单数量
isExpendGoods	boolean	是否消耗报名单
userId	long	用户ID
userNamr	string	用户姓名

 

 

 

 

 

 

 

 

 

现在使用ES来实现以上效果：

    public List<PartJobEntryApplyCountModel> countPartJobEntryApply(List<Long> partJobIds) {
        if (CollectionUtils.isEmpty(partJobIds)){
            return new ArrayList<>();
        }
        BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
        queryBuilder.filter(QueryBuilders.termsQuery("partJobId", partJobIds));
     
        // 指定字段进行聚合统计求和
        SumAggregationBuilder sumExpendGoodsCountBuilder = AggregationBuilders.sum("sumExpendGoodsCount").field("expendGoodsCount");
        // 过滤出已消耗报名单的记录
        BoolQueryBuilder expendGoodsQueryBuilder = QueryBuilders.boolQuery();
        expendGoodsQueryBuilder.must(QueryBuilders.termQuery("expendGoods", true));
        // 将过滤条件、求和语句添加到分组统计的过滤器中
        FilterAggregationBuilder addExpendGoodsQueryBuilder = AggregationBuilders.filter("add_expendGoods", expendGoodsQueryBuilder)
                .subAggregation(sumExpendGoodsCountBuilder);
        // 以岗位维度进行分组统计
        TermsAggregationBuilder partJobAggBuilder = AggregationBuilders.terms("partJobIdAgg").field("partJobId")
                .subAggregation(addExpendGoodsQueryBuilder);
        partJobAggBuilder.size(100);
        partJobAggBuilder.order( BucketOrder.key(false));
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        searchSourceBuilder.query(queryBuilder).aggregation(partJobAggBuilder);

        //设置分页
        searchSourceBuilder.from(1);
        searchSourceBuilder.size(0);
        searchSourceBuilder.timeout(new TimeValue(10, TimeUnit.SECONDS));
        //设置index, type
        SearchRequest searchRequest = new SearchRequest(INDEX_RESUME_RECORD);
        searchRequest.types("resume_record");
        searchRequest.source(searchSourceBuilder);
        try{
            if (logElasticsearch) {
                log.info("countPartJobEntryApply query dsl:" + searchSourceBuilder.toString());
            }
            SearchResponse sr = restHighLevelClient.search(searchRequest);
            Map<String, Aggregation> aggMap = sr.getAggregations().getAsMap();
            ParsedLongTerms partJobIdAgg = (ParsedLongTerms)aggMap.get("partJobIdAgg");
            List<PartJobEntryApplyCountModel> models = new ArrayList<>();
            Iterator<? extends Terms.Bucket> iterator = partJobIdAgg.getBuckets().iterator();
            PartJobEntryApplyCountModel initModel = new PartJobEntryApplyCountModel();
            ParsedSum sumTerms;
            ParsedSingleBucketAggregation sumNext;
            while (iterator.hasNext()) {
                Terms.Bucket next = iterator.next();
                PartJobEntryApplyCountModel model = initModel.clone();
                model.setPartJobId(next.getKeyAsNumber().longValue());
                model.setEntryCount(next.getDocCount());
                sumNext = (ParsedSingleBucketAggregation)next.getAggregations().getAsMap().get("add_expendGoods");
                sumTerms = (ParsedSum)sumNext.getAggregations().getAsMap().get("sumExpendGoodsCount");
                if (sumTerms != null){
                    model.setExpendGoodsCount(Double.valueOf(sumTerms.getValue()).longValue());
                }
                models.add(model);
            }
            return models;
        } catch (Exception e){
            log.error("countPartJobEntryApply ES查询失败", e);
        }
        return new ArrayList<>();
    }

PartJobEntryApplyCountModel字段：

@Data
public class PartJobEntryApplyCountVO{
    /**
     * 兼职ID
     */
    private Long partJobId;

    /**
     * 总报名人数
     */
    private Integer entryCount;

    /**
     * 消耗报名单数量
     */
    private Integer expendGoodsCount;


}

countPartJobEntryApply query dsl：

{
    "from":1,
    "size":0,
    "timeout":"10s",
    "query":{
        "bool":{
            "filter":[
                {
                    "terms":{
                        "partJobId":[
                            58,
                            59
                        ],
                        "boost":1
                    }
                }
            ],
            "adjust_pure_negative":true,
            "boost":1
        }
    },
    "aggregations":{
        "partJobIdAgg":{
            "terms":{
                "field":"partJobId",
                "size":100,
                "min_doc_count":1,
                "shard_min_doc_count":0,
                "show_term_doc_count_error":false,
                "order":{
                    "_key":"desc"
                }
            },
            "aggregations":{
                "add_expendGoods":{
                    "filter":{
                        "bool":{
                            "must":[
                                {
                                    "term":{
                                        "expendGoods":{
                                            "value":true,
                                            "boost":1
                                        }
                                    }
                                }
                            ],
                            "adjust_pure_negative":true,
                            "boost":1
                        }
                    },
                    "aggregations":{
                        "sumExpendGoodsCount":{
                            "sum":{
                                "field":"expendGoodsCount"
                            }
                        }
                    }
                }
            }
        }
    }
}

ES查询结果：

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 30,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "partJobIdAgg": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key":58,
          "doc_count": 16,
          "add_expendGoods": {
            "doc_count": 15,
            "sumExpendGoodsCount": {
              "value": 15
            }
          }
        },
        {
          "key":59,
          "doc_count": 14,
          "add_expendGoods": {
            "doc_count": 14,
            "sumExpendGoodsCount": {
              "value": 0
            }
          }
        }
      ]
    }
  }
}