ES 聚合-嵌套桶
数据准备
-
PUT cars { "mappings": { "transactions": { "properties": { "color": { "type": "keyword" }, "make": { "type": "keyword" }, "price": { "type": "long" }, "sold": { "type": "date" } } } } }
-
POST /cars/transactions/_bulk { "index": {}} { "price" : 10000, "color" : "red", "make" : "honda", "sold" : "2014-10-28" } { "index": {}} { "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05" } { "index": {}} { "price" : 30000, "color" : "green", "make" : "ford", "sold" : "2014-05-18" } { "index": {}} { "price" : 15000, "color" : "blue", "make" : "toyota", "sold" : "2014-07-02" } { "index": {}} { "price" : 12000, "color" : "green", "make" : "toyota", "sold" : "2014-08-19" } { "index": {}} { "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05" } { "index": {}} { "price" : 80000, "color" : "red", "make" : "bmw", "sold" : "2014-01-01" } { "index": {}} { "price" : 25000, "color" : "blue", "make" : "ford", "sold" : "2014-02-12" }
查询
-
要求:每个颜色的汽车制造商的分布。即先按颜色分类,再按制造商分类
-
GET /cars/transactions/_search { "size" : 0, "aggs" : { "popular_colors" : { "terms" : { "field" : "color" }, "aggs": { "make": { "terms": { "field": "make" } } } } } }
-
返回结果 "aggregations": { "popular_colors": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "red", "doc_count": 4, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "honda", "doc_count": 3 }, { "key": "bmw", "doc_count": 1 } ] } }, { "key": "blue", "doc_count": 2, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "ford", "doc_count": 1 }, { "key": "toyota", "doc_count": 1 } ] } }, { "key": "green", "doc_count": 2, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "ford", "doc_count": 1 }, { "key": "toyota", "doc_count": 1 } ] } } ] } }
-
可以看到,另一个聚合make被添加到了color颜色桶中,terms桶会为每一个制造商生成唯一的桶
-
@Test public void test03() { TermsAggregationBuilder colorTermsAggregationBuilder = AggregationBuilders.terms("popular_colors").field("color"); TermsAggregationBuilder makeTermsAggregationBuilder = AggregationBuilders.terms("popular_make").field("make"); colorTermsAggregationBuilder.subAggregation(makeTermsAggregationBuilder); SearchResponse searchResponse = elasticsearchTemplate.getClient().prepareSearch("cars") .setTypes("transactions") .addAggregation(colorTermsAggregationBuilder) .execute() .actionGet(); StringTerms stringTerms = searchResponse.getAggregations().get("popular_colors"); List<StringTerms.Bucket> buckets = stringTerms.getBuckets(); for (StringTerms.Bucket bucket : buckets){ Object key = bucket.getKey(); long docCount = bucket.getDocCount(); System.out.println("颜色:"+key+"---"+docCount); StringTerms makeStringTerms = bucket.getAggregations().get("popular_make"); for (StringTerms.Bucket makeBucket : makeStringTerms.getBuckets()){ Object makeBucketKey = makeBucket.getKey(); long makeBucketDocCount = makeBucket.getDocCount(); System.out.println(" 制造商:"+makeBucketKey+"---"+makeBucketDocCount); } } }
-
返回结果 颜色:red---4 制造商:honda---3 制造商:bmw---1 颜色:blue---2 制造商:ford---1 制造商:toyota---1 颜色:green---2 制造商:ford---1 制造商:toyota---1
修改
-
为每个汽车生成商计算最低和最高的价格:
-
GET /cars/transactions/_search { "size" : 0, "aggs": { "colors": { "terms": { "field": "color" }, "aggs": { "avg_price": { "avg": { "field": "price" } }, "make" : { "terms" : { "field" : "make" }, "aggs" : { "min_price" : { "min": { "field": "price"} }, "max_price" : { "max": { "field": "price"} } } } } } } }
-
返回结果 "aggregations": { "colors": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "red", "doc_count": 4, "avg_price": { "value": 32500 }, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "honda", "doc_count": 3, "max_price": { "value": 20000 }, "min_price": { "value": 10000 } }, { "key": "bmw", "doc_count": 1, "max_price": { "value": 80000 }, "min_price": { "value": 80000 } } ] } }, { "key": "blue", "doc_count": 2, "avg_price": { "value": 20000 }, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "ford", "doc_count": 1, "max_price": { "value": 25000 }, "min_price": { "value": 25000 } }, { "key": "toyota", "doc_count": 1, "max_price": { "value": 15000 }, "min_price": { "value": 15000 } } ] } }, { "key": "green", "doc_count": 2, "avg_price": { "value": 21000 }, "make": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "ford", "doc_count": 1, "max_price": { "value": 30000 }, "min_price": { "value": 30000 } }, { "key": "toyota", "doc_count": 1, "max_price": { "value": 12000 }, "min_price": { "value": 12000 } } ] } } ] } }
-
@Test public void test04() { // 每种颜色中各个制造商价格的最大值、最小值 MaxAggregationBuilder maxAggregationBuilder = AggregationBuilders.max("popular_price_max").field("price"); MinAggregationBuilder minAggregationBuilder = AggregationBuilders.min("popular_price_min").field("price"); TermsAggregationBuilder makeTermsAggregationBuilder = AggregationBuilders.terms("popular_make").field("make"); makeTermsAggregationBuilder.subAggregation(maxAggregationBuilder).subAggregation(minAggregationBuilder); // 每种颜色的价格平均值 AvgAggregationBuilder avgAggregationBuilder = AggregationBuilders.avg("avg_price").field("price"); TermsAggregationBuilder colorTermsAggregationBuilder = AggregationBuilders.terms("popular_colors").field("color"); colorTermsAggregationBuilder.subAggregation(avgAggregationBuilder).subAggregation(makeTermsAggregationBuilder); SearchResponse searchResponse = elasticsearchTemplate.getClient().prepareSearch("cars") .setTypes("transactions") .addAggregation(colorTermsAggregationBuilder) .execute() .actionGet(); StringTerms stringTerms = searchResponse.getAggregations().get("popular_colors"); List<StringTerms.Bucket> buckets = stringTerms.getBuckets(); for (StringTerms.Bucket bucket : buckets){ // 颜色和数量 Object key = bucket.getKey(); long docCount = bucket.getDocCount(); System.out.println("颜色:"+key+"---"+docCount); // 平均价格 InternalAvg internalAvg = bucket.getAggregations().get("avg_price"); double value = internalAvg.getValue(); System.out.println(" 平均价格:"+value); // 制造商 StringTerms makeStringTerms = bucket.getAggregations().get("popular_make"); for (StringTerms.Bucket makeBucket : makeStringTerms.getBuckets()){ Object makeBucketKey = makeBucket.getKey(); long makeBucketDocCount = makeBucket.getDocCount(); System.out.println(" 制造商:"+makeBucketKey+"---"+makeBucketDocCount); InternalMax internalMax = makeBucket.getAggregations().get("popular_price_max"); double maxValue = internalMax.getValue(); System.out.println(" 价格最大值:"+maxValue); InternalMin internalMin = makeBucket.getAggregations().get("popular_price_min"); double minValue = internalMin.getValue(); System.out.println(" 价格最小值:"+minValue); } } }
-
返回结果 颜色:red---4 平均价格:32500.0 制造商:honda---3 价格最大值:20000.0 价格最小值:10000.0 制造商:bmw---1 价格最大值:80000.0 价格最小值:80000.0 颜色:blue---2 平均价格:20000.0 制造商:ford---1 价格最大值:25000.0 价格最小值:25000.0 制造商:toyota---1 价格最大值:15000.0 价格最小值:15000.0 颜色:green---2 平均价格:21000.0 制造商:ford---1 价格最大值:30000.0 价格最小值:30000.0 制造商:toyota---1 价格最大值:12000.0 价格最小值:12000.0