一尘不染

如何在Elasticsearch Aggregation中获得最小和最大日期之间的小时数?

elasticsearch

如何在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)
                                 //}*
                            }
                        ]
                    }
                }
            ]
        }
    }

任何人都可以帮助我找出解决方案吗?提前致谢。


阅读 500

收藏
2020-06-22

共1个答案

一尘不染

您可以利用bucket_script管道聚合来计算每个存储区的最小值和最大值之间的差异。

只需在同一级别添加以下minDatemaxDate

            "hours": {
                "bucket_script": {
                    "buckets_path": {
                      "min": "minDate",
                      "max": "maxDate"
                    },
                    "script": "(params.max - params.min) / 3600000"
                }
            }

对于上面的示例数据,在这种情况下,结果将为0.0175(即大约1分钟)

2020-06-22