MySQL group by场景下如何按小时统计非连续告警触发次数

实现逻辑 #

你要统计的每小时告警触发次数，本质是统计该小时内告警状态从0（OFF）跳转到1（ON）的次数，每次跳转就算一次新触发，连续的1只算一次。

具体SQL实现 #

我们可以用窗口函数LAG()获取上一条记录的告警状态，再判断是否发生跳转，最后按小时分组统计即可：

WITH alarm_with_prev_status AS (
    SELECT
        value,
        registered_at,
        -- 按时间排序，取上一条记录的value值，第一条记录默认上一条值为0
        LAG(value, 1, 0) OVER (ORDER BY registered_at) AS prev_value
    FROM 你的告警表名
)
SELECT
    -- 按小时截断时间，适配不同数据库可替换对应函数
    DATE_TRUNC('hour', registered_at) AS registered_at,
    -- 统计跳变次数：当前是1且上一条是0时算一次触发
    SUM(CASE WHEN value = 1 AND prev_value = 0 THEN 1 ELSE 0 END) AS alarm_triggered
FROM alarm_with_prev_status
GROUP BY DATE_TRUNC('hour', registered_at)
ORDER BY registered_at;

适配说明 #

如果你使用的不是PostgreSQL，可替换时间截断函数：

• MySQL：替换DATE_TRUNC('hour', registered_at)为DATE_FORMAT(registered_at, '%Y-%m-%d %H:00:00')
• SQL Server：替换为DATEADD(HOUR, DATEDIFF(HOUR, 0, registered_at), 0)

扩展场景 #

如果你的表中存在多条不同告警的记录，只需要在LAG的窗口函数中加上分区规则即可：

LAG(value, 1, 0) OVER (PARTITION BY 告警ID字段 ORDER BY registered_at) AS prev_value

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