elasticsearch - 如何计算 Elasticsearch 聚合每个步骤的平均天数？

Question

我正在使用 Elasticsearch 7.5。

我有一个看起来像这样的 Elasticsearch 映射:

{
...
 "assigned_on" : { "type" : "date" },
 "collection_complete" : { "type" : "date" },
 "correct_complete" : { "type" : "date" },
 "production_complete" : { "type" : "date" },
 "complete_on" : { "type" : "date" }
...
}

每个属性都是在流程中的该步骤完成时设置的日期。所以 assigned_on表示进程开始， collection_complete将标志着该过程第一步的结束。

因此，每个日期之间的差异是该步骤发生时耗时。例如， collection_complete 之间的差异和 correct_complete是该特定文档的“正确”步骤所花费的时间。

这是我目前尝试过的:

{
  "query" : {"match_all" : {}},
        "aggs" : {
            "average_step_durations" : {
                "aggs" : {
                    "collection_average_duration" : {
                        "avg" : { "script" : "doc['collection_complete'] - doc['assigned_on']" }
                    },
                    "correction_average_duration" : {
                        "avg" : { "script" : "doc['correct_complete'] - doc['collection_complete']" }
                    },
                    "production_average_duration" : {
                        "avg" : { "script" : "doc['production_complete'] - doc['correct_complete']" }
                    }
                }
            }
        }
}

如何汇总每个步骤所需的平均天数？ 这是我希望我的输出看起来像的一个例子:

   "_shards": ...,
   "hits": ...,
   "aggregations": {
      "average_step_durations": {
         "collection_stage" : 23.45,
         "correct_stage" : 3.89,
         "production_stage" : 4.51 // All these values would be in unit of days. 
      }
   }
}

Answer 1

bucket_script 将在这里派上用场。

另外，请注意:

avg(col1 - col2) = avg(col1) - avg(col2)

让我们一步一步来。

映射:

PUT /process_times
{
  "mappings": {
    "properties": {
      "step1": {
        "type": "date"
      },
      "step2": {
        "type": "date"
      }
    }
  }
}

在那里放一些虚拟值:

POST /process_times/_doc?refresh=wait_for
{
    "step1" : 0,
    "step2" : 120
}

值:(10,20)，(20,20)，(0,120)

询问:

POST /process_times/_search?filter_path=aggregations
{
  "size": 0,
  "aggs": {
    "result": {
      "terms": {
        "script":"'Hack, as only sibling pipeline aggregations are allowed at the top level'"
      },
      "aggs": {
        "avg_step1": {
          "avg": {
            "field": "step1"
          }
        },
        "avg_step2": {
          "avg": {
            "field": "step2"
          }
        },
        "avg_s2_to_s1": {
          "bucket_script": {
            "buckets_path": {
              "step1Avg": "avg_step1",
              "step2Avg": "avg_step2"
            },
            "script": "params.step2Avg - params.step1Avg"
          }
        }
      }
    }
  }
}

输出:

{
  "aggregations" : {
    "result" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Hack, as only sibling pipeline aggregations are allowed at the top level",
          "doc_count" : 3,
          "avg_step2" : {
            "value" : 53.333333333333336,
            "value_as_string" : "1970-01-01T00:00:00.053Z"
          },
          "avg_step1" : {
            "value" : 10.0,
            "value_as_string" : "1970-01-01T00:00:00.010Z"
          },
          "avg_s2_to_s1" : {
            "value" : 43.333333333333336
          }
        }
      ]
    }
  }
}

您可以迭代流程集合中的多个步骤。