ElasticSearch7.3學習(二十八)----聚合實戰之電視案例

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

一、電視案例

1.1 資料準備

建立索引及對映

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

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"}

1.2 統計哪種顏色的電視銷量最高

不加query 預設查詢全部

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "popular_colors": {
      "terms": {
        "field": "color"
      }
    }
  }
}

查詢條件解析

size：只獲取聚合結果，而不要執行聚合的原始資料
aggs：固定語法，要對一份資料執行分組聚合操作
popular_colors：就是對每個aggs，都要起一個名字，
terms：根據欄位的值進行分組
field：根據指定的欄位的值進行分組

{
  "took" : 121,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "popular_colors" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "紅色",
          "doc_count" : 4
        },
        {
          "key" : "綠色",
          "doc_count" : 2
        },
        {
          "key" : "藍色",
          "doc_count" : 2
        }
      ]
    }
  }
}

返回結果解析

hits.hits：我們指定了size是0，所以hits.hits就是空的
aggregations：聚合結果
popular_color：我們指定的某個聚合的名稱
buckets：根據我們指定的field劃分出的buckets
key：每個bucket對應的那個值
doc_count：這個bucket分組內，有多少個數量，其實就是這種顏色的銷量
bucket中的資料的預設的排序規則：按照doc_count降序排序

1.3 統計每種顏色電視平均價格

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "colors": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

在一個aggs執行的bucket操作（terms），平級的json結構下，再加一個aggs，

這個第二個aggs內部，同樣取個名字，執行一個metric操作，avg，對之前的每個bucket中的資料的指定的field，求一個平均值

{
  "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" : {
    "colors" : {
      "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
          }
        }
      ]
    }
  }
}

返回結果解析：

avg_price：我們自己取的metric aggs的名字
value：我們的metric計算的結果，每個bucket中的資料的price欄位求平均值後的結果

相當於sql: select avg(price) from tvs group by color

1.4 每個顏色下，平均價格及每個顏色下，每個品牌的平均價格

多個子聚合

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "color_avg_price": {
          "avg": {
            "field": "price"
          }
        },
        "group_by_brand": {
          "terms": {
            "field": "brand"
          },
          "aggs": {
            "brand_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,
          "color_avg_price" : {
            "value" : 3250.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "長虹",
                "doc_count" : 3,
                "brand_avg_price" : {
                  "value" : 1666.6666666666667
                }
              },
              {
                "key" : "三星",
                "doc_count" : 1,
                "brand_avg_price" : {
                  "value" : 8000.0
                }
              }
            ]
          }
        },
        {
          "key" : "綠色",
          "doc_count" : 2,
          "color_avg_price" : {
            "value" : 2100.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "TCL",
                "doc_count" : 1,
                "brand_avg_price" : {
                  "value" : 1200.0
                }
              },
              {
                "key" : "小米",
                "doc_count" : 1,
                "brand_avg_price" : {
                  "value" : 3000.0
                }
              }
            ]
          }
        },
        {
          "key" : "藍色",
          "doc_count" : 2,
          "color_avg_price" : {
            "value" : 2000.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "TCL",
                "doc_count" : 1,
                "brand_avg_price" : {
                  "value" : 1500.0
                }
              },
              {
                "key" : "小米",
                "doc_count" : 1,
                "brand_avg_price" : {
                  "value" : 2500.0
                }
              }
            ]
          }
        }
      ]
    }
  }
}

1.5 求出每個顏色的銷售數量，平均價格、最小价格、最大價格、價格總和

