> 虚幻大学 > ElasticSearch7.3学习(二十九)—-聚合实战之使用Java api实现电视案例

ElasticSearch7.3学习(二十九)—-聚合实战之使用Java api实现电视案例

虚幻大学 xuhss 172℃ 0评论
文章目录 显示

? 优质资源分享 ?

学习路线指引（点击解锁） 知识定位 人群定位
? Python实战微信订餐小程序 ? 进阶级 本课程是python flask+微信小程序的完美结合，从项目搭建到腾讯云部署上线，打造一个全栈订餐系统。
?Python量化交易实战? 入门级 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统

一、数据准备

创建索引及映射

建立价格、颜色、品牌、售卖日期字段

PUT /tvs
PUT /tvs/_mapping
{
  "properties": {
    "price": {
      "type": "long"
    },
    "color": {
      "type": "keyword"
    },
    "brand": {
      "type": "keyword"
    },
    "sold\_date": {
      "type": "date"
    }
  }
}

插入数据

POST /tvs/_bulk
{"index":{}}
{"price":1000,"color":"红色","brand":"长虹","sold\_date":"2019-10-28"}
{"index":{}}
{"price":2000,"color":"红色","brand":"长虹","sold\_date":"2019-11-05"}
{"index":{}}
{"price":3000,"color":"绿色","brand":"小米","sold\_date":"2019-05-18"}
{"index":{}}
{"price":1500,"color":"蓝色","brand":"TCL","sold\_date":"2019-07-02"}
{"index":{}}
{"price":1200,"color":"绿色","brand":"TCL","sold\_date":"2019-08-19"}
{"index":{}}
{"price":2000,"color":"红色","brand":"长虹","sold\_date":"2019-11-05"}
{"index":{}}
{"price":8000,"color":"红色","brand":"三星","sold\_date":"2020-01-01"}
{"index":{}}
{"price":2500,"color":"蓝色","brand":"小米","sold\_date":"2020-02-12"}

二、 按照颜色分组，计算每个颜色卖出的个数

ES语句

GET /tvs/_search
{
  "size": 0,
  "query": {
    "match\_all": {}
  },
  "aggs": {
    "group\_by\_color": {
      "terms": {
        "field": "color"
      }
    }
  }
}

返回

{
  "took" : 1,
  "timed\_out" : false,
  "\_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max\_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group\_by\_color" : {
      "doc\_count\_error\_upper\_bound" : 0,
      "sum\_other\_doc\_count" : 0,
      "buckets" : [
        {
          "key" : "红色",
          "doc\_count" : 4
        },
        {
          "key" : "绿色",
          "doc\_count" : 2
        },
        {
          "key" : "蓝色",
          "doc\_count" : 2
        }
      ]
    }
  }
}

Java代码

//按照颜色分组，计算每个颜色卖出的个数
    @Test
    public void testAggs() throws IOException {
        //1 构建请求
        SearchRequest searchRequest=new SearchRequest("tvs");
        //请求体
        SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
        searchSourceBuilder.size(0);
        searchSourceBuilder.query(QueryBuilders.matchAllQuery());
        TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group\_by\_color").field("color");
        searchSourceBuilder.aggregation(termsAggregationBuilder);
        //请求体放入请求头
        searchRequest.source(searchSourceBuilder);
        //2 执行
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        Aggregations aggregations = searchResponse.getAggregations();
        Terms group\_by\_color = aggregations.get("group\_by\_color");
        List <span class="hljs-keyword"extends Terms.Bucket> buckets = group_by_color.getBuckets();
        for (Terms.Bucket bucket : buckets) {
            String key = bucket.getKeyAsString();
            System.out.println("key:"+key);
            long docCount = bucket.getDocCount();
            System.out.println("docCount:"+docCount);
            System.out.println("=================================");
        }
    }

结果

ebd5b1a46d201c82dc74356c563bc33b - ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例

三、按照颜色分组，计算每个颜色卖出的个数，每个颜色卖出的平均价格

ES语句

