Vertica SQL查询需求：找出连续2小时CPU Util %≥80%的主机记录

思路说明 #

要定位连续2小时（≥24条5分钟间隔样本）CPU使用率≥80%的记录，核心是先识别每个主机的连续满足条件的记录段，再筛选出长度达标的段，最后提取这些段的完整数据。Vertica的窗口函数可以高效实现这个逻辑，不用复杂的子查询关联。

具体SQL实现 #

WITH filtered_records AS (
    -- 第一步：过滤指定时间范围、CPU≥80%的记录，按主机和时间排序
    SELECT 
        Hostname,
        "CPU Util %" AS cpu_util,
        Timestamp AS epoch_ts,
        TO_TIMESTAMP(Timestamp) AS sample_time -- 转换为可读时间（可选）
    FROM your_table_name
    WHERE 
        Timestamp BETWEEN UNIX_TIMESTAMP('2024-01-01 00:00:00') AND UNIX_TIMESTAMP('2024-01-07 23:59:59') -- 替换为你的起止时间
        AND "CPU Util %" >= 80
    ORDER BY Hostname, Timestamp
),
continuous_groups AS (
    -- 第二步：标记每个连续记录段的唯一ID
    SELECT 
        *,
        -- 若当前记录与上一条的时间差不是5分钟（300秒），则标记为新段起点，累计求和生成段ID
        SUM(CASE 
            WHEN epoch_ts - LAG(epoch_ts) OVER (PARTITION BY Hostname ORDER BY epoch_ts) = 300 THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY Hostname ORDER BY epoch_ts) AS group_id
    FROM filtered_records
),
valid_groups AS (
    -- 第三步：筛选出记录数≥24的有效段（对应连续2小时）
    SELECT Hostname, group_id
    FROM continuous_groups
    GROUP BY Hostname, group_id
    HAVING COUNT(*) >= 24
)
-- 第四步：关联获取所有有效段的完整记录
SELECT fr.*
FROM filtered_records fr
JOIN valid_groups vg 
    ON fr.Hostname = vg.Hostname 
    AND fr.group_id = vg.group_id
ORDER BY fr.Hostname, fr.epoch_ts;

关键逻辑拆解 #

1. filtered_records：先缩小计算范围，只保留符合时间要求和CPU阈值的记录，减少后续运算量。
2. continuous_groups：用LAG()函数获取同主机上一条记录的时间，判断是否为连续的5分钟间隔。若间隔异常，则标记为新段起点，通过SUM()累计生成唯一的group_id，同一段的记录会共享这个ID。
3. valid_groups：按主机和段ID分组统计，筛选出记录数≥24的段（即连续2小时的样本）。
4. 最后通过关联得到所有有效段的完整数据，就是你需要的结果。

注意事项 #

• 替换your_table_name为你的实际表名。
• 起止时间可直接用Epoch秒数值，或通过UNIX_TIMESTAMP()转换时间字符串，按需调整。
• 若存在同一主机同一时间多条重复记录，可在filtered_records中添加DISTINCT或按时间去重。

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