如何获取过去X天逐小时平均数据以对比当日数据？

现有逐小时聚合脚本 #

以下脚本可实现过去指定天数内ExecNom字段的逐小时聚合，返回每个小时段的ExecNom平均值：

GET /mkt-with-time/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-7d/d",
        "lte": "now-2d/d"
      }
   }
  },
  "aggs": {
        "per_hour": {
            "date_histogram": {
                "field": "@timestamp",
                "calendar_interval": "hour"
            },
            "aggs": {
                "avg_execnom": {
                    "avg": {
                        "field": "ExecNom"
                    }
                }
            }
        }
    }
}

需求说明 #

已获取当日逐小时数据，需要计算过去X天内每日同一小时的平均值集合，用于与当日数据逐小时对比。最终需得到00点至23点各时段的平均值，格式如下：

[
    avg(2022-08-14T00:00:00时段平均值, 2022-08-15T00:00:00时段平均值, ...),
    avg(2022-08-14T01:00:00时段平均值, 2022-08-15T01:00:00时段平均值, ...),
    ...
    avg(2022-08-14T23:00:00时段平均值, 2022-08-15T23:00:00时段平均值, ...)
]

尝试过的失败脚本 #

以下是尝试但未成功的脚本：

GET /mkt-with-time/_search
{
  "size": 0,
  "aggs": {
    "orders_per_hour_of_day": {
      "terms": {
        "script" : {
            "lang": "painless",
            "source": "doc['@timestamp'].value.getHour()"
        },
        "size": 24
      },
      "aggs": {
        "dayOfWeek": {
          "terms": {
            "script": {
              "lang": "painless",
              "source": "doc['@timestamp'].value.getDayOfWeekEnum()"
            }
          },
          "aggs": {
            "total_execnom": {
              "sum": {
                "field": "ExecNom"
              }
            },
            "number_of_weeks": {
              "date_histogram": {
                "field": "@timestamp",
                "calendar_interval": "week"
              }
            },
            "average_orders_per_hour_day_of_week": {
              "bucket_selector": {
                "buckets_path": {
                  "totalExecNom" : "total_execnom",
                  "number_of_weeks": "number_of_weeks._bucket_count"
                },
                "script": "params.totalExecNom / params.number_of_weeks"
              }
            }
          }
        }
      }
    }
  }
}

可行解决方案 #

使用嵌套聚合先计算逐小时平均值，再按一天中的小时数分组求平均，脚本如下：

GET /mkt-with-time/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-7d/d",
        "lte": "now-2d/d"
      }
    }
  },
  "aggs": {
    // 第一步：计算每个小时段的ExecNom平均值
    "per_hour": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "hour"
      },
      "aggs": {
        "avg_execnom": {
          "avg": {
            "field": "ExecNom"
          }
        }
      }
    },
    // 第二步：按一天中的小时数分组，对同小时的所有时段平均值再求平均
    "hour_of_day_avg": {
      "terms": {
        "script": {
          "lang": "painless",
          "source": "doc['@timestamp'].value.getHour()"
        },
        "size": 24,
        "order": {
          "_key": "asc"
        }
      },
      "aggs": {
        "avg_of_hourly_avgs": {
          "avg_bucket": {
            "buckets_path": "per_hour>avg_execnom"
          }
        }
      }
    }
  }
}

脚本说明 #

1. 时间过滤：通过range查询限定统计的时间范围，示例中为过去7天至2天前的数据，可根据需求调整gte和lte参数。
2. 逐小时聚合：per_hour聚合拆分所有时间范围内的数据为小时级时段，计算每个时段的ExecNom平均值，与原脚本逻辑一致。
3. 同小时分组求平均：hour_of_day_avg聚合按一天中的小时数（0-23）分组，使用avg_bucket对每个小时组下的所有时段平均值再计算一次平均，得到该小时在过去X天内的整体平均。
4. 排序：通过order: { "_key": "asc" }保证结果按0到23小时的顺序返回，直接对应当日逐小时数据的对比顺序。

内容的提问来源于stack exchange，提问作者yi.han