具有 group by 和 where 条件的弹性搜索总和聚合 [英] Elastic Search Sum aggregation with group by and where condition

问题描述

我是 ElasticSearch 的新手.

I am newbie in ElasticSearch.

我们目前正在将代码从关系数据库迁移到 ElasticSearch.因此，我们将查询转换为 ElasticSearch 查询格式.

We are currently moving our code from relational DB to ElasticSearch. So we are converting our queries in ElasticSearch query format.

我正在寻找等效于以下查询的 ElasticSearch -

I am looking for ElasticSearch equivalent of below query -

SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL 
    AND ListPrice != 0.00 
    AND Name LIKE 'Mountain%'
GROUP BY Color

谁能给我提供上面的 ElasticSearch 查询示例?

Can someone provide me the example of ElasticSearch query for above?

推荐答案

您将拥有一个 products 索引，其中包含一个 product 类型的文档，其映射看起来像这样基于关于您上面的查询:

You'd have a products index with a product type documents whose mapping could look like this based on your query above:

curl -XPUT localhost:9200/products -d '
{
  "mappings": {
    "product": {
      "properties": {
        "Color": {
          "type": "string"
        },
        "Name": {
          "type": "string"
        },
        "ListPrice": {
          "type": "double"
        },
        "StandardCost": {
          "type": "double"
        }
      }
    }
  }
}'

那么与您上面给出的 SQL 等效的 ES 查询将如下所示:

Then the ES query equivalent to the SQL one you gave above would look like this:

{
  "query": {
    "filtered": {
      "query": {
        "query_string": {
          "default_field": "Name",
          "query": "Mountain*"
        }
      },
      "filter": {
        "bool": {
          "must_not": [
            {
              "missing": {
                "field": "Color"
              }
            },
            {
              "term": {
                "ListPrice": 0
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "by_color": {
      "terms": {
        "field": "Color"
      },
      "aggs": {
        "total_price": {
          "sum": {
            "field": "ListPrice"
          }
        },
        "total_cost": {
          "sum": {
            "field": "StandardCost"
          }
        }
      }
    }
  }
}

这篇关于具有 group by 和 where 条件的弹性搜索总和聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！