Elasticsearch 7.X 聚合查询 及 ElasticsearchRestTemplate 操作

一、创建测试索引

创建索引结构,向 ES 服务发送 PUT 请求:

http://127.0.0.1:9200/jh_test
{
	"settings": {},
	"mappings": {
		"properties": {
			"name": {
				"type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
			},
			"sex": {
				"type": "keyword"
			},
			"buyCount": {
				"type": "long"
			},
            "createMonth":{
                "type":"keyword"
            }
		}
	}
}

其中字段的含义为:
name:姓名、buyCount:购买数量,sex:性别,createMonth:创建月

添加测试数据:

{"name":"张三","buyCount":5,"sex":"男","createMonth":"2021-01"}
{"name":"李四","buyCount":5,"sex":"男","createMonth":"2021-01"}
{"name":"小卷","buyCount":18,"sex":"女","createMonth":"2021-01"}
{"name":"小明","buyCount":6,"sex":"女","createMonth":"2021-01"}
{"name":"张三","buyCount":3,"sex":"男","createMonth":"2021-02"}
{"name":"王五","buyCount":8,"sex":"男","createMonth":"2021-02"}
{"name":"赵四","buyCount":4,"sex":"男","createMonth":"2021-02"}
{"name":"诸葛亮","buyCount":6,"sex":"男","createMonth":"2021-02"}
{"name":"黄忠","buyCount":9,"sex":"男","createMonth":"2021-03"}
{"name":"李白","buyCount":1,"sex":"男","createMonth":"2021-03"}
{"name":"赵四","buyCount":3,"sex":"男","createMonth":"2021-03"}
{"name":"张三","buyCount":2,"sex":"男","createMonth":"2021-03"}
{"name":"李四","buyCount":6,"sex":"男","createMonth":"2021-04"}
{"name":"王五","buyCount":9,"sex":"男","createMonth":"2021-04"}
{"name":"李四","buyCount":4,"sex":"男","createMonth":"2021-04"}
{"name":"王五","buyCount":2,"sex":"男","createMonth":"2021-04"}

二、聚合查询

聚合查询的用法,向 ES 服务发送 GET 请求:

http://127.0.0.1:9200/jh_test/_search
"aggs" : {
    "<aggregation_name>" : {                                 <!--聚合名称 -->
        "<aggregation_type>" : {                             <!--聚合类型 -->
            <aggregation_body>                               <!--聚合具体字段 -->
        }
        [,"meta" : {  [<meta_data_body>] } ]?                <!--元信息 -->
        [,"aggs" : { [<sub_aggregation>]+ } ]?       <!--子聚合 -->
    }
}

1. 查询 buyCount 的总和:
{
  "size":0,
  "aggs":{
    "buyCountSum":{
      "sum": {
        "field": "buyCount"
      }
    }
  }
}

在这里插入图片描述

2. 查询 2021-02 月 buyCount 的总和:
{
  "size":0,
   "query": {
		"term": { 
            "createMonth": "2021-02" 
        }
	},
  "aggs":{
    "buyCountSum":{
      "sum": {
        "field": "buyCount"
      }
    }
  }
}

在这里插入图片描述

3. 查询 2021-03 月 buyCount 的最大值:
{
  "size":0,
   "query": {
		"term": { 
            "createMonth": "2021-03" 
        }
	},
  "aggs":{
    "buyCountMax":{
      "max": {
        "field": "buyCount"
      }
    }
  }
}

在这里插入图片描述

4. 查询 2021-03 月 buyCount 的最小值:
{
  "size":0,
   "query": {
		"term": { 
            "createMonth": "2021-03" 
        }
	},
  "aggs":{
    "buyCountMin":{
      "min": {
        "field": "buyCount"
      }
    }
  }
}

在这里插入图片描述

5. 同时查询 2021-03 月 buyCount 的最大值和最小值:
{
  "size":0,
   "query": {
		"term": { 
            "createMonth": "2021-03" 
        }
	},
  "aggs":{
    "buyCountMax":{
      "max": {
        "field": "buyCount"
      }
    },
     "buyCountMin":{
      "min": {
        "field": "buyCount"
      }
    }
  }
}

在这里插入图片描述

6. 查询所有 name 的去重后的数量
{
  "size":0,
  "aggs":{
    "distinctName":{
      "cardinality": {
        "field": "name.keyword"
      }
    }
  }
}

在这里插入图片描述

7. 查询 2021-04 月 name 的去重后的数量
{
  "size":0,
  "query": {
		"term": { 
            "createMonth": "2021-04" 
        }
	},
  "aggs":{
    "distinctName":{
      "cardinality": {
        "field": "name.keyword"
      }
    }
  }
}

在这里插入图片描述

