sql - 通过多个参数和条件检测 SQL 孤岛

Question

(PostgreSQL 8.4) 我对 SQL 间隙和孤岛有了很好的介绍 here on Stack Overflow但我还有一个问题。许多孤岛检测 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以下逻辑。

1. #10 打开 -- OK，新序列，包含在 SELECT 中
2. #10 关闭——正确结束序列，忽略行
3. #11 开启 -- OK，新序列，包含在 SELECT 中
4. #11 打开 -- 重复，忽略行
5. #10 关闭 -- #10 未开启，忽略
6. #12 打开 -- OK，隐式关闭 #11，包含在 SELECT 中
7. #13 打开 -- OK，隐式关闭 #12，包含在 SELECT 中
8. #13 关闭——正确结束序列，忽略行
9. #13 打开 -- OK，新序列，包含在 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 行。

如何？

根据您的描述，子查询忽略所有关闭，因为那只是噪音。留下设备打开的条目。其中，只有那些条目被取消资格，其中相同的设备已经打开(最后一个条目打开)。使用 window function lag()为了那个原因。特别是我提供 0 作为默认值以涵盖第一行的特殊情况 - 假设没有设备具有 val = 0。
如果有，再选一个不可能的数。
如果没有数字是不可能的，将特殊情况保留为 NULL 和 lag(val) OVER ... 并在外部查询中检查:

WHERE last_on IS DISTINCT FROM val