如何用SQL高效标记连续5小时及以上数值低于0的时段

核心思路 #

采用连续区间岛屿分组法，仅需2次窗口函数遍历即可完成计算，避免多次调用lag/lead带来的额外扫描开销，性能提升显著。

实现步骤 #

• 标记区间分界：数值≥0的行赋值为1、<0的行赋值为0，按时间排序累加求和，累加值即为连续负数值区间的分组ID，同一分组内的行属于同一个连续负数值时段
• 分组校验长度：统计每个分组的行数量（对应连续时长，单行为1小时），只要分组行数≥5，该分组内所有行标记为True，其余行标记为False

通用代码示例（支持所有标准SQL窗口函数引擎） #

适用于MySQL 8.0+、PostgreSQL、Spark SQL、Hive、ClickHouse等主流SQL引擎：

WITH step1 AS (
    -- 生成连续负数值区间的分组ID
    SELECT 
        时间,
        数值,
        SUM(CASE WHEN 数值 >= 0 THEN 1 ELSE 0 END) OVER (ORDER BY 时间) AS group_id
    FROM 你的业务表名
),
step2 AS (
    -- 筛选出时长满足≥5小时的连续负数值分组
    SELECT 
        group_id
    FROM step1
    WHERE 数值 < 0
    GROUP BY group_id
    HAVING COUNT(*) >= 5
)
-- 关联得到最终标记结果
SELECT 
    a.时间,
    a.数值,
    CASE WHEN b.group_id IS NOT NULL THEN TRUE ELSE FALSE END AS FiveConse
FROM step1 a
LEFT JOIN step2 b ON a.group_id = b.group_id
ORDER BY a.时间;

方案优势 #

• 时间复杂度为O(n)，相比多次lag/lead的O(kn)（k为连续校验时长）性能提升明显，连续时长要求越长优势越突出
• 逻辑可扩展性强，调整连续时长阈值仅需修改step2的HAVING条件即可，不需要调整窗口函数逻辑
• 适配性高，若存在小时数据缺行的场景，仅需将step1的SUM窗口函数调整为时间范围窗口即可兼容

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