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

(PostgreSQL 8.4)我对SQL gap-and-islands 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,0);
INSERT INTO T1 (val,status) VALUES (11,status) VALUES (12,status) VALUES (13,1);

在这种情况下,val表示设备,状态为ON或OFF.我想用以下逻辑选择记录1,3,6,7和9.

> 10次ON – OK,新序列,包含在SELECT中
> 10匝关闭 – 正确结束序列,忽略行
> 11开 – OK,包含在SELECT中
> 11开启 – 复制,忽略行
> 10关闭 – #10未开启,忽略
> 12开 – OK,隐含关闭#11,包含在SELECT中
> 13开 – OK,隐含关闭#12,包含在SELECT中
> 13关闭 – 正确结束序列,忽略行
> 13开 – OK,包含在SELECT中

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

> new.val = running.val和new.status = 0
> new.val<> running.val和new.status = 1

我正在寻找CTE形式的东西,请不要使用游标.

解决方法

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 *,0) OVER (ORDER BY id) last_on
   FROM   t1
   WHERE  status = 1
   ) x
WHERE  last_on <> val

按要求返回行1,7.

怎么样？

根据您的描述,子查询忽略所有关闭,因为这只是噪声.留下设备开启的条目.其中,只有那些条目被取消资格,同一设备已经开启(最后一个条目开启).使用window function lag().特别是我默认提供0来覆盖第一行的特殊情况 – 假设没有val = 0的设备.
如果有,请选择另一个不可能的数字.
如果没有数字是不可能的,请将特殊情况保留为NULL,并使用滞后(val)OVER …并在外部查询中检查：

WHERE last_on IS DISTINCT FROM val

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!