当一列连续 4 次具有相同的值时，返回第一行值

Question

提前感谢您的帮助。我正在使用MS SQL Server 2008，并尝试从Auto_Manual列中值为"0"的第一行捕获数据，其中一行至少有四个"0"。以下是我的数据示例：

Lot      DateTimeStamp  Auto_Manual rn
----------------------------------------
abc123   1/4/2017 2:08      1        1
abc123   1/4/2017 2:10      1        2
abc123   1/4/2017 2:12      1        3
abc123   1/4/2017 2:14      1        4
abc123   1/4/2017 2:16      1        5
abc123   1/4/2017 2:18      1        6
abc123   1/4/2017 2:20      1        7
abc123   1/4/2017 2:22      1        8
abc123   1/4/2017 2:24      0        1
abc123   1/4/2017 2:26      1        9
abc123   1/4/2017 2:28      1        10
abc123   1/4/2017 2:30      1        11
abc123   1/4/2017 2:32      0        2
abc123   1/4/2017 2:34      1        12
abc123   1/4/2017 2:36      1        13
abc123   1/4/2017 2:38      0        3
abc123   1/4/2017 2:40      0        4
abc123   1/4/2017 2:42      0        5
abc123   1/4/2017 2:44      0        6
abc123   1/4/2017 2:46      0        7 

我正在寻找的结果是：

Lot      DateTimeStamp  Auto_Manual rn
----------------------------------------
abc123   1/4/2017 2:38      0        3

到目前为止，我所能做的就是获取按 Lot 和 Auto_Manual 列分区的行号：

select 
   Lot
  ,DateTimeStamp
  ,Auto_Manual
  ,ROW_NUMBER() over (partition by Lot, Auto_Manual 
  order by DateTimeStamp) rn
  from dbo.Table )  tmp
  order by Lot, DateTimeStamp

我能够为 Auto_Manual = 0 或 1 选择 rn = 1 的行，但这不是我要做的。我只想要第一行，其中Auto_Manual列中有 4 个连续的"0"。请注意，另一种方法是找到第一个Auto_Manual值"0"，而 8 分钟后该值仍然是"0"。这仅在两者之间只有"0"时才有效......

任何帮助将不胜感激。

谢谢

Answer 1

这有点丑，但它应该可以工作：

select * from (
    select 
        *, last_value(RN) over (partition by GRP order by DateTimeStamp RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as LASTVAL, 
        row_number() over (partition by GRP order by DateTimeStamp) as RN2
    from (
    select *, RN - row_number() over (order by DateTimeStamp asc) GRP
        from (
            select
              *, row_number() over (order by DateTimeStamp asc) as RN
            from Table1
        ) X where Auto_Manual = 0
    ) Y 
) Z where LASTVAL >= RN+3 and RN2 = 1
order by DateTimeStamp

首先对所有行

进行编号，然后仅筛选那些具有 0 的行。这些行再次编号，数字之间的差异用于对它们进行分组。如果差异保持不变，则它们属于连续组。

然后用last_value处理此组，如果最后一个值是当前行 + 3 或更大，则至少有 4 行，并且还有一个行号用于选择该组的第一个行号。

对于 2008 年，您可以执行以下操作：

select min(DateTimeStamp)
from (
    select *, RN - row_number() over (order by DateTimeStamp asc) GRP
    from (
        select
          *, row_number() over (order by DateTimeStamp asc) as RN
        from Table1
    ) X where Auto_Manual = 0
) Y
group by GRP
having count(*) >= 4