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

    一、创建测试索引

    创建索引结构，向 ES 服务发送 PUT 请求：

    http://127.0.0.1:9200/jh_test

    • 1
    {
	"settings": {},
	"mappings": {
		"properties": {
			"name": {
				"type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
			},
			"sex": {
				"type": "keyword"
			},
			"buyCount": {
				"type": "long"
			},
            "createMonth":{
                "type":"keyword"
            }
		}
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    其中字段的含义为：
    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"}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    二、聚合查询

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

    http://127.0.0.1:9200/jh_test/_search

    • 1
    "aggs" : {
    "" : {                                 <!--聚合名称 -->
        "" : {                             <!--聚合类型 -->
            <aggregation_body>                               <!--聚合具体字段 -->
        }
        [,"meta" : {  [<meta_data_body>] } ]?                <!--元信息 -->
        [,"aggs" : { [<sub_aggregation>]+ } ]?       <!--子聚合 -->
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    1. 查询 buyCount 的总和：
    {
  "size":0,
  "aggs":{
    "buyCountSum":{
      "sum": {
        "field": "buyCount"
      }
    }
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    在这里插入图片描述

    三、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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    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());
         }
     }
 }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

    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());
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    在这里插入图片描述

    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());
                }
            });
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    在这里插入图片描述

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    在这里插入图片描述

  • 相关阅读:
    C++多态
    如何提高bp神经网络精度,bp神经网络收敛速度慢
    【并发编程】多线程读写同一变量的并发问题（并发编程启动）
    RabbitMQ快速入门
    在Windows部署项目出现问题记录
    IB中文考试是什么？如何考？
    MySQL8.0中你不得不知道的索引新特性
    element-ui 中 el-tree 和 el-table 样式调整
    蓝桥等考Python组别八级005
    NLP实践！文本语法纠错模型实战，搭建你的贴身语法修改小助手 ⛵
  • 原文地址：https://blog.csdn.net/qq_43692950/article/details/127599352