如何使用SQL计算动态月均值、对比当日均值并实现区间阈值告警

以下方案兼容MySQL 8.0+/PostgreSQL等支持窗口函数的主流数据库，可根据实际业务表结构调整字段、表名后直接使用。

假设你存储监控指标的业务表名为metrics_daily，核心字段如下：

• stat_date DATE 统计日期
• ind_value FLOAT 待监控的指标数值（如请求量、成功率、响应耗时等）

1. 动态滚动月均值计算 #

滚动窗口规则匹配你要求的逻辑：当日为T日时，取T-30日到T-1日的区间数据计算均值，每日自动更新窗口范围。

窗口函数实现（推荐） #

WITH daily_agg AS (
    -- 日维度聚合指标，若表本身已是日维度聚合结果可跳过该层
    SELECT 
        stat_date,
        AVG(ind_value) AS daily_avg
    FROM metrics_daily
    GROUP BY stat_date
),
rolling_cal AS (
    SELECT
        stat_date,
        daily_avg,
        -- 窗口函数取当前行前30天的均值
        AVG(daily_avg) OVER (
            ORDER BY stat_date 
            ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
        ) AS rolling_month_avg
    FROM daily_agg
)
SELECT * FROM rolling_cal;

低版本数据库兼容实现（无窗口函数可用） #

SELECT 
    t1.stat_date,
    t1.daily_avg,
    AVG(t2.daily_avg) AS rolling_month_avg
FROM (
    SELECT stat_date, AVG(ind_value) AS daily_avg FROM metrics_daily GROUP BY stat_date
) t1
LEFT JOIN (
    SELECT stat_date, AVG(ind_value) AS daily_avg FROM metrics_daily GROUP BY stat_date
) t2 ON t2.stat_date BETWEEN DATE_SUB(t1.stat_date, INTERVAL 30 DAY) AND DATE_SUB(t1.stat_date, INTERVAL 1 DAY)
GROUP BY t1.stat_date, t1.daily_avg;

2. 阈值对比与告警判断 #

在上述计算基础上新增告警状态字段即可，50%的阈值比例可按需调整：

WITH daily_agg AS (
    SELECT 
        stat_date,
        AVG(ind_value) AS daily_avg
    FROM metrics_daily
    GROUP BY stat_date
),
rolling_cal AS (
    SELECT
        stat_date,
        daily_avg,
        AVG(daily_avg) OVER (
            ORDER BY stat_date 
            ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
        ) AS rolling_month_avg
    FROM daily_agg
)
SELECT 
    stat_date,
    daily_avg,
    rolling_month_avg,
    rolling_month_avg * 0.5 AS alarm_threshold,
    CASE 
        WHEN daily_avg < rolling_month_avg * 0.5 THEN '需触发通知'
        ELSE '正常'
    END AS monitor_status
FROM rolling_cal
-- 仅需查询当日监控结果可打开下方注释
-- WHERE stat_date = CURDATE();

使用说明 #

• 若需使用自然月（如上一整月）作为滚动窗口，调整SQL中时间区间的判断条件即可
• 可将脚本配置为每日定时任务，执行后筛选monitor_status = '需触发通知'的记录对接企业微信/邮件/短信等通知通道即可完成自动监控

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