我用的es是 6.8.1
如果我们想在es实现sql 中的group by 进行 日 周月年的统计
SELECT COUNT(id) FROM patientstudy GROUP BY createTime;
先介绍下 es DateHistogramInterval
public static final DateHistogramInterval SECOND = new DateHistogramInterval("1s");
public static final DateHistogramInterval MINUTE = new DateHistogramInterval("1m");
public static final DateHistogramInterval HOUR = new DateHistogramInterval("1h");
public static final DateHistogramInterval DAY = new DateHistogramInterval("1d");
public static final DateHistogramInterval WEEK = new DateHistogramInterval("1w");
public static final DateHistogramInterval MONTH = new DateHistogramInterval("1M");
public static final DateHistogramInterval QUARTER = new DateHistogramInterval("1q");
public static final DateHistogramInterval YEAR = new DateHistogramInterval("1y");
在kibana中执行命令
POST /patientstudy/_search?size=5
{
"query": {
"bool": {
"must": [{
"range": {
"createTime": {
"gte": "2021-01-06",
"lte": "2021-04-08"
}
}
}]
}
},
"aggs": {
"studyCount": {
"date_histogram": {
"field": "createTime",
"interval": "1h",
"time_zone": "+08:00",
"format":"yyyy/MM/dd HH:mm"
}
}
}
}
看结果
java代码
public Result<List> studyTimeList() {
List list=new ArrayList();
RangeQueryBuilder rangequerybuilder = QueryBuilders
.rangeQuery("createTime")
.from("2021-04-06").to("2021-04-06");
QueryBuilder builder = null;
builder=QueryBuilders.boolQuery().must(QueryBuilders.termsQuery("hospitalId", "2265")).must(rangequerybuilder);
DateHistogramAggregationBuilder tb1 = AggregationBuilders.dateHistogram("study_count").minDocCount(0).dateHistogramInterval(DateHistogramInterval.MONTH).format("yyyy-MM-dd HH:mm:ss").field("createTime");
ValueCountAggregationBuilder sb = AggregationBuilders.count("count_id").field("hospitalId");
tb1.subAggregation(sb);
//patientstudy 是索引名称 _doc 是索引类型 一般在es 索引实体里配置的 @Document(indexName = "patientstudy", type = "_doc")
SearchQuery searchQuery = new NativeSearchQueryBuilder().withIndices("patientstudy").withTypes("_doc").withQuery(builder).addAggregation(tb1).build();
// 执行语句获取聚合结果
Aggregations aggregations = elasticsearchTemplate.query(searchQuery, new ResultsExtractor<Aggregations>() {
@Override
public Aggregations extract(SearchResponse response) {
return response.getAggregations();
}
});
// 获取聚合结果
Histogram term = aggregations.get("study_count");// 获取结果后进行解析
if (term.getBuckets().size() > 0) {
for ( Histogram.Bucket bk : term.getBuckets()) {
Map<String,Object> map = new HashMap<>();
map.put("study_count",bk.getDocCount());
map.put("time",bk.getKeyAsString());
log.info("一次循环结束--------------------------");
list.add(map);
}
}
return ResultUtil.data(ResultCode.RESULT_SUCCESS.getCode(), "",list);
}
注意:createTime 必须是date 类型