一、ES的demo数据如下:
二、简单聚合,获得平均分
GET /student/student/_search
{
"aggs": {
"avg_score": {
"avg": {
"field": "score"
}
}
}
}
查询结果:
java实现:
/** * 聚合查询avg * *GET /student/student/_search * { * "aggs": { * "avg_score": { * "avg": { * "field": "score" * } * } * } * } * */ @ResponseBody @RequestMapping("/searchAvg") public Double searchAvg(){ AvgAggregationBuilder scoreQuery = AggregationBuilders.avg("avg_score"); scoreQuery.field("score"); System.out.println(scoreQuery); SearchQuery searchQuery = new NativeSearchQueryBuilder().withIndices("student").withTypes("student").addAggregation(scoreQuery).build(); Aggregations aggregations = elasticsearchTemplate.query(searchQuery, new ResultsExtractor<Aggregations>() { @Override public Aggregations extract(SearchResponse response) { return response.getAggregations(); } }); Aggregation avg_score = aggregations.asMap().get("avg_score"); System.out.println(avg_score.toString()); ObjectMapper objectMapper = new ObjectMapper(); Double avgScore=null; try { JsonNode jsonNode = objectMapper.readTree(avg_score.toString()); avgScore=Double.parseDouble(jsonNode.get("avg_score").get("value").toString()); } catch (IOException e) { e.printStackTrace(); } return avgScore; }
其他如sum,max,min如上类似
二、根据名字聚合获得各自的所有成绩的平均分
GET /student/student/_search
{
"aggs": {
"_result": {
"terms": {
"field": "name",
"size": 10,
"order": {
"avg_score": "desc"
}
},
"aggs": {
"avg_score": {
"avg": {
"field": "score"
}
}
}
}
}
}
查询结果:
java实现:
/** * 聚合查询 select avg(score) group by name * */ @ResponseBody @RequestMapping("/searchGroupBy") public Double searchGroupBy(){ TermsAggregationBuilder resultQuery = AggregationBuilders.terms("_result"); resultQuery.field("name");//根据名字聚合 resultQuery.size(10);//结果显示10条 resultQuery.order(BucketOrder.aggregation("avg_score",false));//按照avg_score分数降序排 AvgAggregationBuilder scoreQuery = AggregationBuilders.avg("avg_score"); scoreQuery.field("score");//聚合查询平均分 SearchQuery searchQuery = new NativeSearchQueryBuilder().withIndices("student").withTypes("student").addAggregation(resultQuery.subAggregation(scoreQuery)).build(); Aggregations aggregations = elasticsearchTemplate.query(searchQuery, new ResultsExtractor<Aggregations>() { @Override public Aggregations extract(SearchResponse response) { return response.getAggregations(); } }); System.out.println(aggregations); Aggregation avg_score = aggregations.asMap().get("_result"); System.out.println(avg_score.toString());//已经有查询数据返回 return null; }