如何将指标与过去x天同一时段平均值对比？offset函数扩展用法

针对你需要将当日某时段指标平均值与过去X天同一时段平均值对比的需求，以下是不同工具场景下的实现方法，完全可以扩展到一周（X=7）或一个月（X=30）：

1. SQL 实现（通用数据查询场景） #

假设你的数据表包含metric_value（指标数值）、record_time（记录时间戳）字段，核心思路是先按天聚合同期时段的平均值，再计算这些日均值的整体平均。

今日目标时段平均值 #

SELECT AVG(metric_value) AS today_avg
FROM your_table
WHERE record_time BETWEEN CURRENT_DATE + INTERVAL '13 hours' 
                      AND CURRENT_DATE + INTERVAL '14 hours';

过去X天同一时段的平均值 #

SELECT AVG(daily_avg) AS past_x_days_avg
FROM (
    -- 先计算过去X天每天13-14点的平均值
    SELECT AVG(metric_value) AS daily_avg
    FROM your_table
    WHERE record_time >= CURRENT_DATE - INTERVAL 'X days' + INTERVAL '13 hours'
      AND record_time < CURRENT_DATE + INTERVAL '13 hours'
    GROUP BY DATE(record_time) -- 按天分组，确保每天只算一次同期均值
) AS daily_avgs;

合并对比结果 #

SELECT
    (SELECT AVG(metric_value) FROM your_table 
     WHERE record_time BETWEEN CURRENT_DATE + INTERVAL '13 hours' 
                           AND CURRENT_DATE + INTERVAL '14 hours') AS today_avg,
    (SELECT AVG(daily_avg) FROM (
        SELECT AVG(metric_value) AS daily_avg
        FROM your_table
        WHERE record_time >= CURRENT_DATE - INTERVAL 'X days' + INTERVAL '13 hours'
          AND record_time < CURRENT_DATE + INTERVAL '13 hours'
        GROUP BY DATE(record_time)
    ) AS daily_avgs) AS past_x_days_avg;

注意：将X替换为实际天数（如7、30），若涉及多时区，需添加AT TIME ZONE子句处理时区转换。

2. Excel 实现（桌面办公场景） #

假设A列为日期（格式YYYY-MM-DD）、B列为时间（格式HH:MM）、C列为指标值，可通过AVERAGEIFS结合数组公式实现：

今日13-14点平均值 #

=AVERAGEIFS(C:C, A:A, TODAY(), B:B, ">="&TIME(13,0,0), B:B, "<"&TIME(14,0,0))

过去X天同期平均值 #

=AVERAGE(INDEX(AVERAGEIFS(C:C, A:A, TODAY()-ROW(INDIRECT("1:"&X)), B:B, ">="&TIME(13,0,0), B:B, "<"&TIME(14,0,0)),0,1))

注意：将X替换为天数，旧版Excel需按Ctrl+Shift+Enter作为数组公式执行。

3. PromQL 实现（监控指标场景） #

针对Prometheus这类监控系统，利用avg_over_time和offset扩展实现多日同期对比：

今日13-14点平均值（当前时间需在14点之后） #

avg_over_time(metric_name[1h] offset 0h)

过去X天同一时段的平均值 #

如果X较小（如一周），可直接枚举每日偏移：

avg(
  avg_over_time(metric_name[1h] offset 1d)
  or avg_over_time(metric_name[1h] offset 2d)
  or avg_over_time(metric_name[1h] offset 3d)
  or avg_over_time(metric_name[1h] offset 4d)
  or avg_over_time(metric_name[1h] offset 5d)
  or avg_over_time(metric_name[1h] offset 6d)
  or avg_over_time(metric_name[1h] offset 7d)
)

如果X较大（如一个月），用范围聚合简化：

sum_over_time(avg_over_time(metric_name[1h] offset 1d)[30d:1d]) / 30

逻辑：先每天取13-14点的1小时均值，再对过去30天的这些值求和后除以30，得到整体平均。

内容的提问来源于stack exchange，提问作者Farhan Jailani