查找一列中包含 6 个连续 1 的组

Question

我有一个包含 2 列的表：

• val 其值：0 或 1
• id 具有唯一标识符

with cte(val, id) as (
    select 0, 0 union all
    select 1, 1 union all
    select 1, 2 union all
    select 0, 3 union all
    select 1, 4 union all
    select 1, 5 union all
    select 1, 6 union all
    select 1, 7 union all
    select 1, 8 union all
    select 1, 9 union all
    select 1, 10
)
select *
into #tmp
from cte

如何查找连续 6 个值 = 1 的 id。

在上面的示例中：id = 9，id = 10。

最好不要使用循环（游标或 while），而是使用类似于 sum(...) over(...) 的内容。

Answer 1

为什么不 LAG() （但你需要一个订单列）：

SELECT id
FROM (
   SELECT 
      id,
      val,
      val1 = LAG(val, 1) OVER (ORDER BY id),
      val2 = LAG(val, 2) OVER (ORDER BY id),
      val3 = LAG(val, 3) OVER (ORDER BY id),
      val4 = LAG(val, 4) OVER (ORDER BY id),
      val5 = LAG(val, 5) OVER (ORDER BY id)
   FROM YourTable
) t
WHERE val = 1 AND val1 = 1 AND val2 = 1 AND val3 = 1 AND val4 = 1 AND val5 = 1

Answer 2

您可以在包含 6 行（前 5 行加当前行）的窗口框架上使用运行求和：

with cte as (
    select *, sum(val) over (
        order by id
        rows between 5 preceding and current row
    ) as rsum
    from #tmp
)
select *
from cte
where rsum = 6

调整窗口和 where 子句的大小以匹配所需的值。

Answer 3

另一种方法是在 LAG 值上使用 ROW_NUMBER，

declare @tmp table (val int, id int)
insert into @tmp values  
(0, 0), (1, 1), (1, 2), (0, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10)

select 0, 0 union all 
select 1, 1 union all 
select 1, 2 union all 
select 0, 3 union all 
select 1, 4 union all 
select 1, 5 union all 
select 1, 6 union all 
select 1, 7 union all 
select 1, 8 union all 
select 1, 9 union all 
select 1, 10

select t2.id,
       t2.islandcount
from   ( select t.id,
                t.val,
                t.priorval,
                row_number() over (partition by t.val, t.priorval order by t.id) as islandcount
         from   ( select id,
                         val,
                         lag(val, 1) over (order by id) priorval
                  from   @tmp
                ) t
       ) t2 
where  t2.islandcount >= 6

结果是

id  islandcount
9   6
10  7

亲自尝试 在此 DBFiddle

这种方法的优点是您可以轻松地设置值6 到任何其他值

编辑

正如@Zhorov 在评论中提到的，我的代码中有一个缺陷，当您添加某些行时它会返回错误的结果

此解决方案将修复该问题，它基于@SalmanA 的解决方案，因此接受答案的功劳应该归于他

declare @tmp table (val int, id int)
insert into @tmp values  
(0, 0), (1, 1), (1, 2), (0, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10)

-- these are the certains rows added
,(0, 11), (1, 12), (1, 13)


select t.id,
       t.val,
       t.islandcount
from   ( select id,
                val,
                sum(val) over (order by id rows between 5 preceding and current row) as islandcount
         from   @tmp
       ) t
where  t.islandcount >= 6
order by t.id

，并且再次 DBFiddle