GET /tvs/_search
{
  "size": 0,
  "aggs": {
    "colors": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "color_avg_price": {
          "avg": {
            "field": "price"
          }
        },
        "color_min_price": {
          "min": {
            "field": "price"
          }
        },
        "color_max_price": {
          "max": {
            "field": "price"
          }
        },
        "color_sum_price": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

檢視程式碼

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "colors" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "紅色",
          "doc_count" : 4,
          "color_avg_price" : {
            "value" : 3250.0
          },
          "color_min_price" : {
            "value" : 1000.0
          },
          "color_max_price" : {
            "value" : 8000.0
          },
          "color_sum_price" : {
            "value" : 13000.0
          }
        },
        {
          "key" : "綠色",
          "doc_count" : 2,
          "color_avg_price" : {
            "value" : 2100.0
          },
          "color_min_price" : {
            "value" : 1200.0
          },
          "color_max_price" : {
            "value" : 3000.0
          },
          "color_sum_price" : {
            "value" : 4200.0
          }
        },
        {
          "key" : "藍色",
          "doc_count" : 2,
          "color_avg_price" : {
            "value" : 2000.0
          },
          "color_min_price" : {
            "value" : 1500.0
          },
          "color_max_price" : {
            "value" : 2500.0
          },
          "color_sum_price" : {
            "value" : 4000.0
          }
        }
      ]
    }
  }
}

返回結果解析

count：bucket，terms，自動就會有一個doc_count，就相當於是count
avg：avg aggs，求平均值
max：求一個bucket內，指定field值最大的那個資料
min：求一個bucket內，指定field值最小的那個資料
sum：求一個bucket內，指定field值的總和

1.6 劃分範圍 histogram（直方圖），求出價格每2000為一個區間，每個區間的銷售總額

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

histogram：類似於terms，也是進行bucket分組操作，接收一個field，按照這個field的值的各個範圍區間，進行bucket分組操作

"histogram": {
    "field": "price",
    "interval": 2000
}

interval：2000，劃分範圍，左閉右開區間，[0~2000），2000~4000，4000~6000，6000~8000，8000~10000

bucket有了之後，一樣的，去對每個bucket執行avg，count，sum，max，min，等各種metric操作，聚合分析

1.7 按照日期分組聚合，求出每個月銷售個數

引數解析：

date_histogram，按照我們指定的某個date型別的日期field，以及日期interval，按照一定的日期間隔，去劃分bucket
min_doc_count：即使某個日期interval，2017-01-01~2017-01-31中，一條資料都沒有，那麼這個區間也是要返回的，不然預設是會過濾掉這個區間的 extended_bounds，
min，max：劃分bucket的時候，會限定在這個起始日期，和截止日期內

GET /tvs/_search
{
   "size" : 0,
   "aggs": {
      "date_sales": {
         "date_histogram": {
            "field": "sold_date",
            "interval": "month", 
            "format": "yyyy-MM-dd",
            "min_doc_count" : 0, 
            "extended_bounds" : { 
                "min" : "2019-01-01",
                "max" : "2020-12-31"
            }
         }
      }
   }
}

檢視程式碼

#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
  "took" : 11,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "date_sales" : {
      "buckets" : [
        {
          "key_as_string" : "2019-01-01",
          "key" : 1546300800000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-02-01",
          "key" : 1548979200000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-03-01",
          "key" : 1551398400000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-04-01",
          "key" : 1554076800000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-05-01",
          "key" : 1556668800000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2019-06-01",
          "key" : 1559347200000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-07-01",
          "key" : 1561939200000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2019-08-01",
          "key" : 1564617600000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2019-09-01",
          "key" : 1567296000000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2019-10-01",
          "key" : 1569888000000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2019-11-01",
          "key" : 1572566400000,
          "doc_count" : 2
        },
        {
          "key_as_string" : "2019-12-01",
          "key" : 1575158400000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-01-01",
          "key" : 1577836800000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2020-02-01",
          "key" : 1580515200000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2020-03-01",
          "key" : 1583020800000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-04-01",
          "key" : 1585699200000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-05-01",
          "key" : 1588291200000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-06-01",
          "key" : 1590969600000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-07-01",
          "key" : 1593561600000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-08-01",
          "key" : 1596240000000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-09-01",
          "key" : 1598918400000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-10-01",
          "key" : 1601510400000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-11-01",
          "key" : 1604188800000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "2020-12-01",
          "key" : 1606780800000,
          "doc_count" : 0
        }
      ]
    }
  }
}

