时间戳

首页 > 解决方案 > 具有过滤和聚合的 Elasricsearch 查询

elasticsearch - 具有过滤和聚合的 Elasricsearch 查询

问题描述

我有一个包含简单汽车广告数据的 Elasticsearch 索引。我做了一个映射：

"mappings": {
"properties": {
  "brand": {"type": "keyword"},
  "model": {"type": "keyword"},
  "year of production": {"type": "integer"},
  "mileage": {"type": "integer"},
  "color": {"type": "keyword"},
  "engine capacity": {"type": "double"},
  "horse power": {"type": "integer"}, 
  "type of fuel": {"type": "keyword"},
  "condition": {"type": "keyword"},
  "accident history": {"type": "boolean"},
  "price": {"type": "integer"}
  }
}

我想创建两个查询：

  1. 显示 2010 年至 2012 年间制造的二手车的平均里程
  2. 显示 2005 年至 2007 年期间制造的里程超过 200000 的最受欢迎的汽车模型

我尝试创建两者，这是我尝试第一个查询的结果：

GET /gielda/_search?pretty
{ 
  "query": {
    "match": {
      "stan": "Used"
  }
}, 
"aggs": {
  "production_ranges": {
    "range": {
      "field": "year of production",
      "ranges": [
        {
          "from": 2010,
          "to": 2012
        }
      ]
    },
    "aggs": {
      "avg_mileage": {
        "avg": {
          "field": "mileage"
        }
      }
    }
  }
 }
}

我发现这个查询不能正常工作。我在 Elastic docs 和 Stack 上寻找答案，但我仍然不知道如何将聚合与过滤器或范围混合。

标签： elasticsearch

解决方案

您需要使用range、filter、avg和top_hits聚合的组合

添加带有索引数据、搜索查询和搜索结果的工作示例

指数数据：

{
  "mileage": 10,
  "year of production": 2011
}
{
  "mileage": 30,
  "year of production": 2012
}
{
  "mileage": 20,
  "year of production": 2013
}
{
  "mileage": 200001,
  "year of production": 2005,
  "model": "ABC"
}
{
  "mileage": 400000,
  "year of production": 2006,
  "model": "DEF"
}
{
  "mileage": 400000,
  "year of production": 2008,
  "model": "GHI"
}

搜索查询：

{
  "size": 0,
  "aggs": {
    "production_ranges": {
      "range": {
        "field": "year of production",
        "ranges": [
          {
            "from": 2010,
            "to": 2013
          }
        ]
      },
      "aggs": {
        "avg_mileage": {
          "avg": {
            "field": "mileage"
          }
        }
      }
    },
    "Popular_car_model": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "year of production": {
                  "gte": 2005,
                  "lte": 2007
                }
              }
            },
            {
              "range": {
                "mileage": {
                  "gte": 200000
                }
              }
            }
          ]
        }
      },
      "aggs": {
        "top_sales_hits": {
          "top_hits": {
            "sort": [
              {
                "mileage": {
                  "order": "desc"
                }
              }
            ],
            "_source": {
              "includes": [
                "model",
                "mileage",
                "year of production"
              ]
            },
            "size": 1
          }
        }
      }
    }
  }
}

搜索结果：

"aggregations": {
    "Popular_car_model": {
      "doc_count": 2,
      "top_sales_hits": {
        "hits": {
          "total": {
            "value": 2,
            "relation": "eq"
          },
          "max_score": null,
          "hits": [
            {
              "_index": "67650682",
              "_type": "_doc",
              "_id": "5",
              "_score": null,
              "_source": {
                "year of production": 2006,
                "model": "DEF",
                "mileage": 400000
              },
              "sort": [
                400000
              ]
            }
          ]
        }
      }
    },
    "production_ranges": {
      "buckets": [
        {
          "key": "2010.0-2013.0",
          "from": 2010.0,
          "to": 2013.0,
          "doc_count": 2,
          "avg_mileage": {
            "value": 20.0
          }
        }
      ]
    }
  }

推荐阅读