PostgreSQL：获取首个未通过测试行或最后测试行

需求回顾 #

需要从设备测试记录表中按以下规则筛选数据：

1. 仅保留每个测试的最后一次测量（is_last_measurement_on_test=true）
2. 若设备存在未通过测试（ok=false），取该设备首个未通过的测试行
3. 若设备所有测试均通过，取该设备最后一个通过的测试行

解决方案SQL #

WITH measurements (device_id,measurement_no,test,is_last_measurement_on_test,ok,error_code) AS ( VALUES
  -- case 1: all measurements good, expecting to show test 3 only
  ('d1',1,'test1',true,true,0),
  ('d1',2,'test2',true,true,0),
  ('d1',3,'test3',true,true,0),
  -- case 2: test 2, expecting to show test 2 only
  ('d2',1,'test1',true,true,0),
  ('d2',2,'test2',true,false,100),
  ('d2',3,'test3',true,true,0),
  -- case 3: test 2 und 3 bad, expecting to show test 2 only
  ('d3',1,'test1',true,true,0),
  ('d3',2,'test2',true,false,100),
  ('d3',3,'test3',true,false,200),
  -- case 4: test 2 bad on first try, second time good, expecting to show test 3 only
  ('d4',1,'test1',true,true,0),
  ('d4',2,'test2',false,false,100),
  ('d4',3,'test2',true,true,0),
  ('d4',4,'test3',true,true,0)
),
-- 步骤1：筛选每个测试的最后一次测量结果
last_measurements AS (
    SELECT *
    FROM measurements
    WHERE is_last_measurement_on_test = true
),
-- 步骤2：为每个设备的记录标记优先级排序
ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY device_id
            ORDER BY 
                -- 未通过测试优先级高于通过测试
                CASE WHEN ok = false THEN 0 ELSE 1 END,
                -- 未通过测试按执行顺序升序（取首个失败），通过测试按执行顺序降序（取最后一个成功）
                CASE WHEN ok = false THEN measurement_no ELSE -measurement_no END
        ) AS record_rank
    FROM last_measurements
)
-- 步骤3：提取每个设备优先级最高的记录
SELECT device_id, measurement_no, test, is_last_measurement_on_test, ok, error_code
FROM ranked_records
WHERE record_rank = 1;

逻辑解释 #

1. 筛选最后一次测量：先过滤出is_last_measurement_on_test=true的记录，确保只处理每个测试的最终结果。
2. 优先级排序：
   • 通过ROW_NUMBER()按设备分组排序，未通过测试（ok=false）直接排在前面；
   • 未通过测试按measurement_no升序，确保最早出现的失败测试排在第1位；
   • 通过测试按-measurement_no升序（等价于measurement_no降序），确保最后一个通过的测试排在第1位。
3. 提取目标记录：取每个设备中record_rank=1的行，就是符合需求的结果。

为什么之前的first_value尝试未生效 #

first_value(nullif(error_code,0))仅能提取第一个非0错误码，但没有结合排序逻辑定位到对应的行，也无法区分“取首个失败行”和“取最后一个成功行”的两种场景，因此需要通过排序+行号的方式精准筛选目标记录。

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