ElasticSearch7.3學習(二十九)----聚合實戰之使用Java api實現電視案例

|舊市拾荒|發表於2022-05-28

一、資料準備

建立索引及對映

建立價格、顏色、品牌、售賣日期欄位

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

結果

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

返回結果

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

返回結果

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

返回結果

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

返回結果

ElasticSearch7.3學習(二十九)----聚合實戰之使用Java api實現電視案例

ElasticSearch7.3學習(二十八)----聚合實戰之電視案例
2022-05-25
Elasticsearch
ElasticSearch7.3學習(三十)----ES7.X SQL新特性解析及使用Java api實現sql功能
2022-06-06
ElasticsearchSQLJavaAPI
ElasticSearch7.3 學習之生產環境實時重建索引
2022-03-26
Elasticsearch索引
java8實戰學習
2022-05-17
Java
Java學習之註解Annotation實現原理
2016-07-15
Java
Elasticsearch學習系列三（搜尋案例實戰）
2022-06-22
Elasticsearch
ElasticSearch7.3學習(二十七)----聚合概念（bucket和metric）及其示例
2022-05-22
Elasticsearch
ffmpeg實戰-音視訊合成案例
2021-06-17
視覺化學習：使用WebGL實現網格背景
2024-03-05
視覺化Web
Flutter 入門與實戰（二十九）：Dio 之攔截器
2021-07-15
Flutter
MySQL入門學習之——實戰XtraBackup
2016-01-20
MySql
MySQL入門學習之——實戰mysqldump
2015-04-14
MySql
使用Java實現簡單的鬥地主案例
2020-05-09
Java
深度學習之PyTorch實戰（4）——遷移學習
2023-03-26
深度學習PyTorch遷移學習
線上最佳化之案例實戰
2023-12-11
【備忘】Java菜鳥到大牛學習路線之實戰篇
2017-09-02
Java
MongoDB學習之聚合操作
2021-12-02
MongoDB
Python學習6之簡單實戰
2020-10-18
Python
學習UML實現、泛化、依賴、關聯、聚合、組合
2017-03-06
ElasticSearch7.3 學習之定製分詞器（Analyzer）
2022-03-22
Elasticsearch分詞
計算機視覺實戰的深度學習實戰二：影像預處理
2019-05-12
計算機視覺深度學習
使用Spring Data JDBC實現DDD聚合
2018-09-30
SpringJDBC
Flutter實戰之動畫實現篇
2019-08-22
Flutter動畫
線上業務最佳化之案例實戰
2023-12-10
深度學習實現NBA球星顏值打分完整案例（二）
2018-01-23
深度學習
hyperf 學習專案，使用社群教程實現的一套商城 API
2020-06-16
API
DDD實戰課（實戰篇）--學習筆記
2021-06-19
筆記
android學習視訊（實戰專案演練）
2013-08-15
Android
微服務實戰（二）：使用API Gateway
2017-09-28
微服務APIGateway
webpack 學習筆記：實戰之 babel 編碼
2020-09-27
Web筆記Babel
K8S系列學習之Pod實戰
2020-10-13
K8S
使用 Flask 實現 RESTful API
2016-06-14
FlaskRESTAPI
ARM學習之GPIO實現流水燈
2017-08-06
AI實戰 | 由淺入深，手把手帶你實現Java轉型學習助手
2024-03-01
AIJava
實戰架構 API 伺服器學習筆記（1）序言
2018-09-15
架構API伺服器筆記
Nancy之實現API的功能
2018-05-21
NaNAPI
Java複習筆記第二篇--集合學習之實現原理二
2019-01-19
Java筆記
使用Golang建立RESTful API的最佳實踐案例
2022-07-31
GolangRESTAPI

ElasticSearch7.3學習(二十九)----聚合實戰之使用Java api實現電視案例

一、資料準備

二、 按照顏色分組，計算每個顏色賣出的個數

三、按照顏色分組，計算每個顏色賣出的個數，每個顏色賣出的平均價格

四、按照顏色分組，計算每個顏色賣出的個數，以及每個顏色賣出的平均值、最大值、最小值、總和

五、按照售價每2000價格劃分範圍，算出每個區間的銷售總額

六、計算每個季度的銷售總額

相關文章

二、按照顏色分組，計算每個顏色賣出的個數