注意：

#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.

1.8 統計每季度每個品牌的銷售額，及每季度的銷售總額

GET /tvs/_search 
{
  "size": 0,
  "aggs": {
    "group_by_sold_date": {
      "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": {
        "group_by_brand": {
          "terms": {
            "field": "brand"
          },
          "aggs": {
            "sum_price": {
              "sum": {
                "field": "price"
              }
            }
          }
        },
        "total_sum_price": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

檢視程式碼

#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
  "took" : 3,
  "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_sold_date" : {
      "buckets" : [
        {
          "key_as_string" : "2019-01-01",
          "key" : 1546300800000,
          "doc_count" : 0,
          "total_sum_price" : {
            "value" : 0.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2019-04-01",
          "key" : 1554076800000,
          "doc_count" : 1,
          "total_sum_price" : {
            "value" : 3000.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "小米",
                "doc_count" : 1,
                "sum_price" : {
                  "value" : 3000.0
                }
              }
            ]
          }
        },
        {
          "key_as_string" : "2019-07-01",
          "key" : 1561939200000,
          "doc_count" : 2,
          "total_sum_price" : {
            "value" : 2700.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "TCL",
                "doc_count" : 2,
                "sum_price" : {
                  "value" : 2700.0
                }
              }
            ]
          }
        },
        {
          "key_as_string" : "2019-10-01",
          "key" : 1569888000000,
          "doc_count" : 3,
          "total_sum_price" : {
            "value" : 5000.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "長虹",
                "doc_count" : 3,
                "sum_price" : {
                  "value" : 5000.0
                }
              }
            ]
          }
        },
        {
          "key_as_string" : "2020-01-01",
          "key" : 1577836800000,
          "doc_count" : 2,
          "total_sum_price" : {
            "value" : 10500.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "三星",
                "doc_count" : 1,
                "sum_price" : {
                  "value" : 8000.0
                }
              },
              {
                "key" : "小米",
                "doc_count" : 1,
                "sum_price" : {
                  "value" : 2500.0
                }
              }
            ]
          }
        },
        {
          "key_as_string" : "2020-04-01",
          "key" : 1585699200000,
          "doc_count" : 0,
          "total_sum_price" : {
            "value" : 0.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2020-07-01",
          "key" : 1593561600000,
          "doc_count" : 0,
          "total_sum_price" : {
            "value" : 0.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2020-10-01",
          "key" : 1601510400000,
          "doc_count" : 0,
          "total_sum_price" : {
            "value" : 0.0
          },
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      ]
    }
  }
}

1.9 搜尋與聚合結合，查詢某個品牌按顏色銷量

搜尋與聚合可以結合起來。sql語句如下

select count(*)
from tvs
where brand like "%小米%"
group by color

注意：任何的聚合，都必須在搜尋出來的結果資料中之行。

GET /tvs/_search 
{
  "size": 0,
  "query": {
    "term": {
      "brand": {
        "value": "小米"
      }
    }
  },
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "color"
      }
    }
  }
}

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "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" : 1
        },
        {
          "key" : "藍色",
          "doc_count" : 1
        }
      ]
    }
  }
}

1.10 global bucket（全域性桶）：單個品牌與所有品牌銷量對比

