SQL Server Discovery by Several Parameters and Conditions

Question

(PostgreSQL 8.4) I got a great introduction to SQL clearances and islands here in Stack Overflow , but I still have a question, Many CTE island detections are based on the current timestamp order and some flag that breaks the sequence when it changes. But what if the “break” condition is a little more complicated?

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); 

In this case, val is 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 will turn on - OK, a new sequence, turn on in SELECT

• 10 disconnects - ends the sequence correctly, ignores the line

• 11 will turn on - OK, a new sequence, turn on in SELECT

• 11 turns on - duplicates, ignores the line

• 10 is turned off - # 10 is not turned on, ignored

• 12 turns on - OK, implicitly turns off # 11, turns on in SELECT

• 13 turns on - OK, implicitly turns off # 12, turns on in SELECT

• 13 turns off - ends the sequence correctly, ignores the line

• 13 turns on - OK, the new sequence is included in SELECT

In principle, only one device can be turned on at a time, and the break condition is as follows:

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

I am looking for something in the form of a CTE, without cursors.

Answer 1

The answer to the 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) 

How?

Same as before, but this time combine the two window functions. Turning on the device depends on ..
1. The last device turned on was different.
2. or the same device was turned off in its last entry. Angular case with NULL for the first line of the section does not matter, because then the line already has a value of 1.

---

The answer to the original version of the question.

If 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 

Returns rows 1, 3, 6, 7 upon request.

How?

The subquery ignores all shutdowns as it is just noise, as per your description. Transmits records in which the device is turned on. Among them, only those records are disqualified where the same device has already been installed (inclusion of the last record). To do this, use the window function lag() . In particular, I provide 0 by default to cover the special case of the first line - provided that there is no device with val = 0 .
If there is, select another impossible number.
If the number is not possible, leave the special case as NULL with lag(val) OVER ... and in the external query validation:

 WHERE last_on IS DISTINCT FROM val