问一个如何判断数据是否连续的sql

[lighting_pig]

系统自动取数据,假设1分钟取一次. 如果正常,那么取的数据每次都会不一样.至少过几分钟会变一次.
如果仪表故障,那么可能连续几十个甚至上百个数都是一样的.我们姑且假设连续相同的值超过20个算异常.
复杂的情况是某个id先死机1小时,连续出现60个相同的数,然后正常3个小时后,又死了2小时之类的情况.
我想判断统计昨天每个id异常连续次数 sql如何写?


CREATE TABLE [dbo].[Web_HistoryData](
[NodeID] [int] NOT NULL,
[DateTime] [datetime2](7) NOT NULL,
[Value] [decimal](18, 4) NULL,
)


declare @dt datetime;
set @dt='2018-08-27 00:00:00';
declare @i int
set @i=0
while @i<1440
begin
set @dt=DATEADD(MI,1,@dt)
INSERT INTO [Web_HistoryData] ([NodeID] ,[DateTime] ,[Value]) VALUES (1,@dt,7.33);
INSERT INTO [Web_HistoryData] ([NodeID] ,[DateTime] ,[Value]) VALUES (2,@dt,13.43);
set @i=@i +1
end

update [Web_HistoryData] set value=6.67 where [DateTime]='2018-08-27 00:01:00' and nodeid=1;

update [Web_HistoryData] set value=7.67 where [DateTime]='2018-08-27 01:10:00' and nodeid=2;

update [Web_HistoryData] set value=7.89 where [DateTime]='2018-08-27 03:50:00' and nodeid=1;

update [Web_HistoryData] set value=7.39 where [DateTime]='2018-08-27 13:50:00' and nodeid=2;

update [Web_HistoryData] set value=7.49 where [DateTime]='2018-08-27 19:50:00' and nodeid=1;


示例数据大约如上.

[砸死牛顿的苹果]

declare @t datetime ='2018-08-27'
;with t1 as (select nodeid,[datetime],n1=datediff(mi,@t,[datetime]),n2 =row_number()over(partition by nodeid,value order by [datetime]),[value] from Web_HistoryData)
,t2 as (select nodeid,value,min(n1)r1,max(n1)r2,count(1)ct from t1 group by nodeid,[value],n1-n2 having count(1)>=20)
select nodeid,dateadd(mi,r1,@t)dt_start,dateadd(mi,r2,@t)dt_end,ct from t2
/*nodeid	dt_start	dt_end	ct
1	2018-08-27 00:02:00.000	2018-08-27 03:49:00.000	228
1	2018-08-27 03:51:00.000	2018-08-27 19:49:00.000	959
1	2018-08-27 19:51:00.000	2018-08-28 00:00:00.000	250
2	2018-08-27 00:01:00.000	2018-08-27 01:09:00.000	69
2	2018-08-27 01:11:00.000	2018-08-27 13:49:00.000	759
2	2018-08-27 13:51:00.000	2018-08-28 00:00:00.000	610
*/

[RINK_1]

with cte_1
as
(select *,row_number() over (order by datetime) as rn_1 from Web_HistoryData),

cte_2
as
(select *,row_number() over (partition by NodeID,Value order by DateTime) as rn_2 from cte_2)

select NodeID,rn_1-rn_2,count(*) as frequency 
from cte_2
group by NodeID,rn_1-rn_2

[qq_25073223]

这里主要采用:
1.row_number 编号
2.对编号后的表，进行互补左连接比对，我认为这样编写sql脚本，会更加清晰
请查看数据是否连续的检测方法

[lighting_pig]

谢谢各位,问题已经解决