GET /tvs/_search 
{
  "size": 0, 
  "query": {
    "term": {
      "brand": {
        "value": "小米"
      }
    }
  },
  "aggs": {
    "single_brand_avg_price": {
      "avg": {
        "field": "price"
      }
    },
    "all": {
      "global": {},
      "aggs": {
        "all_brand_avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

{
  "took" : 61,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "all" : {
      "doc_count" : 8,
      "all_brand_avg_price" : {
        "value" : 2650.0
      }
    },
    "single_brand_avg_price" : {
      "value" : 2750.0
    }
  }
}

返回結果解析：

一個結果，是基於query搜尋結果來聚合的;
一個結果，是對所有資料執行聚合的

1.11 統計價格大於1200的電視平均價格

注意：單獨使用filter 需加上constant_score

GET /tvs/_search 
{
  "size": 0,
  "query": {
    "constant_score": {
      "filter": {
        "range": {
          "price": {
            "gte": 1200
          }
        }
      }
    }
  },
  "aggs": {
    "avg_price": {
      "avg": {
        "field": "price"
      }
    }
  }
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 7,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "avg_price" : {
      "value" : 2885.714285714286
    }
  }
}

1.12 bucket filter：統計品牌最近4年，3年的平均價格

注意：因為是最近的時間，所以讀者實驗的時候，需根據當前時間來自行設定查詢範圍

注意下面的區別

aggs.filter，針對的是聚合去做的
query裡面的filter，是全域性的，會對所有的資料都有影響

GET /tvs/_search 
{
  "size": 0,
  "query": {
    "term": {
      "brand": {
        "value": "小米"
      }
    }
  },
  "aggs": {
    "recent_fouryear": {
      "filter": {
        "range": {
          "sold_date": {
            "gte": "now-4y"
          }
        }
      },
      "aggs": {
        "recent_fouryear_avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    },
    "recent_threeyear": {
      "filter": {
        "range": {
          "sold_date": {
            "gte": "now-3y"
          }
        }
      },
      "aggs": {
        "recent_threeyear_avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "recent_threeyear" : {
      "meta" : { },
      "doc_count" : 2,
      "recent_threeyear_avg_price" : {
        "value" : 2750.0
      }
    },
    "recent_fouryear" : {
      "meta" : { },
      "doc_count" : 2,
      "recent_fouryear_avg_price" : {
        "value" : 2750.0
      }
    }
  }
}

1.13 按每種顏色的平均銷售額降序排序

GET /tvs/_search 
{
  "size": 0,
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "color",
        "order": {
          "avg_price": "desc"
        }
      },
      "aggs": {
        "avg_price": {
          "avg": {
            "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" : {
    "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
          }
        }
      ]
    }
  }
}

1.14 按每種顏色的每種品牌平均銷售額降序排序

GET /tvs/_search    
{
  "size": 0,
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "color"
      },
      "aggs": {
        "group_by_brand": {
          "terms": {
            "field": "brand",
            "order": {
              "avg_price": "desc"
            }
          },
          "aggs": {
            "avg_price": {
              "avg": {
                "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,
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "三星",
                "doc_count" : 1,
                "avg_price" : {
                  "value" : 8000.0
                }
              },
              {
                "key" : "長虹",
                "doc_count" : 3,
                "avg_price" : {
                  "value" : 1666.6666666666667
                }
              }
            ]
          }
        },
        {
          "key" : "綠色",
          "doc_count" : 2,
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "小米",
                "doc_count" : 1,
                "avg_price" : {
                  "value" : 3000.0
                }
              },
              {
                "key" : "TCL",
                "doc_count" : 1,
                "avg_price" : {
                  "value" : 1200.0
                }
              }
            ]
          }
        },
        {
          "key" : "藍色",
          "doc_count" : 2,
          "group_by_brand" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "小米",
                "doc_count" : 1,
                "avg_price" : {
                  "value" : 2500.0
                }
              },
              {
                "key" : "TCL",
                "doc_count" : 1,
                "avg_price" : {
                  "value" : 1500.0
                }
              }
            ]
          }
        }
      ]
    }
  }
}