8. 查询 BuyCount 的平均值
{
    "size":"0",
    "aggs":{
        "buyCountAvg":{
            "avg":{
                "field":"buyCount"
            }
        }
    }
}

在这里插入图片描述

9. 一次查询 总数,最大值,最小值,平均值,总和
{
  "size":0,
  "aggs":{
    "statsAll":{
      "stats":{
        "field":"buyCount"
      }
    }
  }
}

在这里插入图片描述

10. 根据 createMonth 分组查询每个月的最大 buyCount
{
    "size":0,
    "aggs": {
    "createMonthGroup": {
      "terms": {
        "field": "createMonth"
      },
      "aggs": {
        "buyCountMax": {
          "max": {
            "field": "buyCount"
          }
        }
      }
    }
  }
}

在这里插入图片描述

11. 查询每 createMonth 下,根据 sex 区分,统计buyCount 的平均值
{
    "size":0,
    "aggs": {
    "createMonthGroup": {
      "terms": {
        "field": "createMonth"
      },
      "aggs": {
        "sexGroup": {
          "terms": {
            "field": "sex"
          },
          "aggs": {
                "buyCountAvg": {
                    "avg": {
                        "field": "buyCount"
                    }
                }
            }
        }
      }
    }
  }
}

在这里插入图片描述

三、ElasticsearchRestTemplate 聚合查询

创建实体类:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Document(indexName = "jh_test", shards = 3, replicas = 1, refreshInterval = "30s")
public class JhTestEntity {
    @Id
    private String id;

    @Field(type = FieldType.Text)
    private String name;

    @Field(type = FieldType.Keyword)
    private String sex;

    @Field(type = FieldType.Long)
    private Long buyCount;

    @Field(type = FieldType.Keyword)
    private String createMonth;
}
1. 查询 buyCount 的总和:
 @Test
 void aggs() {
     SumAggregationBuilder buyCountSum = AggregationBuilders.sum("buyCountSum").field("buyCount");
     Query query = new NativeSearchQueryBuilder()
             .addAggregation(buyCountSum)
             .build();
     SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
     if (search.hasAggregations()) {
         Aggregations aggregations = search.getAggregations();
         if (Objects.nonNull(aggregations)) {
             Sum sum = aggregations.get("buyCountSum");
             log.info("计算 buyCount 总数:{} ", sum.getValue());
         }
     }
 }

在这里插入图片描述

2. 查询 2021-02 月 buyCount 的总和:
@Test
void aggs() {
    QueryBuilder queryBuilder = QueryBuilders.termQuery("createMonth", "2021-02");
    SumAggregationBuilder buyCountSum = AggregationBuilders.sum("buyCountSum").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .withQuery(queryBuilder)
            .addAggregation(buyCountSum)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Sum sum = aggregations.get("buyCountSum");
            log.info("计算 buyCount 总数:{} ", sum.getValue());
        }
    }
}

在这里插入图片描述

3. 查询 2021-03 月 buyCount 的最大值:
@Test
void aggs() {
    QueryBuilder queryBuilder = QueryBuilders.termQuery("createMonth", "2021-03");
    MaxAggregationBuilder buyCountMax = AggregationBuilders.max("buyCountMax").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .withQuery(queryBuilder)
            .addAggregation(buyCountMax)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Max max = aggregations.get("buyCountMax");
            log.info("计算 buyCount 最大值:{} ", max.getValue());
        }
    }
}

在这里插入图片描述

4. 查询 2021-03 月 buyCount 的最小值:
@Test
void aggs() {
    QueryBuilder queryBuilder = QueryBuilders.termQuery("createMonth", "2021-03");
    MinAggregationBuilder buyCountMin = AggregationBuilders.min("buyCountMin").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .withQuery(queryBuilder)
            .addAggregation(buyCountMin)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Min min = aggregations.get("buyCountMin");
            log.info("计算 buyCount 最小值:{} ", min.getValue());
        }
    }
}

在这里插入图片描述

5. 同时查询 2021-03 月 buyCount 的最大值和最小值:
@Test
void aggs() {
    QueryBuilder queryBuilder = QueryBuilders.termQuery("createMonth", "2021-03");
    MaxAggregationBuilder buyCountMax = AggregationBuilders.max("buyCountMax").field("buyCount");
    MinAggregationBuilder buyCountMin = AggregationBuilders.min("buyCountMin").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .withQuery(queryBuilder)
            .addAggregation(buyCountMax)
            .addAggregation(buyCountMin)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Max max = aggregations.get("buyCountMax");
            log.info("计算 buyCount 最大值:{} ", max.getValue());
            Min min = aggregations.get("buyCountMin");
            log.info("计算 buyCount 最小值:{} ", min.getValue());
        }
    }
}

