MES系统需求连续出现5次FAIL记录预警--SQL SERVER

用户提出了一个需求，生产过程中同一个订单在某个工序连续出现5次FAIL记录需要预警显示。工厂数据生产情况如下：

1、一个订单批量大可能会连续一周生产。

2、一个订单测试工序会使用多台测试设备。

3、不同订单、不同工序、不同设备等的生产数据实时写入表。

解决思路：1、查询最近七天生产的订单号，游标遍历订单号获取生产数据，存放到临时表。

2、对获取到的数据进行分组排序，订单号、工序、测试设备等分组，按时间先后排序。

3、新增ID1赋值按2的排序自增，新增ID2对FAIL和PASS排序，ID1-ID2赋值给新增的ID3，连续的ID差，即等差数列。

/*
Leo
2023/06/23
FCCD连续5次FAIL记录，预警
*/
use [数据库]
go

IF (exists (select * from sys.objects where name = 'NGSN_cursor'))
    DROP PROCEDURE NGSN_cursor
GO
CREATE PROCEDURE NGSN_cursor 
AS
DECLARE @MO char(15)         --设置变量MO，订单号

--声明游标 ，查找近7天的MO
declare MO_cursor cursor for  
        select distinct(MO) from [数据库].[dbo].[表] with(nolock)
             WHERE StartTime BETWEEN DATEADD(DD, -7, GETDATE()) AND GETDATE()
                   AND Operation = 'FCCD'                                --用户需求的工序
                   and isnull(Segment3,'')=''                                 --记录未抓取数据、标识字段
 
    
    open MO_cursor
    fetch next from MO_cursor into @MO
    while(@@FETCH_STATUS=0)

    begin 

            WITH ctea        --临时表ctea,存放数据
            AS (SELECT ROW_NUMBER() OVER (ORDER BY StartTime) AS ID,
                        ID as oldID,
                       SerialNumber AS SN,
                       MO AS MO,
                       StartTime AS InTime,
                       Result AS Result,
                       Line,
                       SwName,
                       SwRevision,
                       Segment1                --预警、停线标识
                  FROM [数据库].[dbo].[表] with(nolock)
                 WHERE mo=@MO
                       AND Operation = 'FCCD'),

                 cteb        --临时表cteb，res=ID-ID1，若连续，则为等差数列
            AS  (SELECT ROW_NUMBER() OVER (PARTITION BY ctea.Result ORDER BY ctea.InTime) AS ID1,
                       ID - ROW_NUMBER() OVER (PARTITION BY ctea.Result ORDER BY ctea.InTime) res,
                       *
                  FROM ctea),

                ctec    --临时表ctec，剔除等差数量的重复SN
                AS (select MO,SN,res,count(res) as DIS_SN from cteb
                        where MO=@MO
                        and Result='FAIL'
                        group by MO,SN,res)

                        --查询连续Fail>=5PCBA的SN数据
                select  a.MO,b.SN,a.res,
                ROW_NUMBER() OVER (PARTITION BY a.MO ORDER BY a.MO) AS ID,
                COUNT(a.res) AS NGSN_count
                from ctec a left join ctec b on a.MO=b.MO and a.res=b.res
                group by a.MO,b.SN,a.res
                having COUNT(a.res)>=5

    

    fetch next from MO_cursor into @MO        --循环下一个MO
    end

    close MO_cursor                    

    deallocate MO_cursor           
    go

EXEC proc_mo_cursor
GO

-------------------------------------------------------------------------------------------------------------------------------- 

--执行查询存放到临时表

use CmDataCenter
go
DECLARE  @MO_SN table(MO char(15),SN char(50),res int,ID bigint,NGSN_count int)
            insert into @MO_SN(MO,SN,res,ID,NGSN_count)
            EXEC NGSN_cursor

select * from @MO_SN

---------------------------------------------------------------------------------------------------------------------------------

记录和分享，有不同见解和修改欢迎评论，谢谢！