【SQL】大工程：模拟工具故障的时间序列（分平稳期和衰减期）

/********************************

                1、建各种临时表

*********************************/
IF OBJECT_ID('tempdb..#TongsEntity') IS NOT NULL DROP TABLE #TongsEntity;
IF OBJECT_ID('tempdb..#EntityErrorSeries') IS NOT NULL DROP TABLE #EntityErrorSeries;

CREATE TABLE #EntityErrorSeries(--创建实体故障时间表
    Code varchar(10) not null,
    SeqID int not null,
    GEN int not null,
    Times datetime2 not null,
    Event varchar(5) not null,
    ErrorCnt int not null 
    )
    
SELECT ROW_NUMBER() OVER ( ORDER BY ID ) i,* INTO #TongsEntity FROM [TongsEntity];-- 为实体表创建临时表，分配ID以便于枚举

/********************************

    2、程序主要部分:
                [循环嵌套]
                    第一层:枚举实体
                    第二层:产生时间序列

*********************************/

/*
    初始化：定义及赋值各变量
*/

DECLARE @firstdate datetime, @lastdate datetime, @DD int;
DECLARE @Code varchar(10),@SeqID INT,@GEN INT;
DECLARE @ErrorCnt INT;
SELECT @ErrorCnt=COUNT(1) FROM #TongsEntity; --统计#TongsEntity行数
DECLARE @i INT=1; 
DECLARE @rand INT;  --产生随机数
DECLARE @tt INT;
DECLARE @ErrorCount INT;
DECLARE @alpha FLOAT;--故障衰减期的衰减率
/*
    循环主体
*/
SET NOCOUNT ON; --不返回计数[开]（提高效率）

WHILE(@i<=@ErrorCnt)--待替换成@ErrorCnt
BEGIN


    SELECT @Code=Code,@SeqID=SeqID,@GEN=GEN FROM #TongsEntity WHERE i=@i;--获取实体的基本信息(代码、序列号)
    --初始化时间起始值
    SELECT @firstdate=(SELECT jbuyoff FROM [GMSAudit].[dbo].[Warehouse] WHERE Code=@Code AND SeqID=@SeqID AND GEN=@GEN);
    
    SELECT @tt=COUNT(*) FROM [GMSAudit].[dbo].[Scrap] WHERE Code=@Code AND SeqID=@SeqID AND GEN=@GEN;--判断是否报废
    
    SELECT @lastdate =(
        CASE @tt
        WHEN 0 THEN '5/23/2020 23:59'--未报废则截止到仿真结束
        WHEN 1 THEN (SELECT ScrapDate FROM [GMSAudit].[dbo].[Scrap] WHERE Code=@Code AND SeqID=@SeqID AND GEN=@GEN)
        END
        );
        
    --嵌套循环
    SET @DD=DATEDIFF(DD,@firstdate,@lastdate)--差别精确到天
    
    WHILE(@DD>=0)
    BEGIN
        SET @ErrorCount= (
            CASE 
            WHEN EXISTS((SELECT * FROM #EntityErrorSeries WHERE Code=@Code AND SeqID=@SeqID AND GEN=@GEN)) 
                THEN ((SELECT MAX(ErrorCnt) FROM #EntityErrorSeries WHERE Code=@Code AND SeqID=@SeqID AND GEN=@GEN))
            ELSE 0 
            END
            )+1;
        IF @ErrorCount>4 SET @alpha=(1-(@ErrorCount-4)*0.0233333); --设置衰减率
        SELECT @rand =
        (
            CASE WHEN @ErrorCount<=4 THEN(
                    CASE (SELECT PMType FROM TongsDefinition T1 WHERE T1.Code = @Code)
                        WHEN 1 THEN ROUND(((35-25)* RAND()+25), 0)
                        WHEN 2 THEN ROUND(((55-45)* RAND()+45), 0)
                        WHEN 3 THEN ROUND(((65-55)* RAND()+55), 0)
                        WHEN 4 THEN ROUND(((75-65)* RAND()+65), 0)
                    END)
            ELSE(
                    CASE (SELECT PMType FROM TongsDefinition T1 WHERE T1.Code = @Code)
                        WHEN 1 THEN (ROUND(((35-25)* RAND()+25), 0)*@alpha)
                        WHEN 2 THEN (ROUND(((55-45)* RAND()+45), 0)*@alpha)
                        WHEN 3 THEN (ROUND(((65-55)* RAND()+55), 0)*@alpha)
                        WHEN 4 THEN (ROUND(((75-65)* RAND()+65), 0)*@alpha)
                    END)
            END
        );

        SET @firstdate=DATEADD(DD,@rand,@firstdate);--完好使用时间1：增加[20,40]天（PM类型为'15天'）
        
        SELECT @rand = ROUND(((24-1)* RAND()+1), 0);
        SET @firstdate=DATEADD(HH, @rand, @firstdate);--完好使用时间2：增加[1,24](小时)
        
        --故障申请
        SET @DD=datediff(DD,@firstdate,@lastdate);
        IF @DD<0 BREAK;--当@firstdate增加至大于@lastdate的天数时，DD<0，退出循环
        INSERT INTO #EntityErrorSeries VALUES(@Code,@SeqID,@GEN,@firstdate,'040',@ErrorCount);
        SELECT @rand = ROUND(((24-1)* RAND()+1), 0);
        SET @firstdate=DATEADD(HH, @rand, @firstdate);--审核时间：增加[1,24](小时)
        

        --故障审核
        SET @DD=datediff(DD,@firstdate,@lastdate);
        IF @DD<0 BREAK;
        INSERT INTO #EntityErrorSeries VALUES(@Code,@SeqID,@GEN,@firstdate,'041',@ErrorCount);
        SELECT @rand = ROUND(((4-2)* RAND()+2), 0);
        SET @firstdate=DATEADD(DD,1,@firstdate)--维修时间1：增加[2,4]（天）
        SELECT @rand = ROUND(((24-1)* RAND()+1), 0);
        SET @firstdate=DATEADD(HH, @rand, @firstdate);--维修时间2：增加[1,24](小时)
        
      --故障修复完成
        SET @DD=datediff(DD,@firstdate,@lastdate);
        IF @DD<0 BREAK;
        INSERT INTO #EntityErrorSeries VALUES(@Code,@SeqID,@GEN,@firstdate,'042',@ErrorCount);
        
    END
    SET @i=@i+1;
END



SELECT ROW_NUMBER() OVER(ORDER BY Times) AS id, *,  CONVERT(varchar(10),Times, 120 ) AS 日期 
FROM #EntityErrorSeries 
--WHERE Code='EF0798' AND SeqID=1 AND GEN=1
ORDER BY Times ASC


SET NOCOUNT OFF --不返回计数[关]

/********************************

                3、删除临时表

*********************************/
IF OBJECT_ID('tempdb..#TongsEntity') IS NOT NULL DROP TABLE #TongsEntity;
IF OBJECT_ID('tempdb..#EntityErrorSeries') IS NOT NULL DROP TABLE #EntityErrorSeries;