ElasticSearch搜索语法进阶学习（搜索+聚合，过滤+聚合）

深藏阁楼爱情的钟 2022-11-22 00:14 271阅读 0赞

# ElasticSearch聚合+搜索语法学习 #

--------------------

## 目录 ##

1.  搜索+聚合：统计指定品牌下每个颜色的销量
2.  global bucket：单个品牌与所有品牌销量对比
3.  过滤+聚合：统计价格大于1200的电视平均价格
4.  bucket filter：统计牌品最近一个月的平均价格
5.  排序：按每种颜色的平均销售额降序排序
6.  颜色+品牌下钻分析时按最深层metric进行排序
7.  cardinality去重算法以及每月销售品牌数量统计
8.  cardinality算法之优化内存开销以及HLL算法

ES数据参考上一篇：[ElasticSearch聚合语法学习（bucket，metric，hitogram，date hitogram）][ElasticSearch_bucket_metric_hitogram_date hitogram]

--------------------

### 1. 搜索+聚合：统计指定品牌下每个颜色的销量 ###

1.  实际上来说，我们之前学习的搜索相关的知识，完全可以和聚合组合起来使用

select count(*)
    from tvs.sales
    where brand like "%长%"
    group by price

1.  es aggregation，scope，任何的聚合，都必须在搜索出来的结果数据中之行，搜索结果，就是聚合分析操作的scope

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

{
        
      "took": 5,
      "timed_out": false,
      "_shards": {
        
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        
        "total": 2,
        "max_score": 0,
        "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
            }
          ]
        }
      }
    }

### 2. global bucket：单个品牌与所有品牌销量对比 ###

1.  aggregation，scope，一个聚合操作，必须在query的搜索结果范围内执行

GET /tvs/sales/_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"
              }
            }
          }
        }
      }
    }

1.  global：就是global bucket，就是将所有数据纳入聚合的scope，而不管之前的query
2.  出来两个结果，一个结果，是基于query搜索结果来聚合的; 一个结果，是对所有数据执行聚合的

{
        
      "took": 4,
      "timed_out": false,
      "_shards": {
        
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        
        "total": 3,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        
        "all": {
        
          "doc_count": 8,
          "all_brand_avg_price": {
        
            "value": 2650
          }
        },
        "single_brand_avg_price": {
        
          "value": 1666.6666666666667
        }
      }
    }

1.  single\_brand\_avg\_price：就是针对query搜索结果，执行的，拿到的，就是长虹品牌的平均价格
2.  all.all\_brand\_avg\_price：拿到所有品牌的平均价格

--------------------

### 3. 过滤+聚合：统计价格大于1200的电视平均价格 ###

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

{
        
      "took": 41,
      "timed_out": false,
      "_shards": {
        
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        
        "total": 7,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        
        "avg_price": {
        
          "value": 2885.714285714286
        }
      }
    }

--------------------

### 4. bucket filter：统计牌品最近一个月的平均价格 ###

GET /tvs/sales/_search 
    {
        
      "size": 0,
      "query": {
        
        "term": {
        
          "brand": {
        
            "value": "长虹"
          }
        }
      },
      "aggs": {
        
        "recent_150d": {
        
          "filter": {
        
            "range": {
        
              "sold_date": {
        
                "gte": "now-150d"
              }
            }
          },
          "aggs": {
        
            "recent_150d_avg_price": {
        
              "avg": {
        
                "field": "price"
              }
            }
          }
        },
        "recent_140d": {
        
          "filter": {
        
            "range": {
        
              "sold_date": {
        
                "gte": "now-140d"
              }
            }
          },
          "aggs": {
        
            "recent_140d_avg_price": {
        
              "avg": {
        
                "field": "price"
              }
            }
          }
        },
        "recent_130d": {
        
          "filter": {
        
            "range": {
        
              "sold_date": {
        
                "gte": "now-130d"
              }
            }
          },
          "aggs": {
        
            "recent_130d_avg_price": {
        
              "avg": {
        
                "field": "price"
              }
            }
          }
        }
      }
    }

1.  aggs.filter，针对的是聚合去做的
2.  如果放query里面的filter，是全局的，会对所有的数据都有影响
3.  但是，如果，比如说，你要统计，长虹电视，最近1个月的平均值; 最近3个月的平均值; 最近6个月的平均值
4.  bucket filter：对不同的bucket下的aggs，进行filter

--------------------

### 5. 排序：按每种颜色的平均销售额降序排序 ###

1.  之前的话，排序，是按照每个bucket的doc\_count降序来排的
2.  但是假如说，我们现在统计出来每个颜色的电视的销售额，需要按照销售额降序排序？？？？

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

{
        
      "took": 2,
      "timed_out": false,
      "_shards": {
        
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        
        "total": 8,
        "max_score": 0,
        "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
              }
            },
            {
        
              "key": "绿色",
              "doc_count": 2,
              "avg_price": {
        
                "value": 2100
              }
            },
            {
        
              "key": "蓝色",
              "doc_count": 2,
              "avg_price": {
        
                "value": 2000
              }
            }
          ]
        }
      }
    }

