如何在SQL Hive中基于前置值逻辑为目标列赋值？

问题核心逻辑 #

当Temp≥95时，状态切换为System Off；后续只要Temp>80则维持该状态，直到遇到Temp<80的行，状态切换为System On；初始状态为System On。

正确SQL实现 #

WITH ranked_data AS (
  SELECT 
    位置ID,
    事件时间,
    Temp,
    -- 标记触发System Off的节点
    CASE WHEN Temp >= 95 THEN 1 ELSE 0 END AS trigger_off,
    -- 标记重置为System On的节点
    CASE WHEN Temp < 80 THEN 1 ELSE 0 END AS reset_on
  FROM your_table
  ORDER BY 位置ID, 事件时间
),
grouped_data AS (
  SELECT 
    *,
    -- 按位置分区，累计重置节点生成分组ID：每次重置后开启新分组
    SUM(reset_on) OVER (PARTITION BY 位置ID ORDER BY 事件时间 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM ranked_data
),
trigger_tracking AS (
  SELECT 
    *,
    -- 在每个分组内，累计触发Off的次数，判断是否已进入Off状态
    SUM(trigger_off) OVER (PARTITION BY 位置ID, group_id ORDER BY 事件时间 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_trigger
  FROM grouped_data
)
SELECT 
  位置ID,
  事件时间,
  Temp,
  CASE
    WHEN reset_on = 1 THEN 'System On'
    WHEN cumulative_trigger > 0 THEN 'System Off'
    ELSE 'System On'
  END AS valid_consecutive_values
FROM trigger_tracking
ORDER BY 位置ID, 事件时间;

方案说明 #

1. 标记关键节点：用trigger_off标记触发Off的行，reset_on标记触发On的行。
2. 生成状态分组：通过累计reset_on的值，将每次重置后的行划分为独立分组，确保每个分组内的状态流是连续的。
3. 追踪触发状态：在每个分组内累计trigger_off的值，只要分组内存在过触发Off的行，后续所有非重置行都维持Off状态。
4. 最终状态判断：重置行直接设为On；分组内已触发过Off的非重置行设为Off；其余行设为On。

原方案问题分析 #

你之前的SQL使用相邻行的求和逻辑，只能覆盖前后1行的局部情况，无法追踪从触发Off到重置On之间的完整连续区间，因此无法正确维持状态。

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