elasticsearch - 在Elasticsearch索引中选择两个属性之间的平均时间

Question

给定带有两个日期字段的elasticsearch索引，我如何查询这两个日期之间的平均时间？

{
    "mappings": {
        "properties": {
            "dateInserted": {
                "type": "date",
                "format": "dateOptionalTime"
            },
            "dateUpdated": {
                "type": "date",
                "format": "dateOptionalTime"
            }
        }
    }
}

[
    {
        "dateInserted": "2020-10-01 00:00:00",
        "dateUpdated": "2020-10-01 01:00:00"
    }, {
        "dateInserted": "2020-10-01 02:00:00",
        "dateUpdated": "2020-10-01 04:00:00"
    }
]

文档A的间隔为一小时，文档B的间隔为两小时，因此预期结果为1.5小时。

Answer 1

avg aggregation脚本应该可以:

{
  "size": 0,
  "aggs": {
    "avg_duration": {
      "avg": {
        "script": {
          "source": """
            def duration = doc.dateUpdated.value.getMillis() - doc.dateInserted.value.getMillis();
            return duration / (float) (1000*60*60)
          """
        }
      }
    }
  }
}

或者，更紧凑:

{
 ...
 "source": "def duration = doc.dateUpdated.value.getMillis() - doc.dateInserted.value.getMillis(); return duration / (float) (1000*60*60)"
 ...
}