在SQL中计算机器状态时间

Question

我有一个简单的SQL express服务器设置，并将数据记录到它，我有多台机器(工业机械)我正在监控。每当机器状态改变时，我都会创建一条新记录，在以下列中输入数据。运行、空闲和停机的时间(时间戳)、机器名称(文本)、状态(1、2或3)和ReasonCode (1-10)。我需要计算机器处于每种状态的时间，以及每种状态的原因。我想通过SQL报告来实现这一点。这是目前许多工业制造商都希望实现的功能，我正在尝试创建一个简单的示例。不幸的是，我并不精通SQL。我想这可以放在一个存储过程中，每隔n秒运行一次来重新计算。任何帮助都将不胜感激。

Answer 1

假设一个名为status的表包含

MachineName NVarChar(whatever)
Status Int
Reason Int
Time DateTime

那么这个查询应该可以很好地工作

Select 
    st.MachineName,
    st.status,
    st.reason
    st.Time as TimeChanged,
    DateDiff(ss, min(dur.Time), st.Time)
From
    Status st inner join
    Status dur on st.MachineName = dur.MachineName and st.Time <dur.Time
group by
    st.MachineName,
    St.Status,
    st.Time,
    st.reason

编辑-回复您的评论

select
    st.MachineName,
    sum(case when status=1 then Duration else 0 end) as RunningTime,
    sum(case when status=2 then Duration else 0 end) as IdleTime,
    sum(case when status=3 then Duration else 0 end) as DownTime,
From
    (Select 
        st.MachineName,
        st.status,
        st.reason
        st.Time as TimeChanged,
        DateDiff(ss, min(dur.Time), st.Time) as Duration
    From
        Status st inner join
        Status dur on st.MachineName = dur.MachineName and st.Time <dur.Time
    group by
        st.MachineName,
        St.Status,
        st.Time,
        st.reason) as foo
Group by MachineName

它应该获取机器名称和在每个状态下花费的时间列

Answer 2

你不需要重新计算。您可以只按需查询信息。下面是一个自包含的示例

DECLARE @Test 
Table (
    LogTime datetime, 
    MachineName varchar(100),
     Status int , 
     ReasonCode  int)

INSERT INTO @Test VALUES ('01/01/2011 4:19:11.459'  ,   'ServerX',  1,  3)
INSERT INTO @Test VALUES ('01/02/2011 3:43:03.652'  ,   'ServerZ',  0,  4)  
INSERT INTO @Test VALUES ('02/04/2011 11:17:51.827'  ,  'ServerX',  2,  2)  
INSERT INTO @Test VALUES ('02/05/2011 4:22:22.205'  ,   'ServerX',  3,  1)  
INSERT INTO @Test VALUES ('01/03/2011 11:42:44.211'  ,  'ServerZ',  1,  4)  



;with TIMEdelta AS (
SELECT 
    machineName,
    t.LogTime,
    MIN(nextTime) as nextTime 
FROM (
    Select 
        t.MachineName,
        t.LogTime, 

        t1.LogTime nextTime
    from @Test t
        INNER JOIN @Test t1
        ON t.machineName = t1.machineName
        AND t.LogTime < t1.LogTime

    ) t

GROUP BY
    machineName,

    t.LogTime

)

SELECT 
    t.MachineName,
    t.LogTime,
    t.ReasonCode,
    t.Status,
DateDiff(DAY, 0, (TIMEdelta.nextTime - TIMEdelta.LogTime) ) Days,
DatePart(HOUR , TIMEdelta.nextTime - TIMEdelta.LogTime ) Hour,
DatePart(MINUTE, TIMEdelta.nextTime - TIMEdelta.LogTime ) MInute

FROM 

    @Test t
    LEFT JOIN TIMEdelta
    ON t.LogTime = timedelta.logtime
        and t.MachineName = TIMEdelta.MachineName

下面的输出

MachineName LogTime                 ReasonCode  Status      Days        Hours       Minutes
----------- ----------------------- ----------- ----------- ----------- ----------- -----------
ServerX     2011-01-01 04:19:11.460 3           1           34          6           58
ServerZ     2011-01-02 03:43:03.653 4           0           1           7           59
ServerX     2011-02-04 11:17:51.827 2           2           0           17          4
ServerX     2011-02-05 04:22:22.207 1           3           NULL        NULL        NULL
ServerZ     2011-01-03 11:42:44.210 4           1           NULL        NULL        NULL

您可以修改输出以显示当前时间- logtime，而不是那些当前状态行的Null

更新透视的聚合您可以使用pivot

  SELECT
 MachineName,
 [0] as Started,
 [1] as Stopped,
 [2] as Paused,
 [3] as Foo

FROM
(
SELECT 
    t.MachineName,
    t.status,
    cast(TimeDelta.nextTime - t.LogTime as DECIMAL(18,10)) duration

FROM 

    @Test t
    LEFT JOIN TIMEdelta
    ON t.LogTime = timedelta.logtime
    and t.MachineName = TIMEdelta.MachineName
) source
PIVOT
(
SUM(duration)
FOR status IN ([0], [1], [2], [3])
) AS PivotTable;

，它的输出如下

MachineName Started       Stopped        Paused       Foo
----------- ------------  -------------- ------------ ------------
ServerX     NULL          34.2907449846  0.7114627315 1.0000000000
ServerZ     1.3331082948  NULL           NULL         ULL

更新使用跨月和更新天数计算的日期