我用的es是 6.8.1
如果我们想在es实现sql 中的group by的查询统计效果
SELECT COUNT(id) FROM patientstudy GROUP BY hospitalId;
在kibana中执行命令
POST /patientstudy/_search?size=0
{
"aggs" : {
"count" : { "terms" : { "field" : "hospitalId" } }
}
}
看结果
用java怎么实现呢
public Result<List> studyCountList() {
List list=new ArrayList();
QueryBuilder builder = null;
builder=QueryBuilders.boolQuery().must(QueryBuilders.termsQuery("hospitalId", "2265"));//这里是测试查询机构id为2265的
//hospitalId 是分组字段名,hospital是查询结果的别名
TermsAggregationBuilder tb1 = AggregationBuilders.terms("hospital").field("hospitalId").
//分组后以Count聚合函数名字排序 false 倒叙 true 正序
order(BucketOrder.aggregation("count_id", false));
ValueCountAggregationBuilder sb = AggregationBuilders.count("count_id").field("hospitalId"); //对hospitalId 进行count
tb1.subAggregation(sb);
SearchQuery searchQuery = new NativeSearchQueryBuilder().withIndices("patientstudy").withTypes("_doc").withQuery(builder).addAggregation(tb1).build(); //patientstudy 是索引名称 _doc 是索引类型 一般在es 索引实体里配置的 @Document(indexName = "patientstudy", type = "_doc")
// 执行语句获取聚合结果
Aggregations aggregations = elasticsearchTemplate.query(searchQuery, new ResultsExtractor<Aggregations>() {
@Override
public Aggregations extract(SearchResponse response) {
return response.getAggregations();
}
});
// 获取聚合结果
Terms term = aggregations.get("hospital");// 获取结果后进行解析
if (term.getBuckets().size() > 0) {
for (Bucket bk : term.getBuckets()) {
Map<String,Object> map = new HashMap<>();
int hospitalId = bk.getKeyAsNumber().intValue();
map.put("count",bk.getDocCount());
map.put("hospital",bk.getKeyAsString());
log.info("typeId:{}" + hospitalId);
log.info("一次循环结束--------------------------");
list.add(map);
}
}
return ResultUtil.data(ResultCode.RESULT_SUCCESS.getCode(), "",list);
}
最后看结果
注意:hospitalId必须是keyword类型