elasticsearch - 如何扩展 Elasticsearch 日期范围直方图聚合查询？

Question

嗨，我有一个名为mep-report的 Elasticsearch 索引。

每个文档都有一个状态字段。状态字段的可能值为“ENROUTE”，“SUBMITTED”，“DELIVERED”，“FAILED”。以下是包含6个文档的示例 Elasticsearch 索引。

{
  "took" : 10,
  "timed_out" : false,
  "_shards" : {
    "total" : 13,
    "successful" : 13,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1094313,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837500",
        "_score" : 1.0,
        "_source" : {
          "status" : "ENROUTE",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        },
       {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837501",
        "_score" : 1.0,
        "_source" : {
          "status" : "ENROUTE",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        },
      {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837502",
        "_score" : 1.0,
        "_source" : {
          "status" : "SUBMITTED",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        }
      },
      {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837503",
        "_score" : 1.0,
        "_source" : {
          "status" : "DELIVERED",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        }
      },
      {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837504",
        "_score" : 1.0,
        "_source" : {
          "status" : "FAILED",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        },
      {
        "_index" : "mep-reports-2019.09.11",
        "_type" : "doc",
        "_id" : "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837504",
        "_score" : 1.0,
        "_source" : {
          "status" : "FAILED",
          "@timestamp" : "2019-09-11T10:21:26.000Z"
        }
      }
}

我想找到一个聚合直方图分布，类似于获取messages_processed，message_delivered，messages_failed。

messages_processed : 3 ( 2 documents in status ENROUTE + 1 Document with status SUBMITTED ) 
message_delivered  1  ( 1 document with status DELIVERED )
messages_failed : 2   ( 2 documents with status FAILED ) 

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 13,
    "successful" : 13,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 21300,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "performance_over_time" : {
      "buckets" : [
        {
          "key_as_string" : "2020-02-21",
          "key" : 1582243200000,
          "doc_count" : 6,
          "message_processed": 3,
          "message_delivered": 1,
          "message_failed": 2
        }
      ]
    }
  }
}

So the following is my current query  and i would like to modify it to get some additional statistics such as message_processed , message_delivered, message_failed.  kindly let me know .


{ "size": 0, "query": { "bool": { "must": [ { "range": { "@timestamp": { "from": "2020-02-21T00:00Z", "to": "2020-02-21T23:59:59.999Z", "include_lower": true, "include_upper": true, "format": "yyyy-MM-dd'T'HH:mm:ss.SSSZ ||yyyy-MM-dd'T'HH:mmZ", "boost": 1.0 } } } ], "adjust_pure_negative": true, "boost": 1.0 } }, "aggregations": { "performance_over_time": { "date_histogram": { "field": "@timestamp", "format": "yyyy-MM-dd", "interval": "1d", "offset": 0, "order": { "_key": "asc" }, "keyed": false, "min_doc_count": 0 } } } }

Answer 1

您几乎可以使用查询了，只需要添加Terms Aggregation并查看您的请求，我就提出了Scripted Terms Aggregation。

我还已将date histogram聚合字段interval修改为calendar_interval，以便您获得日历日期的值。

查询请求:

POST <your_index_name>/_search
{  
  "size": 0,
  "query":{
    "bool":{
      "must":[
        {
         "range":{
            "@timestamp":{
               "from":"2019-09-10",
               "to":"2019-09-12",
               "include_lower":true,
               "include_upper":true,
               "boost":1.0
            }
         }
      }
      ],
      "adjust_pure_negative":true,
      "boost":1.0
    }
  },
  "aggs":{
    "message_processed":{
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "1d"                       <----- Note this
      },
      "aggs": {
        "my_messages": {
          "terms": {
            "script": {                                 <----- Core Logic of Terms Agg
              "source": """
                if(doc['status'].value=="ENROUTE" || doc['status'].value == "SUBMITTED"){
                  return "message_processed";
                }else if(doc['status'].value=="DELIVERED"){
                  return "message_delivered"
                }else {
                  return "message_failed"
                }
                """,
              "lang": "painless"
            }, 
            "size": 10
          }
        }
      }
    }
  }
}

请注意，您要查找的核心逻辑在脚本术语聚合中。如果您通过逻辑，则逻辑是可以自我解释的。随时修改适合您的逻辑。

对于您共享的样本日期，您将获得以下格式的结果:

响应:

{
  "took" : 144,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "message_processed" : {
      "buckets" : [
        {
          "key_as_string" : "2019-09-11T00:00:00.000Z",
          "key" : 1568160000000,
          "doc_count" : 6,
          "my_messages" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "message_processed",
                "doc_count" : 3
              },
              {
                "key" : "message_failed",
                "doc_count" : 2
              },
              {
                "key" : "message_delivered",
                "doc_count" : 1
              }
            ]
          }
        }
      ]
    }
  }
}