Elasticsearch多個聚合或術語和同一列上的聚合

我想在 Elasticsearch 中實作 SQL 的功能。考慮下表：

我想要實作的是聚合列的不同值，SQL 等效項：

select product,min(distinct price) from test group by product;

Expected output:

聚集可以是任何標準的總比分一樣min，max，sum，avg，count等。

我已經嘗試過使用無痛腳本，我能夠通過無痛腳本實作所需的輸出，但是以編程方式生成無痛腳本非常困難。

我正在尋找的是可以以編程方式生成的 EQL 查詢或一些可以以編程方式生成無痛腳本的 java 插件。

Edit:

我試過的無痛腳本：

{
  "aggs": {
    "terms": {
      "scripted_metric": {
        "init_script": "state.rawMap = [:];",
        "map_script": "def product = doc['product'].value;state.rawMap.putIfAbsent(product, new ArrayList());def price = doc['price'].value;if(!state.rawMap.get(product).contains(price)){state.rawMap.get(product).add(price);}",
        "combine_script": "List outputList=new ArrayList();for (entry in state.rawMap.entrySet()) {def map=[:]; def min=entry.getValue().get(0); for(price in entry.getValue()){if(price<min){min=price;}}map.product=(entry.getKey()); map.min_price=min;outputList.add(map);}return outputList;"
      }
    }
  }
}

uj5u.com熱心網友回復：

無需無痛腳本！您可以通過以下聚合查詢來簡單實作：

{
  "size": 0,
  "aggs": {
    "products": {
      "terms": {
        "field": "product"
      },
      "aggs": {
        "distinct_price": {
          "terms": {
            "field": "price"
          },
          "aggs": {
            "price_stats": {
              "stats": {
                "field": "price"
              }
            }
          }
        }
      }
    }
  }
}

uj5u.com熱心網友回復：

以下查詢將為您提供預期的結果：

POST test/_search
{
  "size": 0,
  "aggs": {
    "Fruites": {
      "terms": {
        "field": "product.keyword",
        "size": 10
      },
      "aggs": {
        "min_price": {
          "min": {
            "field": "price"
          }
        }
      }
    }
  }
}

示例結果：

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 5,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "Fruites" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "apple",
          "doc_count" : 2,
          "min_price" : {
            "value" : 120.0
          }
        },
        {
          "key" : "pears",
          "doc_count" : 2,
          "min_price" : {
            "value" : 150.0
          }
        },
        {
          "key" : "banana",
          "doc_count" : 1,
          "min_price" : {
            "value" : 70.0
          }
        }
      ]
    }
  }
}

您可以創建如下索引映射：

PUT test
{
  "mappings": {
    "properties": {
      "price": {
        "type": "integer"
      },
      "product": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

示例資料索引如下：

POST _bulk
{"index":{"_index":"test","_id":"1"}}
{"product":"apple","price":"120"}
{"index":{"_index":"test","_id":"2"}}
{"product":"banana","price":"70"}
{"index":{"_index":"test","_id":"3"}}
{"product":"pears","price":"150"}
{"index":{"_index":"test","_id":"4"}}
{"product":"apple","price":"220"}
{"index":{"_index":"test","_id":"5"}}
{"product":"apple","price":"120"}
{"index":{"_index":"test","_id":"5"}}
{"product":"pears","price":"180"}

標籤：爪哇 弹性搜索

上一篇：uuid欄位的ElasticSearch查詢

下一篇：Elasticsearch5.6.16補丁log4j與2.16