如何编写Elasticsearch查询获取逐日递进的连续时间区间数据

我需要获取按每日递增方式推进的多时间区间数据，比如1月内的7天滑动窗口：1-7日、2-8日、3-9日……直到月末，但目前用date_histogram按周聚合返回的是1-7日、8-15日这种非连续的区间，请问在Elasticsearch中能否实现预期的滑动窗口效果？该怎么写查询？

我的尝试代码 #

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "associated_datetime": {
              "gte": "14/12/2021 19:31:56",
              "lte": "14/12/2022 19:31:56",
              "format": "dd/MM/yyyy HH:mm:ss"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "incident": {
      "date_histogram": {
        "field": "associated_datetime",
        "calendar_interval": "week"
      },
      "aggs": {
        "associated_to.id": {
          "terms": {
            "size": 10000,
            "field": "associated_to.id"
          }
        }
      }
    }
  }
}

当前查询输出结果 #

"aggregations": {
  "incident": {
    "buckets": [
      {
        "key_as_string": "2022-01-03T00:00:00.000Z",
        "key": 1641168000000,
        "doc_count": 2,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 4,
              "doc_count": 2
            }
          ]
        }
      },
      {
        "key_as_string": "2022-01-10T00:00:00.000Z",
        "key": 1641772800000,
        "doc_count": 1,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 2,
              "doc_count": 1
            }
          ]
        }
      },
      {
        "key_as_string": "2022-01-17T00:00:00.000Z",
        "key": 1642377600000,
        "doc_count": 1,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 2,
              "doc_count": 1
            }
          ]
        }
      },
      {
        "key_as_string": "2022-03-07T00:00:00.000Z",
        "key": 1646611200000,
        "doc_count": 1,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 4,
              "doc_count": 1
            }
          ]
        }
      },
      {
        "key_as_string": "2022-03-21T00:00:00.000Z",
        "key": 1647820800000,
        "doc_count": 7,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 37,
              "doc_count": 2
            },
            {
              "key": 38,
              "doc_count": 2
            },
            {
              "key": 39,
              "doc_count": 2
            },
            {
              "key": 40,
              "doc_count": 1
            }
          ]
        }
      },
      {
        "key_as_string": "2022-05-16T00:00:00.000Z",
        "key": 1652659200000,
        "doc_count": 1,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 4,
              "doc_count": 1
            }
          ]
        }
      },
      {
        "key_as_string": "2022-11-14T00:00:00.000Z",
        "key": 1668384000000,
        "doc_count": 3,
        "associated_to.id": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": 2,
              "doc_count": 2
            },
            {
              "key": 37,
              "doc_count": 1
            },
            {
              "key": 38,
              "doc_count": 1
            },
            {
              "key": 39,
              "doc_count": 1
            },
            {
              "key": 40,
              "doc_count": 1
            },
            {
              "key": 41,
              "doc_count": 1
            },
            {
              "key": 42,
              "doc_count": 1
            }
          ]
        }
      }
    ]
  }
}

解决方案：使用date_range聚合实现滑动窗口 #

date_histogram是非重叠固定间隔的聚合，无法直接实现滑动窗口。要得到逐日递进的7天窗口，需要用date_range聚合配合脚本动态生成每个窗口的起止时间。

示例查询代码 #

以下查询会生成从指定起始日开始，每天一个7天滑动窗口，直到结束日：

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "associated_datetime": {
              "gte": "2022-01-01T00:00:00Z",
              "lte": "2022-01-31T23:59:59Z"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "sliding_7day_windows": {
      "date_range": {
        "field": "associated_datetime",
        "ranges": {
          "script": {
            "source": """
              def startDate = ZonedDateTime.parse('2022-01-01T00:00:00Z');
              def endDate = ZonedDateTime.parse('2022-01-31T23:59:59Z');
              def windows = [];
              def current = startDate;
              while (current.plusDays(6).isBeforeOrEqual(endDate)) {
                windows.add(['from': current, 'to': current.plusDays(6), 'key': current.format(DateTimeFormatter.ISO_LOCAL_DATE)]);
                current = current.plusDays(1);
              }
              return windows;
            """
          }
        }
      },
      "aggs": {
        "associated_to.id": {
          "terms": {
            "size": 10000,
            "field": "associated_to.id"
          }
        }
      }
    }
  }
}

关键说明 #

1. 脚本逻辑：

   • 定义窗口的起始和结束日期（这里是2022年1月）
   • 循环生成每个滑动窗口：从起始日开始，每天生成一个窗口，窗口的from是当前日期，to是当前日期+6天（构成7天区间）
   • 当窗口的结束日期超过目标月末时停止循环

2. 注意事项：

   • 确保associated_datetime字段是date类型，且时区设置正确
   • 若需要跨多个月份，可调整脚本中的起始/结束日期，或动态计算月份范围
   • 数据量较大时，建议限制时间范围，避免生成过多窗口影响性能

内容的提问来源于stack exchange，提问作者Derek Fields