GET /tvs/_search
{
  "size": 0,
  "query": {
    "match\_all": {}
  },
  "aggs": {
    "group\_by\_color": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "avg\_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

返回结果

{
  "took" : 2,
  "timed\_out" : false,
  "\_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max\_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group\_by\_color" : {
      "doc\_count\_error\_upper\_bound" : 0,
      "sum\_other\_doc\_count" : 0,
      "buckets" : [
        {
          "key" : "红色",
          "doc\_count" : 4,
          "avg\_price" : {
            "value" : 3250.0
          }
        },
        {
          "key" : "绿色",
          "doc\_count" : 2,
          "avg\_price" : {
            "value" : 2100.0
          }
        },
        {
          "key" : "蓝色",
          "doc\_count" : 2,
          "avg\_price" : {
            "value" : 2000.0
          }
        }
      ]
    }
  }
}

Java代码

// 按照颜色分组，计算每个颜色卖出的个数，每个颜色卖出的平均价格
    @Test
    public void testAggsAndAvg() throws IOException {
        //1 构建请求
        SearchRequest searchRequest=new SearchRequest("tvs");
        //请求体
        SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
        searchSourceBuilder.size(0);
        searchSourceBuilder.query(QueryBuilders.matchAllQuery());
        TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group\_by\_color").field("color");
        //terms聚合下填充一个子聚合
        AvgAggregationBuilder avgAggregationBuilder = AggregationBuilders.avg("avg\_price").field("price");
        termsAggregationBuilder.subAggregation(avgAggregationBuilder);
        searchSourceBuilder.aggregation(termsAggregationBuilder);
        //请求体放入请求头
        searchRequest.source(searchSourceBuilder);
        //2 执行
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        Aggregations aggregations = searchResponse.getAggregations();
        Terms group\_by\_color = aggregations.get("group\_by\_color");
        List <span class="hljs-keyword"extends Terms.Bucket> buckets = group_by_color.getBuckets();
        for (Terms.Bucket bucket : buckets) {
            String key = bucket.getKeyAsString();
            System.out.println("key:"+key);
            long docCount = bucket.getDocCount();
            System.out.println("docCount:"+docCount);
            Aggregations aggregations1 = bucket.getAggregations();
            Avg avg\_price = aggregations1.get("avg\_price");
            double value = avg_price.getValue();
            System.out.println("value:"+value);
            System.out.println("=================================");
        }
    }

返回结果

dceb740a814e8743dcf5b6332f4260e2 - ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例

四、按照颜色分组，计算每个颜色卖出的个数，以及每个颜色卖出的平均值、最大值、最小值、总和

ES语句

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "group\_by\_color": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "avg\_price": {
          "avg": {
            "field": "price"
          }
        },
        "min\_price": {
          "min": {
            "field": "price"
          }
        },
        "max\_price": {
          "max": {
            "field": "price"
          }
        },
        "sum\_price": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

返回结果

{
  "took" : 1,
  "timed\_out" : false,
  "\_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max\_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group\_by\_color" : {
      "doc\_count\_error\_upper\_bound" : 0,
      "sum\_other\_doc\_count" : 0,
      "buckets" : [
        {
          "key" : "红色",
          "doc\_count" : 4,
          "max\_price" : {
            "value" : 8000.0
          },
          "min\_price" : {
            "value" : 1000.0
          },
          "avg\_price" : {
            "value" : 3250.0
          },
          "sum\_price" : {
            "value" : 13000.0
          }
        },
        {
          "key" : "绿色",
          "doc\_count" : 2,
          "max\_price" : {
            "value" : 3000.0
          },
          "min\_price" : {
            "value" : 1200.0
          },
          "avg\_price" : {
            "value" : 2100.0
          },
          "sum\_price" : {
            "value" : 4200.0
          }
        },
        {
          "key" : "蓝色",
          "doc\_count" : 2,
          "max\_price" : {
            "value" : 2500.0
          },
          "min\_price" : {
            "value" : 1500.0
          },
          "avg\_price" : {
            "value" : 2000.0
          },
          "sum\_price" : {
            "value" : 4000.0
          }
        }
      ]
    }
  }
}

