如何在Elasticsearch中计算最大和最小日期之间的小时数(最大和最小相同树级别)?
我的查询:
{ "size": 0, "query": { "bool": { "must": [] } }, "aggs": { "group_by_areaId": { "terms": { "size": 100000, "field": "areaId.keyword" }, "aggs": { "4m": { "date_histogram": { "field": "timestamp", "format": "yyyy-MM-dd'T'HH:mm:ssZZ", "interval": "4m", "order": { "_key": "asc" } }, "aggs": { "maxDate": { "max": { "field": "timestamp" } }, "minDate": { "min": { "field": "timestamp" } } } } } } } }
响应(简短)为
"aggregations": { "group_by_areaId": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "key1", "doc_count": 15, "4m": { "buckets": [ { "key_as_string": "2020-02-12T06:08:00+0000", "key": 1581487680000, "doc_count": 3, "minDate": { "value": 1.581487847E12, "value_as_string": "2020-02-12T06:10:47Z" }, "maxDate": { "value": 1.58148791E12, "value_as_string": "2020-02-12T06:11:50Z" }, *// Need hours between maxDate and minDate here //{ // "hours" : "0.0175" (maxDate-minDate) //}* } ] } } ] } }
任何人都可以帮助我找出解决方案吗?提前致谢。
您可以利用bucket_script管道聚合来计算每个存储区的最小值和最大值之间的差异。
bucket_script
只需在同一级别添加以下minDate和maxDate:
minDate
maxDate
"hours": { "bucket_script": { "buckets_path": { "min": "minDate", "max": "maxDate" }, "script": "(params.max - params.min) / 3600000" } }
对于上面的示例数据,在这种情况下,结果将为0.0175(即大约1分钟)