MSSQL 查询统计某状态出现的次数及累计时间

1、问题来源

最近客户需要统计某个设备，某状态，在某一个时间段内出现的次数，并计算累计出现的时间。

数据源如下：

现在如果要统计UPSCTSTA状态为D出现的次数（同一状态，连续出现的认为是一次），并计算累计出现的时间

 

解决方法

方案1，采用存储过程

CREATE PROCEDURE [dbo].[Proc_UPSState_Statistics]
     @Guid VARCHAR(32),
     @State VARCHAR(2),
     @STime DATETIME,
     @ETime DATETIME,
     @R_C INT OUTPUT,
     @R_S INT OUTPUT
   
AS
BEGIN    
    SET NOCOUNT ON;
    SELECT IDENTITY(INT,1,1) AS ID,Guid,UPSCTState,RecordTime INTO #TSTE FROM [dbo].[EUPSDeviceRunRecordLog] WHERE  Guid = @Guid AND RecordTime >= @STime AND RecordTime <= @ETime  ORDER BY RecordTime
    DECLARE @R_Count INT,@RS INT,@State_Count INT  --记录总数，累计时长，累计次数
    SELECT @RS = 0,@State_Count = 0, @R_S = 0, @R_C = 0 --给统计变量，输出返回值给初始化为0
    SELECT @R_Count = COUNT(*) FROM #TSTE    
    PRINT '符合条件记录：' + STR(@R_Count)
    IF(@R_Count < 1) --少于两条记录不预统计
        BEGIN
            --SELECT @State_Count AS Times,@RS AS Count
            PRINT '在指定时间段内未能查询 ' + @Guid + ' 设备的状态 ' + @State + ' 的记录'
        END
    ELSE BEGIN
        DECLARE @I INT --计数器，步长计数器
        DECLARE @ST VARCHAR(2)   --临时状态
        DECLARE @RTime DATETIME,@LTime DATETIME,@DF DATETIME         --当前记录的记录时间、最后一次（上条）此状态记录的时间、时间常量
        SELECT @DF =  '1979-01-01 0:01:01', @LTime = @DF   
       
        DECLARE @ST_FLAG INT --符合条件状态记录开始，结果标记，用于统计次数
        SELECT @I = 1, @ST_FLAG = 0

        WHILE(@I <= @R_Count)
            BEGIN
                SELECT @ST = UPSCTState,@RTime = RecordTime FROM #TSTE WHERE ID = @I
                IF(@ST = @State)                   
                    IF(@LTime = @DF)
                        BEGIN
                            SET @LTime = @RTime
                            SET @ST_FLAG = 1
                        END
                    ELSE
                        BEGIN
                            SET @RS = @RS + DATEDIFF(S,@LTime,@RTime)
                            SET @LTime = @RTime
                        END                   
                ELSE
                    BEGIN
                        SET @LTime = @DF
                        IF(@ST_FLAG = 1)
                            BEGIN
                                SET @State_Count  = @State_Count + 1
                                SET @ST_FLAG = 0
                            END
                    END
                SET @I = @I + 1
            END

         SELECT @R_C = @State_Count,@R_S = @RS
         --SELECT @State_Count AS Times, @RS AS Count
    END
END

 

执行结果

从1万多条记录中，统计耗时12秒左右.

虽然可以统计出来，但在统计数据较多时，等待的时间可想而知，有没有方法将其优化一下呢？