Pattern detection in a table

Question

The presence of a table, for example:

Table: statuses ID | status ----|--------- 777 | 1 675 | 1 651 | 0 611 | 1 600 | 0 554 | 1 443 | 0 323 | 0 222 | 1 112 | 1 

How to select only rows in which two (or more) states in a row are 0? (in the case of the sample only 443, 323) and group them by the first identifier sequentially.

Thus, the output will be:

 ID | status | group ----|--------|--------- 443 | 0 | 443 323 | 0 | 443 

Answer 1

If a programmatic approach is not possible, you can try this query (although it will have worse performance).

  select s.id, 0 as status, group_field.id as group from statuses s, (select id from statuses where status = 0 group by id having count(1) > 1 limit 1) group_field where s.id in(select id from statuses where id = 0 group by id having count(1) > 1) 

Answer 2

 select id, STATUS, @st_order:=if(@pre_st!=status,0,@st_order+1) as status_order, @group_leader:=if(@pre_st!=status,id,@group_leader) as group_leader, @pre_st:=status from statuses,(select @st_order:=-1, @pre_st:=-1, @group_leader:=-1) val 

This sql gives you output

ID
status,
line number continues the same status,
the same leader of the status group,
useless last column

Given your input, the first four columns are displayed

ID | status | status line number | status group leader
777 | 1 | 0 | 777
675 | 1 | 1 | 777
651 | 0 | 0 | 651
611 | 1 | 0 | 611
600 | 0 | 0 | 600
554 | 1 | 0 | 554
443 | 0 | 0 | 443
323 | 0 | 1 | 443
222 | 1 | 0 | 222
112 | 1 | 1 | 222

So, you can do whatever you want in the external choice.

Answer 3

You can achieve this using a little logic using helper queries

 set @cnt=0; set @id=-1; SELECT t3.id, t3.status, t3.dup_id AS `group` FROM (SELECT id, status, IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great, IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id FROM statuses) t3 WHERE t3.dup_id IN (SELECT t1.dup_id FROM (SELECT id, status, IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great, IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id FROM statuses) t1 GROUP BY t1.dup_id HAVING COUNT(t1.dup_id) > 1 AND t1.dup_id != - 1); 

The result will be what you expected

 id | status | group ----|--------|--------- 443 | 0 | 443 323 | 0 | 443