如何在SQL中基于阈值标记同一连续集群内的所有数据？

要实现需求，核心是先识别连续的负温度区间（集群），再判断每个区间是否存在温度≤-5的记录，最后结合规则标记flag。

具体实现步骤 #

1. 划分连续负温度分组：用窗口函数生成分组ID，每当遇到温度≥0的行，分组ID递增，确保连续的负温度被归为同一组。
2. 统计分组阈值状态：对每个分组，判断是否存在温度≤-5的记录。
3. 生成flag列：依据规则，满足以下任一条件则标记为true，否则为false：
   • 当前行温度≤-5；
   • 当前行温度<0，且所在分组存在温度≤-5的记录。

通用SQL代码（兼容Databricks SQL及多数主流方言） #

WITH temp_groups AS (
    SELECT 
        time,
        temperature,
        -- 生成连续负温度的分组ID：非负温度触发分组递增
        SUM(CASE WHEN temperature >= 0 THEN 1 ELSE 0 END) OVER (ORDER BY time) AS group_id
    FROM your_table_name
),
group_flags AS (
    SELECT 
        group_id,
        -- 标记分组是否存在≤-5的温度记录
        MAX(CASE WHEN temperature <= -5 THEN 1 ELSE 0 END) AS has_cold_threshold
    FROM temp_groups
    GROUP BY group_id
)
SELECT 
    t.time,
    t.temperature,
    CASE 
        WHEN t.temperature <= -5 THEN TRUE
        WHEN t.temperature < 0 AND g.has_cold_threshold = 1 THEN TRUE
        ELSE FALSE
    END AS flag
FROM temp_groups t
LEFT JOIN group_flags g ON t.group_id = g.group_id
ORDER BY t.time;

代码说明 #

• temp_groups CTE：通过累积求和的窗口函数，把连续的负温度行分配到同一个group_id下，非负温度的行会开启新的分组。
• group_flags CTE：对每个分组做聚合，用MAX函数判断该组是否存在阈值温度（≤-5），存在则返回1，否则返回0。
• 主查询的CASE语句严格对应需求规则，确保输出结果符合预期。

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