Elasticsearch sum count group by 按时间区间统计金额与数量

select count(id) totalCount, sum(orderAmount) totalAmount, to_chat(createTime, 'yyyyMMdd') day
group by to_chat(createTime, 'yyyyMMdd')

Java 查询

DateHistogramAggregationBuilder dateHistAgg = AggregationBuilders.dateHistogram("day").field("createTime").dateHistogramInterval(DateHistogramInterval.DAY);

ValueCountAggregationBuilder countAgg = AggregationBuilders.count("totalCount").field("id");
SumAggregationBuilder sumAgg = AggregationBuilders.sum("totalAmount").field("orderAmount");

# 添加 count 子聚合
dateHistAgg.subAggregation(countAgg);
# 添加 sum 子聚合
dateHistAgg.subAggregation(sumAgg);

SeqrchQuery searchQuery = new NativeSearchQueryBuilder().withQuery(queryBuilder).addAggregation(dateHistAgg).build();

List<InternalDateHistogram> result = elasticsearchTemplate.query(searchQuery, new ResultExtractor() {

    public Object extract(SearchResponse response) {
        return response.getAggregations().asList();
    }
});

if(null == result || result.isEmpty()) {
    return new ArrayList<>();
}

InternalDateHistogram dateHistogram = result.get(0);
List<Bucket> bucketList = dateHistogram.getBuckets();

bucketList.forEach(action -> {
    Aggregations aggregation = action.getAggregations();

    org.joda.time.DateTime date = (org.joda.time.DateTime)action.getKey();

    InternalSum totalAmount = aggregation.get("totalAmount");
    InternalValueCount totalCount = aggregation.get("totalCount");
});