检查表中每条记录后首先出现两个值中的哪一个

Suppose I have the following table for the log of how a piece of equipment was used:

假设我有一张关于如何使用设备的日志的表：

CREATE TABLE equip_log(run_id smallserial primary key, recipe smallint)
INSERT INTO equip_log(recipe) VALUES (1), (1), (2), (1), (3), (0), (1), (2), (1), (1), (0), (2), (2), (1), (2), (0), (1), (1), (3)

That gives:

这给了：

 run_id | recipe
--------+--------
      1 |      1
      2 |      1
      3 |      2
      4 |      1
      5 |      3
      6 |      0
      7 |      1
      8 |      2
      9 |      1
     10 |      1
     11 |      0
     12 |      2
     13 |      2
     14 |      1
     15 |      2
     16 |      0
     17 |      1
     18 |      1
     19 |      3

Recipes #1, 2, and 3 are different ways of running the equipment. Recipe #0 is a maintenance procedure.

配方＃1,2和3是运行设备的不同方式。配方＃0是维护程序。

I want a query that returns two columns: the run_id for runs that used recipe #1, and then I want the query to look ahead at whether there's a future maintenance run (recipe 0) that comes before any other runs using recipe #1. So here's my desired output, with explanation for a few of the rows:

我想要一个返回两列的查询：run_id用于使用食谱＃1的运行，然后我希望查询能够预测在使用食谱＃1进行任何其他运行之前是否有未来的维护运行（配方0）。所以这是我想要的输出，并解释了几行：

run_id | maint_next
-------+-----------
     1 | False        [Run #2 uses recipe 1, comes before the next recipe 0]
     2 | False        [Run #4 uses recipe 1, comes before the next recipe 0]
     4 | True         [Recipe 0 shows up before any other recipe 1's]
     7 | False
     9 | False
    10 | True
    14 | True
    17 | False
    18 | False        [Could become True if a maintenance gets logged next, but is False on current data]

1 个解决方案

#1

You can use the LEAD() window function to check the next value:

您可以使用LEAD（）窗口函数来检查下一个值：

 SELECT run_id, recipe, LEAD(recipe) OVER (ORDER BY run_id) = 0
 FROM equip_log
 WHERE recipe = 1 OR recipe = 0;
┌────────┬────────┬──────────┐
│ run_id │ recipe │ ?column? │
├────────┼────────┼──────────┤
│      1 │      1 │ f        │
│      2 │      1 │ f        │
│      4 │      1 │ t        │
│      6 │      0 │ f        │
│      7 │      1 │ f        │
│      9 │      1 │ f        │
│     10 │      1 │ t        │
│     11 │      0 │ f        │
│     14 │      1 │ t        │
│     16 │      0 │ f        │
│     17 │      1 │ f        │
│     18 │      1 │ (null)   │
└────────┴────────┴──────────┘
(12 rows)

Then just filter out the recipe = 0 rows:

然后只过滤掉recipe = 0行：

SELECT *
FROM (
  SELECT run_id, recipe,
         COALESCE(LEAD(recipe) OVER (ORDER BY run_id) = 0, false) AS maintenance_next 
  FROM equip_log
  WHERE recipe = 1 OR recipe = 0
) s
WHERE recipe = 1;
┌────────┬────────┬──────────────────┐
│ run_id │ recipe │ maintenance_next │
├────────┼────────┼──────────────────┤
│      1 │      1 │ f                │
│      2 │      1 │ f                │
│      4 │      1 │ t                │
│      7 │      1 │ f                │
│      9 │      1 │ f                │
│     10 │      1 │ t                │
│     14 │      1 │ t                │
│     17 │      1 │ f                │
│     18 │      1 │ f                │
└────────┴────────┴──────────────────┘
(9 rows)

#1