在这里插入图片描述

6. 查询所有 name 的去重后的数量
@Test
void aggs() {
    CardinalityAggregationBuilder distinctName = AggregationBuilders.cardinality("distinctName").field("name.keyword");
    Query query = new NativeSearchQueryBuilder()
            .addAggregation(distinctName)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Cardinality cardinality = aggregations.get("distinctName");
            log.info("计算 name 的去重后的数量:{} ", cardinality.getValue());
        }
    }
}

在这里插入图片描述

7. 查询 2021-04 月 name 的去重后的数量
@Test
void aggs() {
    QueryBuilder queryBuilder = QueryBuilders.termQuery("createMonth", "2021-04");
    CardinalityAggregationBuilder distinctName = AggregationBuilders.cardinality("distinctName").field("name.keyword");
    Query query = new NativeSearchQueryBuilder()
            .withQuery(queryBuilder)
            .addAggregation(distinctName)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Cardinality cardinality = aggregations.get("distinctName");
            log.info("计算 name 的去重后的数量:{} ", cardinality.getValue());
        }
    }
}

在这里插入图片描述

8. 查询 BuyCount 的平均值
@Test
void aggs() {
    AvgAggregationBuilder buyCountAvg = AggregationBuilders.avg("buyCountAvg").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .addAggregation(buyCountAvg)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Avg avg = aggregations.get("buyCountAvg");
            log.info("计算 buyCount 的平均值:{} ", avg.getValue());
        }
    }
}

在这里插入图片描述

9. 一次查询 总数,最大值,最小值,平均值,总和
@Test
void aggs() {
    StatsAggregationBuilder stats = AggregationBuilders.stats("stats").field("buyCount");
    Query query = new NativeSearchQueryBuilder()
            .addAggregation(stats)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Stats s = aggregations.get("stats");
            log.info("计算 buyCount 的 count:{} ", s.getCount());
            log.info("计算 buyCount 的 min:{} ", s.getMin());
            log.info("计算 buyCount 的 max:{} ", s.getMax());
            log.info("计算 buyCount 的 avg:{} ", s.getAvg());
            log.info("计算 buyCount 的 sum:{} ", s.getSum());
        }
    }
}

在这里插入图片描述

10. 根据 createMonth 分组查询每个月的最大 buyCount
@Test
void aggs() {
    TermsAggregationBuilder createMonthGroup = AggregationBuilders.terms("createMonthGroup").field("createMonth")
            .subAggregation(AggregationBuilders.max("buyCountMax").field("buyCount"));

    Query query = new NativeSearchQueryBuilder()
            .addAggregation(createMonthGroup)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Terms terms = aggregations.get("createMonthGroup");
            terms.getBuckets().forEach(bucket -> {
                String createMonth = bucket.getKeyAsString();
                Aggregations subAggs = bucket.getAggregations();
                if (Objects.nonNull(subAggs)) {
                    Max max = subAggs.get("buyCountMax");
                    log.info("计算 {} 月的最大值为:{} ", createMonth, max.getValue());
                }
            });
        }
    }
}

在这里插入图片描述

11. 查询每 createMonth 下,根据 sex 区分,统计buyCount 的平均值
@Test
void aggs() {
    TermsAggregationBuilder createMonthGroup = AggregationBuilders.terms("createMonthGroup").field("createMonth")
            .subAggregation(AggregationBuilders.terms("sexGroup").field("sex")
                    .subAggregation(AggregationBuilders.avg("buyCountAvg").field("buyCount")));

    Query query = new NativeSearchQueryBuilder()
            .addAggregation(createMonthGroup)
            .build();
    SearchHits<JhTestEntity> search = elasticsearchRestTemplate.search(query, JhTestEntity.class);
    if (search.hasAggregations()) {
        Aggregations aggregations = search.getAggregations();
        if (Objects.nonNull(aggregations)) {
            Terms terms = aggregations.get("createMonthGroup");
            terms.getBuckets().forEach(bucket -> {
                String createMonth = bucket.getKeyAsString();
                Aggregations sexAggs = bucket.getAggregations();
                if (Objects.nonNull(sexAggs)) {
                    Terms sexTerms = sexAggs.get("sexGroup");
                    sexTerms.getBuckets().forEach(sexBucket -> {
                        String sex = sexBucket.getKeyAsString();
                        Aggregations avgAggs = sexBucket.getAggregations();
                        if (Objects.nonNull(avgAggs)) {
                            Avg avg = avgAggs.get("buyCountAvg");
                            log.info("计算 {} 月,{} 性 的平均值为:{} ", createMonth, sex, avg.getValue());
                        }
                    });
                }
            });
        }
    }
}

在这里插入图片描述

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小毕超

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值