如何在Elasticsearch Aggregation中获得最小和最大日期之间的小时数？ [英] How to get hours between Min and Max date in Elasticsearch Aggregation?

问题描述

如何在Elasticsearch中计算最大和最小日期之间的小时数（最大和最小相同的树级别）？

How can I calculate hours between max and min dates (same tree level of max and min) in 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)
                                 //}*
                            }
                        ]
                    }
                }
            ]
        }
    }

有人请帮助我找出解决方案吗？
提前谢谢。

Anyone please help me to find out the solution? Thanks in Advance.

推荐答案

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

You can leverage the bucket_script pipeline aggregation in order to compute the difference between min and max for each bucket.

只需在 minDate 和 maxDate ：

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

对于上面的示例数据，此结果情况将是0.0175（即大约1分钟）

For your sample data above, the result in this case would be 0.0175 (i.e. roughly 1 minute)

这篇关于如何在Elasticsearch Aggregation中获得最小和最大日期之间的小时数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！