ElasticSearch中 如何使用“group by“ 进行日 周 月 年统计呢？（ElasticsearchTemplate进行聚合查询）

我用的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 类型