Java代码

    // 按照颜色分组，计算每个颜色卖出的个数，以及每个颜色卖出的平均值、最大值、最小值、总和。
    @Test
    public void testAggsAndMore() throws IOException {
        //1 构建请求
        SearchRequest searchRequest=new SearchRequest("tvs");
        //请求体
        SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
        searchSourceBuilder.size(0);
        searchSourceBuilder.query(QueryBuilders.matchAllQuery());
        TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group\_by\_color").field("color");
        //termsAggregationBuilder里放入多个子聚合
        AvgAggregationBuilder avgAggregationBuilder = AggregationBuilders.avg("avg\_price").field("price");
        MinAggregationBuilder minAggregationBuilder = AggregationBuilders.min("min\_price").field("price");
        MaxAggregationBuilder maxAggregationBuilder = AggregationBuilders.max("max\_price").field("price");
        SumAggregationBuilder sumAggregationBuilder = AggregationBuilders.sum("sum\_price").field("price");

        termsAggregationBuilder.subAggregation(avgAggregationBuilder);
        termsAggregationBuilder.subAggregation(minAggregationBuilder);
        termsAggregationBuilder.subAggregation(maxAggregationBuilder);
        termsAggregationBuilder.subAggregation(sumAggregationBuilder);
        searchSourceBuilder.aggregation(termsAggregationBuilder);
        //请求体放入请求头
        searchRequest.source(searchSourceBuilder);
        //2 执行
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        Aggregations aggregations = searchResponse.getAggregations();
        Terms group\_by\_color = aggregations.get("group\_by\_color");
        List <span class="hljs-keyword"extends Terms.Bucket> buckets = group_by_color.getBuckets();
        for (Terms.Bucket bucket : buckets) {
            String key = bucket.getKeyAsString();
            System.out.println("key:"+key);

            long docCount = bucket.getDocCount();
            System.out.println("docCount:"+docCount);

            Aggregations aggregations1 = bucket.getAggregations();

            Max max\_price = aggregations1.get("max\_price");
            double maxPriceValue = max_price.getValue();
            System.out.println("maxPriceValue:"+maxPriceValue);

            Min min\_price = aggregations1.get("min\_price");
            double minPriceValue = min_price.getValue();
            System.out.println("minPriceValue:"+minPriceValue);

            Avg avg\_price = aggregations1.get("avg\_price");
            double avgPriceValue = avg_price.getValue();
            System.out.println("avgPriceValue:"+avgPriceValue);

            Sum sum\_price = aggregations1.get("sum\_price");
            double sumPriceValue = sum_price.getValue();
            System.out.println("sumPriceValue:"+sumPriceValue);

            System.out.println("=================================");
        }
    }

返回结果

52ce10df76eb1d196f0248de82aeb7c6 - ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例

五、按照售价每2000价格划分范围，算出每个区间的销售总额

ES语句

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "by\_histogram": {
      "histogram": {
        "field": "price",
        "interval": 2000
      },
      "aggs": {
        "income": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

返回结果

查看代码


{
  "took" : 0,
  "timed\_out" : false,
  "\_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max\_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "by\_histogram" : {
      "buckets" : [
        {
          "key" : 0.0,
          "doc\_count" : 3,
          "income" : {
            "value" : 3700.0
          }
        },
        {
          "key" : 2000.0,
          "doc\_count" : 4,
          "income" : {
            "value" : 9500.0
          }
        },
        {
          "key" : 4000.0,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        },
        {
          "key" : 6000.0,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        },
        {
          "key" : 8000.0,
          "doc\_count" : 1,
          "income" : {
            "value" : 8000.0
          }
        }
      ]
    }
  }
}

Java代码

    // 按照售价每2000价格划分范围，算出每个区间的销售总额 histogram
    @Test
    public void testAggsAndHistogram() throws IOException {
        //1 构建请求
        SearchRequest searchRequest=new SearchRequest("tvs");
        //请求体
        SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
        searchSourceBuilder.size(0);
        searchSourceBuilder.query(QueryBuilders.matchAllQuery());
        HistogramAggregationBuilder histogramAggregationBuilder = 
                AggregationBuilders.histogram("by\_histogram").field("price").interval(2000);
        SumAggregationBuilder sumAggregationBuilder = AggregationBuilders.sum("income").field("price");
        histogramAggregationBuilder.subAggregation(sumAggregationBuilder);
        searchSourceBuilder.aggregation(histogramAggregationBuilder);
        //请求体放入请求头
        searchRequest.source(searchSourceBuilder);
        //2 执行
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        Aggregations aggregations = searchResponse.getAggregations();
        Histogram group\_by\_color = aggregations.get("by\_histogram");
        List <span class="hljs-keyword"extends Histogram.Bucket> buckets = group_by_color.getBuckets();
        for (Histogram.Bucket bucket : buckets) {
            String keyAsString = bucket.getKeyAsString();
            System.out.println("keyAsString:"+keyAsString);
            long docCount = bucket.getDocCount();
            System.out.println("docCount:"+docCount);

            Aggregations aggregations1 = bucket.getAggregations();
            Sum income = aggregations1.get("income");
            double value = income.getValue();
            System.out.println("value:"+value);

            System.out.println("=================================");
        }
    }

