PostgreSQL：获取第一个非真行或最后一行

agyaoht7 于 2023-02-08 发布在 PostgreSQL

关注(0)|答案(2)|浏览(201)

我有一个表格，其中包含每台器械几项测试的测量信息：

device_id提供有关测试的设备的信息
measurement_no是一个递增的数字，给出测试执行的顺序
test为您提供所执行测试的名称
is_last_measurement_on_test是一个布尔字段，提供特定行是否为测试的最后一次测量的信息。如果该行是特定测试的设备的最后一行，则返回真。如果同一测试的同一设备有后续行，则返回假。
ok提供测试合格（=真）或不合格（=假）的信息
如果ok=false，error_code将为您提供特定的错误代码;如果ok=true，error_code将提供0

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)
)
select * from measurements
where is_last_measurement_on_test=true

现在，我想根据每个设备的以下条件筛选这些行：

仅应考虑每次测试的最后一次测量-〉这很简单：在测试上的最后一次测量时过滤=真
对于每台设备：如果在is_last_measurement_on_test=true的任何测试中出现错误结果（ok=false），我希望显示器械失败的第一个测试。
对于每台设备：如果在is_last_measurement_on_test=true的任何测试中没有任何不良结果（ok=true），我希望显示器械通过的最后一项测试。

对于上面给出的示例，我希望只显示以下行：

('d1',3,'test3',true,true,0) 
  ('d2',2,'test2',true,false,100)
  ('d3',2,'test2',true,false,100)
  ('d4',4,'test3',true,true,0)

我怎样才能得到这个结果呢？我已经尝试了很多使用first_value的方法，例如

first_value(nullif(error_code,0)) over (partition by device_id)

但我没能以我想要的方式来处理。

postgresql

来源：https://stackoverflow.com/questions/75370729/postgresql-get-first-non-true-row-or-last-row

2条答案

按热度按时间

a0zr77ik1#

有了这个样本数据：

CREATE TABLE measurements (
  device_id text,
  measurement_no integer,
  test text,
  is_last_measurement_on_test boolean,
  ok boolean,
  error_code integer
);

INSERT INTO measurements (device_id, measurement_no, test, is_last_measurement_on_test, ok, error_code)
VALUES
  ('d1', 1, 'test1', true, true, 0),
  ('d1', 2, 'test2', true, true, 0),
  ('d1', 3, 'test3', true, true, 0),
  ('d2', 1, 'test1', true, true, 0),
  ('d2', 2, 'test2', true, false, 100),
  ('d2', 3, 'test3', true, true, 0),
  ('d3', 1, 'test1', true, true, 0),
  ('d3', 2, 'test2', true, false, 100),
  ('d3', 3, 'test3', true, false, 200),
  ('d4', 1, 'test1', true, true, 0),
  ('d4', 2, 'test2', false, false, 100),
  ('d4', 3, 'test2', true, true, 0),
  ('d4', 4, 'test3', true, true, 0);

它会像：

WITH DataSource AS
(
  SELECT *
       ,MIN(CASE WHEN ok = false THEN test END) OVER (PARTITION BY device_id) AS first_failed_test
       ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY test DESC) AS test_id
  FROM measurements
  WHERE is_last_measurement_on_test = true
)
SELECT device_id, measurement_no, test, is_last_measurement_on_test, ok, error_code
FROM DataSource
WHERE (first_failed_test IS NULL and test_id = 1)
    OR (first_failed_test = test)

其思想是获取第一个失败测试的名称，并使用row_number从最新的测试开始对测试进行排序。
重要的是，我在这里是按名称排序测试的。在您的真实场景中，我猜您有一个record_id或date，可以用来完成此操作。因此，您需要对代码进行一些更改。

赞(0）回复(0）举报 2023-02-08

k10s72fa2#

distinct on为您提供一个，每个device_id的 “顶级” 记录。
order by允许您建立记录可能或可能不位于 “top” 的顺序。由于您的第二种和第三种情况要求每个device_id的测试具有相反的顺序/优先级：
1.***最早的***负数记录（当有负数时）
1.***最新***记录（如果没有底片）
您可以使用case相应地翻转该顺序。

select distinct on (device_id) * 
from measurements 
where is_last_measurement_on_test
order by device_id,   --Necessary for distinct on to return one row per device_id
         not ok desc, --If all is ok for a device_id, this does nothing. 
                      --Otherwise it'll put negative tests results first
         (case when not ok then -1 else 1 end)*measurement_no desc;
                      --When considering negative test results, it'll 
                      --put earliest first. Otherwise it'll put latest first.

一个常见的误解是order by节被限制为纯列名或别名。同时，引用文档，它给你的自由和select节一样多：
排序表达式可以是在查询的选择列表中有效的任何表达式。
Online demo.

赞(0）回复(0）举报 2023-02-08

我来回答

PostgreSQL：获取第一个非真行或最后一行

2条答案

相关问题

热门标签

最新问答