在多个参数和条件下检测SQL孤岛

Question

（PostgreSQL 8.4） 我对SQL的空白和孤岛做了很好的介绍，但是我仍然有一个问题。许多孤岛检测CTE基于时间戳的运行顺序和一些标志，这些标志在更改时会破坏序列。但是，如果“中断”条件稍微复杂些怎么办？

CREATE TABLE T1
(
  id SERIAL PRIMARY KEY,
  val INT,   -- some device
  status INT -- 0=OFF, 1=ON
);

INSERT INTO T1 (val, status) VALUES (10, 1);
INSERT INTO T1 (val, status) VALUES (10, 0);
INSERT INTO T1 (val, status) VALUES (11, 1);
INSERT INTO T1 (val, status) VALUES (11, 1);
INSERT INTO T1 (val, status) VALUES (10, 0);
INSERT INTO T1 (val, status) VALUES (12, 1);
INSERT INTO T1 (val, status) VALUES (13, 1);
INSERT INTO T1 (val, status) VALUES (13, 0);
INSERT INTO T1 (val, status) VALUES (13, 1);

在这种情况下，val表示设备，并且status为ON或OFF。我想选择的记录1，3，6，7并9具有以下逻辑。

2. 10转为OFF-正确结束序列，忽略行

3. 11接通-确定，新序列，包括在SELECT中

4. 11开启-重复，忽略行

5. 10熄灭-＃10没亮，请忽略

6. 12开启-确定，隐式关闭＃11，包括在SELECT中

7. 13开启-确定，隐式关闭＃12，包括在SELECT中

8. 13设为OFF-正确结束序列，忽略行

9. 13接通-确定，新序列，包括在SELECT中

基本上，一次只能打开一个设备，并且“中断”条件是：

• new.val = running.val AND new.status = 0
• new.val <> running.val AND new.status = 1

我正在寻找CTE形式的东西， 请不要游标 。

Answer 1

回答更新的问题

SELECT *
FROM  (
   SELECT *
         ,lag(val, 1, 0) OVER (PARTITION BY status ORDER BY id) last_val
         ,lag(status) OVER (PARTITION BY val ORDER BY id) last_status
   FROM   t1
   ) x
WHERE  status = 1
AND    (last_val <> val OR last_status = 0)

如何？

与以前相同，但是这次结合了两个窗口功能。打开设备是否符合以下条件：
1.最后打开的设备是否与其他设备 不同 。
2.或同一设备的最后一个条目已 关闭 。NULL分区的第一行与的大写字母无关紧要，因为那么该行已经在 1中 限定了 。

回答问题的原始版本。

如果您正确地理解了您的任务，则可以通过以下简单查询完成任务：

SELECT *
FROM  (
   SELECT *
         ,lag(val, 1, 0) OVER (ORDER BY id) last_on
   FROM   t1
   WHERE  status = 1
   ) x
WHERE  last_on <> val

根据要求返回第1、3、6、7行。

如何？

根据您的描述，子查询将忽略所有关闭操作，因为这仅仅是噪音。将条目保留在打开设备的位置。其中，只有那些条目已被取消资格，而同一设备已经处于打开状态（最后一个条目处于打开状态）。为此使用窗口功能lag()。特别是，我0默认提供第一行的特殊情况-假设没有带的设备val = 0。
如果有，请选择另一个不可能的数字。
如果没有数字是不可能的，则保留和一样的特殊情况NULL，lag(val) OVER ...并在外部查询中检查与：

WHERE last_on IS DISTINCT FROM val