返回结果

62948ff96a0ed0c998e312b5c7f18502 - ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例

六、计算每个季度的销售总额

ES语句

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "sales": {
      "date\_histogram": {
        "field": "sold\_date",
        "interval": "quarter",
        "format": "yyyy-MM-dd",
        "min\_doc\_count": 0,
        "extended\_bounds": {
          "min": "2019-01-01",
          "max": "2020-12-31"
        }
      },
      "aggs": {
        "income": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

返回结果

查看代码


#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
  "took" : 6,
  "timed\_out" : false,
  "\_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max\_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "sales" : {
      "buckets" : [
        {
          "key\_as\_string" : "2019-01-01",
          "key" : 1546300800000,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        },
        {
          "key\_as\_string" : "2019-04-01",
          "key" : 1554076800000,
          "doc\_count" : 1,
          "income" : {
            "value" : 3000.0
          }
        },
        {
          "key\_as\_string" : "2019-07-01",
          "key" : 1561939200000,
          "doc\_count" : 2,
          "income" : {
            "value" : 2700.0
          }
        },
        {
          "key\_as\_string" : "2019-10-01",
          "key" : 1569888000000,
          "doc\_count" : 3,
          "income" : {
            "value" : 5000.0
          }
        },
        {
          "key\_as\_string" : "2020-01-01",
          "key" : 1577836800000,
          "doc\_count" : 2,
          "income" : {
            "value" : 10500.0
          }
        },
        {
          "key\_as\_string" : "2020-04-01",
          "key" : 1585699200000,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        },
        {
          "key\_as\_string" : "2020-07-01",
          "key" : 1593561600000,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        },
        {
          "key\_as\_string" : "2020-10-01",
          "key" : 1601510400000,
          "doc\_count" : 0,
          "income" : {
            "value" : 0.0
          }
        }
      ]
    }
  }
}

Java代码

    // 计算每个季度的销售总额
    @Test
    public void testAggsAndDateHistogram() throws IOException {
        //1 构建请求
        SearchRequest searchRequest=new SearchRequest("tvs");
        //请求体
        SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
        searchSourceBuilder.size(0);
        searchSourceBuilder.query(QueryBuilders.matchAllQuery());
        DateHistogramAggregationBuilder dateHistogramAggregationBuilder = 
                AggregationBuilders.dateHistogram("date\_histogram")
                .field("sold\_date")
                .calendarInterval(DateHistogramInterval.QUARTER)
                .format("yyyy-MM-dd")
                .minDocCount(0)
                .extendedBounds(new ExtendedBounds("2019-01-01", "2020-12-31"));
        SumAggregationBuilder sumAggregationBuilder = 
                AggregationBuilders.sum("income").field("price");
        dateHistogramAggregationBuilder.subAggregation(sumAggregationBuilder);
        searchSourceBuilder.aggregation(dateHistogramAggregationBuilder);
        //请求体放入请求头
        searchRequest.source(searchSourceBuilder);
        //2 执行
        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        Aggregations aggregations = searchResponse.getAggregations();
        ParsedDateHistogram date\_histogram = aggregations.get("date\_histogram");
        List <span class="hljs-keyword"extends Histogram.Bucket> buckets = date_histogram.getBuckets();
        for (Histogram.Bucket bucket : buckets) {
            String keyAsString = bucket.getKeyAsString();
            System.out.println("keyAsString:"+keyAsString);
            long docCount = bucket.getDocCount();
            System.out.println("docCount:"+docCount);

            Aggregations aggregations1 = bucket.getAggregations();
            Sum income = aggregations1.get("income");
            double value = income.getValue();
            System.out.println("value:"+value);
            System.out.println("====================");
        }
    }

返回结果

86c888afc6676628d5c04471fe56a0a6 - ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例

如果您觉得阅读本文对您有帮助，请点一下“**推荐**”按钮，您的**“推荐”**将是我最大的写作动力！欢迎各位转载，但是未经作者本人同意，转载文章之后**必须在文章页面明显位置给出作者和原文连接**，否则保留追究法律责任的权利。

转载请注明：xuhss » ElasticSearch7.3学习(二十九)—-聚合实战之使用Java api实现电视案例

喜欢 (0)

您必须 登录 才能发表评论！