mysql - SQL:查找具有替代 0's and 1' s 的行数。基本上需要找到这样的序列的最大计数

Question

这与此处提出的另一个问题有关 SQL: count all records with consecutive occurrence of same value for each device set and return the highest count

但有一点不同。我用表格来解释一下。在下表中，我们需要获取遵循模式 1 0 的所有条目的最大计数，即先打开然后再打开和关闭等。基本上，需要检索连续打开和关闭的任何设备(device_id)最大计数为 101010(按 3 计)模式。关于的链接检索相同但具有相同连续值的链接。需要调整为 1 0 模式

Device ID        on_off             DateTime
--------------------------------------------------
07777778999       1               18-12-2016 17:15
07777778123       1               18-12-2016 18:10
07777778999       1               19-12-2016 19:30
07777778999       1               19-12-2016 20:15
07777778999       0               19-12-2016 21:15
07777778999       1               20-12-2016 11:15
07777778999       0               20-12-2016 12:15
07777778999       1               20-12-2016 17:15
07777778999       0               20-12-2016 17:25
07777778999       1               20-12-2016 17:35
07777778999       0                20-12-2016 17:45
07777778999       0               20-12-2016 17:55
07777778999       0               20-12-2016 18:50
07777778999       0               20-12-2016 18:55
07777778999       1                20-12-2016 19:05
07777778999       0               20-12-2016 19:25
07777778999       1               20-12-2016 19:30
07777778999       0               20-12-2016 19:45
07777778123       1               28-12-2016 20:10
07777778123       1               28-12-2016 20:15
07777778123       1               28-12-2016 20:25
07777778123       1               28-12-2016 20:30
07777778123       0               28-12-2016 20:40

---------------------------------------------------------------- ```
so my expected table would be


```Device ID        max_cons_on_off       
-------------------------------
07777778999       4               
07777778123       1    ```           

Answer 1

这看起来更复杂。我的想法是通过查看前两行来了解岛屿从哪里开始。

select t.*,
       sum( case when oo_prev = 0 and oo_prev2 = 1 then 0 else 1 end) Over (partition by device_id order by datetime) as grp
from (select t.*,
             lag(on_off) over (partition by device_id order by datetime) as oo_prev,
             lag(on_off, 2) over (partition by device_id order by datetime) as oo_prev2
      from t
     ) t;

然后聚合、过滤、再次聚合:

select device_id, max(cnt)
from (select device_id, count(*) as cnt
      from (select t.*,
                   sum( case when oo_prev = 0 and oo_prev2 = 1 and on_off = 1 and oo_next = 0 then 0 else 1 end) Over (partition by device_id order by datetime) as grp
            from (select t.*,
                         lag(on_off) over (partition by device_id order by datetime) as oo_prev,
                         lag(on_off, 2) over (partition by device_id order by datetime) as oo_prev2
                         lead(on_off, 1) over (partition by device_id order by datetime) as oo_next
                  from t
                 ) t
            ) t
     where on_off = 1 and oo_next = 0
     group by device_id, grp
    ) t
group by device_id;