如何基于匹配条件对零件及其后续10个零件进行状态分类？

需求回顾 #

现有Trace_table结构：

• parent : String
• part : String
• local_datetime : Datetime
• trace_number : INT64（格式为DDMMYYYYHHMMSS）
• parameter : String
• value : INT64

核心规则：

• 每个时间点对应53个参数，包含"Hold"参数
• 当某时间点的parameter = 'Hold'且value = 0时：
  1. 该时间点的所有参数行标记为Bad
  2. 按parent、part分区，该时间点之后的连续10个时间点的所有参数行也标记为Bad
• 最终输出需新增part_condition字段，标记零件状态（Bad或Good）

实现思路 #

1. 按时间点聚合，标记触发不良的时间点：按parent、part、local_datetime分组，判断每个时间点是否触发不良条件，同时给每个时间点按时间顺序分配行号。
2. 计算不良时间范围：找出所有触发不良的时间点的行号，确定每个时间点是否落在「触发行号 ~ 触发行号+10」的范围内。
3. 关联原表，标记所有参数行：将时间点的不良标记关联回原表的每一行，生成最终的part_condition字段。

完整SQL代码 #

WITH time_point_status AS (
  -- 按时间点聚合，标记触发不良的时间点，并给每个时间点按时间排序
  SELECT
    parent,
    part,
    local_datetime,
    trace_number,
    -- 判断当前时间点是否触发不良条件
    MAX(CASE WHEN parameter = 'Hold' AND value = 0 THEN 1 ELSE 0 END) AS is_bad_trigger,
    -- 按parent+part分区，给时间点按时间顺序排号
    ROW_NUMBER() OVER (PARTITION BY parent, part ORDER BY local_datetime) AS time_row_num
  FROM Trace_table
  GROUP BY parent, part, local_datetime, trace_number
),
bad_time_ranges AS (
  -- 找出所有触发不良的时间点的行号，以及对应的不良结束行号（当前行号+10）
  SELECT
    parent,
    part,
    time_row_num AS bad_start_row,
    time_row_num + 10 AS bad_end_row
  FROM time_point_status
  WHERE is_bad_trigger = 1
),
time_point_final_status AS (
  -- 判断每个时间点是否属于不良范围
  SELECT
    tps.*,
    CASE
      -- 自身是触发点，或者落在某个不良范围内
      WHEN tps.is_bad_trigger = 1 
           OR EXISTS (
               SELECT 1 FROM bad_time_ranges btr
               WHERE btr.parent = tps.parent
                 AND btr.part = tps.part
                 AND tps.time_row_num BETWEEN btr.bad_start_row AND btr.bad_end_row
             )
      THEN 'Bad'
      ELSE 'Good'
    END AS part_condition
  FROM time_point_status tps
)
-- 关联原表，给每一行标记状态
SELECT
  tt.parent,
  tt.part,
  tt.local_datetime,
  tt.trace_number,
  tt.parameter,
  tt.value,
  tps.part_condition
FROM Trace_table tt
JOIN time_point_final_status tps
  ON tt.parent = tps.parent
  AND tt.part = tps.part
  AND tt.local_datetime = tps.local_datetime
  AND tt.trace_number = tps.trace_number
ORDER BY tt.parent, tt.part, tt.local_datetime, tt.parameter;

代码说明 #

1. time_point_status CTE：

   • 按时间点聚合，通过MAX(CASE...)判断该时间点是否触发不良（只要该时间点有Hold=0的行，就标记为触发点）
   • 用ROW_NUMBER()给每个parent+part下的时间点按时间排序，方便后续计算范围

2. bad_time_ranges CTE：

   • 筛选出所有触发不良的时间点，计算其不良影响的结束行号（当前行号+10）

3. time_point_final_status CTE：

   • 对每个时间点，判断是否是触发点，或者落在某个触发点的10个后续范围内，标记为Bad，否则为Good

4. 最终关联：

   • 将时间点的状态关联回原表的每一行，确保同一时间点的所有参数行都获得相同的part_condition标记

注意事项 #

• 确保local_datetime的排序是正确的，若trace_number的时间顺序更准确，可将ORDER BY local_datetime替换为ORDER BY trace_number
• 若存在同一parent+part+local_datetime下多个trace_number的情况，需根据实际业务调整分组逻辑

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