SQL Server 查询构建

Question

我在 SQL Server 数据库中有一组数据，我需要计算机器的正常运行时间。我使用两个变量来确定正常运行时间或停机时间。这两个变量是 machine_ON 和 failure(s)。 machine_ON只是数据库中的一个变量，failure可以是64种不同的故障，但都表示为fx_x。

这些变量的状态信息在数据库中存储如下:

timestamp                   failurebitNr    timestampOutOfAlarm
2012-01-17 10:38:58.000     f1_14           2012-01-17 10:39:05.000

含义:故障 f1_14 从 2012-01-17 10:38:58.000 一直活跃到 2012-01-17 10:39:05.000

machine_ON 状态也以相同的方式保存在同一个表中，只有 failurebitNr 具有不同的值 [t2_13]。

因此，为了确定正常运行时间，我需要获取 timestamp 和 timestampOutOfAlarm 之间的时间差，其中 failurebutNr = 't2_13' 减去任何故障时间.

例如，我的数据库中有这些行:

这应该给出以下图形表示:

绿色是正常运行时间，红色是停机时间。

我习惯于使用 PHP 而不是使用 while 循环并归档一些数组并执行其他脚本。但现在我需要在 SQL Server 数据库中以查询方式完成所有这些...

那么，如何计算正常运行时间(绿色)和停机时间(红色)？

更新

我试图获取机器开启的时间(以秒为单位)。我使用了这个查询:

<!-- language: lang-sql -->
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @projectNr int
DECLARE @MachineNr nvarchar(10) 

SET @startDate = '2012-01-01 00:00:00.000'
SET @endDate  = '2012-02-01 00:00:00.000'
SET @projectNr = '1234567'
SET @MachineNr = '2'

SELECT 
    DATEDIFF("SECOND",
        CASE WHEN timestamp < @startDate 
            THEN @startDate 
            ELSE timestamp 
        END, 
        CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL 
            THEN @endDate
            ELSE timestampOutOfAlarm 
        END) AS Uptime
FROM 
    [table]
WHERE
     timestamp < @endDate
     AND (timestampOutOfAlarm > @startDate OR timestampOutOfAlarm IS NULL)
     AND fileProjectNr = @projectNr
     AND fileProjectMachineNr = @MachineNr
     AND (failureBitNr = 't2_13' AND failureBitValue = '1')

Answer 1

问题解决:

我在 2 个存储过程或查询中完成了它。一种用于获得“ON”时间，一种用于获得“ON”时间内的所有“DOWN”时间。通过这两次，我可以计算正常运行时间、停机时间和维护时间。

ON时间查询:

<!-- language: lang-sql -->
SELECT  fctAlarmId,
        fileProjectNr, 
        fileProjectMachineNr, 
        failureBitNr,   
        timestamp, 
        CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL 
            THEN @endDate
            ELSE timestampOutOfAlarm
        END 
        AS timestampOutOfAlarm
INTO    #tempTable1
FROM    fctAlarmHistory
WHERE   (timestampOutOfAlarm >= @startDate OR timestampOutOfAlarm = NULL) 
        AND timestamp < @endDate
        AND failureBitValue = 1 
        AND failureBitNr = 't2_13'
        AND fileProjectNr = @projectNr
        And fileProjectMachineNr = @ProjectMachineNr

-- SUM the result of all ON times into OnTime in seconds
SELECT 
    SUM(DATEDIFF("SECOND",
        CASE WHEN timestamp < @startDate 
            THEN @startDate 
            ELSE timestamp 
        END, 
        CASE WHEN timestampOutOfAlarm > @endDate
            THEN @endDate 
            ELSE timestampOutOfAlarm 
        END)) AS OnTime
FROM
    #tempTable1

查询开启期间的停机时间:

<!-- language: lang-sql -->
SELECT  fctAlarmId,
        fileProjectNr, 
        fileProjectMachineNr, 
        failureBitNr,   
        timestamp, 
        CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL 
            THEN @endDate
            ELSE timestampOutOfAlarm
        END 
        AS timestampOutOfAlarm
INTO    #tempTable1
FROM    fctAlarmHistory
WHERE   (timestampOutOfAlarm >= @startDate OR timestampOutOfAlarm = NULL) 
        AND timestamp < @endDate
        AND failureBitValue = 1 
        AND failureBitNr = 't2_13'
        AND fileProjectNr = @projectNr
        And fileProjectMachineNr = @ProjectMachineNr

SELECT  fctAlarmId,
        fileProjectNr, 
        fileProjectMachineNr, 
        failureBitNr,   
        timestamp, 
        CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL 
            THEN @endDate
            ELSE timestampOutOfAlarm
        END 
        AS timestampOutOfAlarm
INTO    #tempTable2
FROM    fctAlarmHistory
WHERE   (timestamp BETWEEN @startDate AND @endDate) 
        AND failureBitValue = 1
        AND (failureBitNr LIKE'f%')
        AND fileProjectNr = @projectNr
        And fileProjectMachineNr = @ProjectMachineNr

CREATE TABLE #tempTable3
(
ID int IDENTITY(1,1),
timestamp datetime,
timestampOutOfAlarm datetime
)

DECLARE failure_Cursor CURSOR FOR
SELECT timestamp, timestampOutOfAlarm
FROM #tempTable2
ORDER BY timestamp ASC

OPEN failure_Cursor

-- Perform the first fetch.
FETCH NEXT FROM failure_Cursor
INTO @rij_timestamp, @rij_timestampOutOfAlarm

INSERT INTO #tempTable3 (timestamp, timestampOutOfAlarm) VALUES(@rij_timestamp,@rij_timestampOutOfAlarm)

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @rij_timestamp
        IF @rij_timestamp <= (SELECT TOP 1 timestampOutOfAlarm FROM #tempTable3 ORDER BY timestamp DESC)
        BEGIN   
            IF @rij_timestampOutOfAlarm > (SELECT TOP 1 timestampOutOfAlarm FROM #tempTable3 ORDER BY timestamp DESC)
            BEGIN
                UPDATE #tempTable3 SET timestampOutOfAlarm = @rij_timestampOutOfAlarm WHERE ID = (SELECT TOP 1 ID FROM #tempTable3 ORDER BY timestamp DESC)

            END
        END
        ELSE
            INSERT INTO #tempTable3 (timestamp, timestampOutOfAlarm) VALUES(@rij_timestamp,@rij_timestampOutOfAlarm)

        FETCH NEXT FROM failure_Cursor
        INTO @rij_timestamp, @rij_timestampOutOfAlarm
    END
CLOSE failure_Cursor
DEALLOCATE failure_Cursor

-- Select the failure time.
SELECT 
    SUM(DATEDIFF("SECOND",
        CASE WHEN tt3.timestamp < @startDate 
            THEN @startDate 
            ELSE tt3.timestamp 
        END, 
        CASE WHEN tt3.timestampOutOfAlarm > tt1.timestampOutOfAlarm 
            THEN tt1.timestampOutOfAlarm 
            ELSE tt3.timestampOutOfAlarm 
        END)) AS DownTime
FROM
    #tempTable3 tt3
INNER JOIN
    #tempTable1 tt1
ON
    tt3.timestamp BETWEEN tt1.timestamp AND tt1.timestampOutOfAlarm