GET /tvs/sales/_search 
    {
        
      "size": 0,
      "aggs": {
        
        "group_by_color": {
        
          "terms": {
        
            "field": "color",
            "order": {
        
              "avg_price": "asc"
            }
          },
          "aggs": {
        
            "avg_price": {
        
              "avg": {
        
                "field": "price"
              }
            }
          }
        }
      }
    }

--------------------

### 6. 颜色+品牌下钻分析时按最深层metric进行排序 ###

GET /tvs/sales/_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"
                  }
                }
              }
            }
          }
        }
      }
    }

--------------------

### 7. cardinality去重算法以及每月销售品牌数量统计 ###

1.  去重，cartinality metric，对每个bucket中的指定的field进行去重，取去重后的count，类似于count(distcint)

GET /tvs/sales/_search
    {
        
      "size" : 0,
      "aggs" : {
        
          "months" : {
        
            "date_histogram": {
        
              "field": "sold_date",
              "interval": "month"
            },
            "aggs": {
        
              "distinct_colors" : {
        
                  "cardinality" : {
        
                    "field" : "brand"
                  }
              }
            }
          }
      }
    }

{
        
      "took": 70,
      "timed_out": false,
      "_shards": {
        
        "total": 5,
        "successful": 5,
        "failed": 0
      },
      "hits": {
        
        "total": 8,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        
        "group_by_sold_date": {
        
          "buckets": [
            {
        
              "key_as_string": "2016-05-01T00:00:00.000Z",
              "key": 1462060800000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2016-06-01T00:00:00.000Z",
              "key": 1464739200000,
              "doc_count": 0,
              "distinct_brand_cnt": {
        
                "value": 0
              }
            },
            {
        
              "key_as_string": "2016-07-01T00:00:00.000Z",
              "key": 1467331200000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2016-08-01T00:00:00.000Z",
              "key": 1470009600000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2016-09-01T00:00:00.000Z",
              "key": 1472688000000,
              "doc_count": 0,
              "distinct_brand_cnt": {
        
                "value": 0
              }
            },
            {
        
              "key_as_string": "2016-10-01T00:00:00.000Z",
              "key": 1475280000000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2016-11-01T00:00:00.000Z",
              "key": 1477958400000,
              "doc_count": 2,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2016-12-01T00:00:00.000Z",
              "key": 1480550400000,
              "doc_count": 0,
              "distinct_brand_cnt": {
        
                "value": 0
              }
            },
            {
        
              "key_as_string": "2017-01-01T00:00:00.000Z",
              "key": 1483228800000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            },
            {
        
              "key_as_string": "2017-02-01T00:00:00.000Z",
              "key": 1485907200000,
              "doc_count": 1,
              "distinct_brand_cnt": {
        
                "value": 1
              }
            }
          ]
        }
      }
    }

### 8. cardinality算法之优化内存开销以及HLL算法 ###

##### 1. cardinality解析 #####

1.  cardinality，count(distinct)，5%的错误率，性能在100ms左右
2.  precision\_threshold优化准确率和内存开销

GET /tvs/sales/_search
    {
        
        "size" : 0,
        "aggs" : {
        
            "distinct_brand" : {
        
                "cardinality" : {
        
                  "field" : "brand",
                  "precision_threshold" : 100 
                }
            }
        }
    }

1.  brand去重，如果brand的unique value，在100个以内，小米，长虹，三星，TCL，HTL…
2.  在多少个unique value以内，cardinality，几乎保证100%准确
3.  cardinality算法，会占用precision\_threshold \* 8 byte 内存消耗，100 \* 8 = 800个字节，占用内存很小，而且unique value如果的确在值以内，那么可以确保100%准确
4.  100，数百万的unique value，错误率在5%以内
5.  precision\_threshold，值设置的越大，占用内存越大，1000 \* 8 = 8000 / 1000 = 8KB，可以确保更多unique value的场景下，100%的准确
6.  field，去重，count，这时候，unique value，10000，precision\_threshold=10000，10000 \* 8 = 80000个byte，80KB

##### 2. HyperLogLog++ (HLL)算法性能优化 #####

1.  cardinality底层算法：HLL算法，HLL算法的性能
2.  会对所有的uqniue value取hash值，通过hash值近似去求distcint count，误差
3.  默认情况下，发送一个cardinality请求的时候，会动态地对所有的field value，取hash值; 将取hash值的操作，前移到建立索引的时候

PUT /tvs/
    {
        
      "mappings": {
        
        "sales": {
        
          "properties": {
        
            "brand": {
        
              "type": "text",
              "fields": {
        
                "hash": {
        
                  "type": "murmur3" 
                }
              }
            }
          }
        }
      }
    }

GET /tvs/sales/_search
    {
        
        "size" : 0,
        "aggs" : {
        
            "distinct_brand" : {
        
                "cardinality" : {
        
                  "field" : "brand.hash",
                  "precision_threshold" : 100 
                }
            }
        }
    }

[ElasticSearch_bucket_metric_hitogram_date hitogram]: https://blog.csdn.net/weixin_41910694/article/details/109444961