如何编写计算conc滚动24小时平均值的SQL查询用于Grafana可视化

你之前直接调用avg()结果和原始conc值一致，是因为没有使用滑动窗口函数定义24小时的移动计算范围，普通聚合的avg()仅会按group by的分组逐组计算，不会包含历史24小时的全量数据。

PostgreSQL/TimescaleDB 适配写法（Grafana时序场景最常用） #

SELECT
  -- 1m为你要展示的时间粒度，可按需调整为5m/1h等
  $__timeGroupAlias(time, '1m'),
  AVG(conc) OVER (
    ORDER BY time 
    RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
  ) AS conc_24h_avg
FROM 你的业务表名
-- Grafana内置宏，自动同步仪表盘的时间选择范围
WHERE $__timeFilter(time)
ORDER BY time

如果使用TimescaleDB，可将$__timeGroupAlias(time, '1m')替换为time_bucket('1m', time) AS time，查询性能更优。

MySQL 8.0+ 适配写法 #

WITH minute_agg AS (
  -- 先按1分钟粒度聚合原始数据，避免数据过密影响性能
  SELECT
    DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') AS time,
    AVG(conc) AS minute_conc
  FROM 你的业务表名
  WHERE time BETWEEN $__timeFrom() AND $__timeTo()
  GROUP BY 1
)
SELECT
  time,
  AVG(minute_conc) OVER (
    ORDER BY UNIX_TIMESTAMP(time) 
    -- 86400为24小时对应的秒数
    RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW
  ) AS conc_24h_avg
FROM minute_agg
ORDER BY time

注意事项 #

• 可自行调整时间聚合粒度，比如需要按小时展示滚动平均值，将聚合逻辑里的1m替换为1h即可
• 若需要过滤掉窗口不满24小时的结果，可以在最终查询中加条件time >= (SELECT MIN(time) + INTERVAL 24 HOUR FROM 你的业务表名)
• 若原始数据时间间隔均匀，可省略预聚合步骤直接对原始数据开窗口计算

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