ElasticSearch7.3學習(二十九)----聚合實戰之使用Java api實現電視案例
2022-05-28
ElasticsearchJavaAPI
ElasticSearch7.3學習(二十七)----聚合概念（bucket和metric）及其示例
2022-05-22
Elasticsearch
ElasticSearch7.3 學習之生產環境實時重建索引
2022-03-26
Elasticsearch索引
MongoDB學習之聚合操作
2021-12-02
MongoDB
ElasticSearch7.3 學習之定製分詞器（Analyzer）
2022-03-22
Elasticsearch分詞
Elasticsearch學習系列三（搜尋案例實戰）
2022-06-22
Elasticsearch
jmeter學習指南之聚合報告
2019-08-20
JMeter
Elasticsearch聚合學習之四：結果排序
2022-09-17
Elasticsearch排序
ffmpeg實戰-音視訊合成案例
2021-06-17
Flutter 案例學習之：ListView
2018-09-09
FlutterView
深度學習之PyTorch實戰（4）——遷移學習
2023-03-26
深度學習PyTorch遷移學習
MySQL入門學習之——實戰XtraBackup
2016-01-20
MySql
MySQL入門學習之——實戰mysqldump
2015-04-14
MySql
Python學習6之簡單實戰
2020-10-18
Python
線上最佳化之案例實戰
2023-12-11
ElasticSearch7.3 學習之倒排索引揭祕及初識分詞器(Analyzer)
2022-03-18
Elasticsearch索引分詞
Flutter 入門與實戰（二十八）：Dio 封裝之金屋藏嬌
2021-07-14
Flutter封裝
Elasticsearch聚合學習之二：區間聚合
2022-09-14
Elasticsearch
android學習視訊（實戰專案演練）
2013-08-15
Android
webpack 學習筆記：實戰之 babel 編碼
2020-09-27
Web筆記Babel
K8S系列學習之Pod實戰
2020-10-13
K8S
線上業務最佳化之案例實戰
2023-12-10
計算機視覺實戰的深度學習實戰二：影像預處理
2019-05-12
計算機視覺深度學習
MySQL之檢視學習
2019-06-18
MySql
gRPC學習之五：gRPC-Gateway實戰
2021-08-18
RPCGateway
ElasticSearch7.3 學習之Mapping核心資料型別及dynamic mapping
2022-03-19
ElasticsearchAPP資料型別
Zabbixzabbix_proxy指令(學習筆記二十八)
2018-05-09
筆記
DDD實戰課（實戰篇）--學習筆記
2021-06-19
筆記
強化學習之蒙特卡洛學習,時序差分學習理論與實戰
2020-12-10
強化學習
gRPC學習之四：實戰四類服務方法
2021-08-17
RPC
java8實戰學習
2022-05-17
Java
《機器學習實戰》學習大綱
2018-12-01
機器學習
開始學習電商實戰
2018-12-27
白日夢的Elasticsearch實戰筆記，32個查詢案例、15個聚合案例、7個查詢優化技巧。
2021-01-27
Elasticsearch筆記優化
深度學習、強化學習核心技術實戰
2021-03-21
深度學習強化學習
Python學習筆記——爬蟲之Scrapy專案實戰
2018-09-03
Python筆記爬蟲
深度學習之tensorflow2實戰：多輸出模型
2022-11-23
深度學習模型
強化學習實戰 | 自定義Gym環境之掃雷
2022-01-26
強化學習

ElasticSearch7.3學習(二十八)----聚合實戰之電視案例

一、電視案例

1.1 資料準備

1.2 統計哪種顏色的電視銷量最高

1.3 統計每種顏色電視平均價格

1.4 每個顏色下，平均價格及每個顏色下，每個品牌的平均價格

1.5 求出每個顏色的銷售數量，平均價格、最小价格、最大價格、價格總和

1.6 劃分範圍 histogram（直方圖），求出價格每2000為一個區間，每個區間的銷售總額

1.7 按照日期分組聚合，求出每個月銷售個數

1.8 統計每季度每個品牌的銷售額，及每季度的銷售總額

1.9 搜尋與聚合結合，查詢某個品牌按顏色銷量

1.10 global bucket（全域性桶）：單個品牌與所有品牌銷量對比

1.11 統計價格大於1200的電視平均價格

1.12 bucket filter：統計品牌最近4年，3年的平均價格

1.13 按每種顏色的平均銷售額降序排序

1.14 按每種顏色的每種品牌平均銷售額降序排序

相關文章