elasticsearch - Elasticsearch 平均时差聚合查询

Question

我在elasticsearch中有一些文档，其中每个文档如下所示:

{
  "id": "T12890ADSA12",
  "status": "ENDED",
  "type": "SAMPLE",
  "updatedAt": "2020-05-29T18:18:08.483Z",
  "events": [
    {
      "event": "STARTED",
      "version": 1,
      "timestamp": "2020-04-30T13:41:25.862Z"
    },
    {
      "event": "INPROGRESS",
      "version": 2,
      "timestamp": "2020-05-14T17:03:09.137Z"
    },
    {
      "event": "INPROGRESS",
      "version": 3,
      "timestamp": "2020-05-17T17:03:09.137Z"
    },
    {
      "event": "ENDED",
      "version": 4,
      "timestamp": "2020-05-29T18:18:08.483Z"
    }
  ],
  "createdAt": "2020-04-30T13:41:25.862Z"
}

现在，我想在elasticsearch中编写查询以获取所有类型为“SAMPLE”的文档，并且我可以获取所有这些文档的STARTED和ENDED之间的平均时间。例如。平均(2020-05-29T18:18:08.483Z-2020-04-30T13:41:25.862Z，....)。假设STARTED和ENDED事件在事件数组中仅出现一次。有什么办法可以做到吗？

Answer 1

你可以做这样的事情。该查询选择类型为SAMPLE且状态为ENDED的事件(以确保存在ENDED事件)。然后，avg聚合使用脚本来收集STARTED和ENDED时间戳，并减去它们以返回天数:

POST test/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "status.keyword": "ENDED"
          }
        },
        {
          "term": {
            "type.keyword": "SAMPLE"
          }
        }
      ]
    }
  },
  "aggs": {
    "duration": {
      "avg": {
        "script": "Map findEvent(List events, String type) {return events.find(it -> it.event == type);} def started = Instant.parse(findEvent(params._source.events, 'STARTED').timestamp); def ended = Instant.parse(findEvent(params._source.events, 'ENDED').timestamp); return ChronoUnit.DAYS.between(started, ended);"
      }
    }
  }
}

该脚本如下所示:

Map findEvent(List events, String type) {
  return events.find(it -> it.event == type);
}
def started = Instant.parse(findEvent(params._source.events, 'STARTED').timestamp);
def ended = Instant.parse(findEvent(params._source.events, 'ENDED').timestamp); 
return ChronoUnit.DAYS.between(started, ended);