Elasticsearch中每组最大持续时间字段的总和

lmyy7pcs 于 12个月前发布在 ElasticSearch

关注(0)|答案(2)|浏览(113)

我想通过在Elasticsearch中检索每组的最大id后总结duration字段来创建一个可视化工具。举例来说：
数据为：
| ID|工作流|Sid|持续时间|
| --|--|--|--|
| 1 |一|X1| 1m|
| 1 |一|X2| 2m|
| 2 |一|X1| 2m|
| 2 |一|X2| 3M|
| 1 |B| Y1| 1m|
| 1 |B| Y2| 2m|
| 2 |B| Y1| 2m|
| 2 |B| Y2| 3M|
| 3 |B| Y1| 4m|
| 3 |B| Y2| 2m|
给定下表，预期返回的数据如下所示，这是每个工作流的最大id，并总结了持续时间。
| ID|工作流|总|
| --|--|--|
| 2 |一|5m|
| 3 |B| 6m|
我是Elasticsearch query和Kibana的新手。如果你能提供一个如何解决我的问题陈述的指针，我将不胜感激。

{
  "size": 0,
  "aggs": {
    "my-bucket": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "max_id": {
          "max": {
            "field": "id"
          }
        }
      }
    }
  }
}

我有上面的搜索查询与预期的工作流桶和最大ID #。如何使用max id #来检索SID并计算持续时间。

elasticsearch

来源：https://stackoverflow.com/questions/77040435/sum-of-duration-field-of-max-per-group-in-elasticsearch

2条答案

按热度按时间

uxh89sit1#

与其查找max_id，不如按id对所有bucket进行排序，只显示最上面的一个：

DELETE test
PUT test
{
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  },
  "mappings": {
    "properties": {
      "id": {
        "type": "long"
      },
      "duration_min": {
        "type": "integer"
      },
      "sid": {
        "type": "keyword"
      },
      "workflow": {
        "type": "keyword"
      }
    }
  }
}

POST test/_bulk?refresh
{"index":{}}
{"id": 1, "workflow": "A", "sid": "x1", "duration_min": 1}
{"index":{}}
{"id": 1, "workflow": "A", "sid": "x2", "duration_min": 2}
{"index":{}}
{"id": 2, "workflow": "A", "sid": "x1", "duration_min": 2}
{"index":{}}
{"id": 2, "workflow": "A", "sid": "x2", "duration_min": 3}
{"index":{}}
{"id": 1, "workflow": "B", "sid": "y1", "duration_min": 1}
{"index":{}}
{"id": 1, "workflow": "B", "sid": "y2", "duration_min": 2}
{"index":{}}
{"id": 2, "workflow": "B", "sid": "y1", "duration_min": 2}
{"index":{}}
{"id": 2, "workflow": "B", "sid": "y2", "duration_min": 3}
{"index":{}}
{"id": 3, "workflow": "B", "sid": "y1", "duration_min": 4}
{"index":{}}
{"id": 3, "workflow": "B", "sid": "y2", "duration_min": 2}

GET test/_search
{
  "size": 0,
  "aggs": {
    "by_workflow": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "by_id": {
          "terms": {
            "field": "id"
          },
          "aggs": {
            "sids": {
              "terms": {
                "field": "sid"
              }
            },
            "duration_sum": {
              "sum": {
                "field": "duration_min"
              }
            },
            "sales_bucket_sort": {
              "bucket_sort": {
                "sort": [
                  { "_key": { "order": "desc" } } 
                ],
                "size": 1                              
              }
            }
          }
        }
      }
    }
  }
}

赞(0）回复(0）举报 12个月前

vnjpjtjt2#

这是我从Elastic Stack社区学到的另一种方法。

GET test/_search
{
  "size": 0,
  "aggs": {
    "workflow": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "ids": {
          "terms": {
            "field": "id",
            "order": { "max_id": "desc" },
            "size": 1
          },
          "aggs": {
            "max_id": {
              "max": {
                "field": "id"
              }
            },
            "sum_duration": {
              "sum": {
                "field": "duration"
              }
            }
          }
        }
      }
    }
  }
}

赞(0）回复(0）举报 12个月前

我来回答

Elasticsearch中每组最大持续时间字段的总和

2条答案

相关问题

热门标签

最新问答