在多个参数和条件下检测SQL孤岛 [英] Detect SQL island over multiple parameters and conditions
问题描述
（PostgreSQL 8.4）我对SQL的空白和岛做了很好的介绍此处关于堆栈溢出，但我仍然有一个问题。许多孤岛检测CTE基于时间戳的运行顺序和一些标志，这些标志在更改时会破坏序列。但是，如果中断条件稍微复杂一点呢？
(PostgreSQL 8.4) I got a great introduction to SQL gaps-and-islands here on Stack Overflow but I still have a question. Many island detection CTEs are based on a running order of a timestamp and some flag which breaks the sequence when it changes. But what if the "break" condition is a little more complex?
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 表示设备，并且状态处于 ON 或 OFF 。我想选择记录 1 ， 3 ， 6 ， 7 和 9 具有以下逻辑。
In this case, val represents a device, and status is either ON or OFF. I want to select records 1, 3, 6, 7 and 9 with the following logic.
-
10打开-确定，新序列，包括在SELECT
-
10关闭-正确结束序列，忽略行
-
11打开-确定，新序列，包括在SELECT
-
11打开-重复，忽略行
-
10关闭-＃10未打开，忽略
-
12打开-确定，隐式关闭＃11，包括在SELECT中
-
13打开-确定，隐式关闭＃12，在SELECT中包含
-
13关闭-正确结束序列，忽略行
-
13开启-确定，新序列，包括在SELECT
10 turns ON -- OK, new sequence, include in SELECT
10 turns OFF -- ends sequence properly, ignore row
11 turns ON -- OK, new sequence, include in SELECT
11 turns ON -- duplicate, ignore row
10 turns OFF -- #10 wasn't ON, ignore
12 turns ON -- OK, implicitly turns OFF #11, include in SELECT
13 turns ON -- OK, implicitly turns OFF #12, include in SELECT
13 turns OFF -- ends sequence properly, ignore row
13 turns ON -- OK, new sequence, include in SELECT
基本上，只有一个设备一次可以打开，并且中断条件是：
Basically, only one device can be ON at a time, and the "break" condition is that:
- new.val = running.val AND new.status = 0
- new.val<> running.val AND new.status = 1
我在找东西以CTE的形式，请不要使用游标。
I'm looking for something in the form of a CTE, no cursors please.
推荐答案
更新问题的答案
Answer for updated question
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.或已接通同一设备 off 在其最后一个条目中。分区第一行的 NULL 的大小写无关，因为那行已经在 1中了。
How?
Same as before, but this time combine two window functions. Switching on a device qualifies if ..
1. the last device switched on was a different one.
2. or the same device has been switched off in its last entry. The corner case with NULL for the first row of the partition is irrelevant, because then the row already qualified in 1.
如果您对我的任务理解正确，这个简单的查询即可完成工作：
If your I understand your task correctly, this simple query does the job:
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行。
Returns rows 1, 3, 6, 7 as requested.
根据您的描述，子查询将忽略所有关闭操作，因为这只是噪音。将条目保留在打开设备的位置。其中，只有那些条目已被取消资格，而同一设备已经处于打开状态（最后一个条目处于打开状态）。使用窗口函数 lag （） 。特别是，我默认提供 0 来覆盖第一行的特殊情况-假设没有设备的 val = 0 。
如果存在，请选择另一个不可能的数字。
如果没有数字，则将特殊情况保留为 NULL 具有 lag（val）OVER ... 并在外部查询中检查：
The subquery ignores all switching off, as that is just noise, according to your description. Leaves entries where a device is switched on. Among those, only those entries are disqualified, where the same device was on already (the last entry switching on). Use the window function lag() for that. In particular I provide 0 as default to cover the special case of the first row - assuming that there is no device with val = 0.
If there is, pick another impossible number.
If no number is impossible, leave the special case as NULL with lag(val) OVER ... and in the outer query check with:
WHERE last_on IS DISTINCT FROM val
这篇关于在多个参数和条件下检测SQL孤岛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
