Calculate time between On and Off Status rows SQL

问题:

I´m using SQL Server 2008, how can I calculate the time between On and Off status? I have the following table (ordered by timestamp):

ID | EQUIP_ID | TIMESTAMP           | STATUS (1 on/0 off)
1  |     1    | 21/05/2012 13:00:00 |   1
3  |     1    | 21/05/2012 13:04:00 |   1
4  |     1    | 21/05/2012 13:05:00 |   0
6  |     1    | 21/05/2012 13:09:00 |   1
7  |     1    | 21/05/2012 13:10:00 |   1
9  |     1    | 21/05/2012 13:12:00 |   1
10 |     1    | 21/05/2012 13:13:00 |   0
10 |     1    | 21/05/2012 13:14:00 |   1
10 |     1    | 21/05/2012 13:15:00 |   0

And I expect a result like this:

EQUIP_ID |    START             |          END           | STATUS
    1    | 21/05/2012 13:00:00  |   21/05/2012 13:05:00  |   1       (WORKING)
    1    | 21/05/2012 13:05:00  |   21/05/2012 13:09:00  |   0       (STOPPED)
    1    | 21/05/2012 13:09:00  |   21/05/2012 13:13:00  |   1
    1    | 21/05/2012 13:13:00  |   21/05/2012 13:14:00  |   0
    1    | 21/05/2012 13:14:00  |   21/05/2012 13:15:00  |   1

I've tried some functions for gaps and islands but didn't work and I don't know what I'm missing...

回答1:

Here's my take on it. Assuming your table is called "MyData":

WITH operating AS
(
    SELECT
        d.EQUIP_ID
        , d.[TIMESTAMP]
        , d.[STATUS]
        , ROW_NUMBER() OVER (PARTITION BY EQUIP_ID ORDER BY [TIMESTAMP]) RowNum
        , ROW_NUMBER() OVER (PARTITION BY EQUIP_ID ORDER BY [TIMESTAMP]) -
            ROW_NUMBER() OVER (PARTITION BY EQUIP_ID, [STATUS] ORDER BY [TIMESTAMP]) AS [Group]
    FROM 
        MyData d
)
SELECT 
    state1.EQUIP_ID
    , MIN(state1.[TIMESTAMP]) [START]
    , MAX(state2.[TIMESTAMP]) [END]
    , state1.STATUS 
FROM 
    operating state1
LEFT JOIN
    operating state2 
    ON 
    state1.RowNum = state2.RowNum - 1
WHERE
    state2.[TIMESTAMP] IS NOT NULL
GROUP BY  
    state1.EQUIP_ID, state1.[STATUS], state1.[Group]
ORDER BY 
    MIN(state1.[TIMESTAMP])

It makes use of the ROW_NUMBER() function to determine changes in statuses for each EQUIP_ID. Then it simply finds when a status started (MIN([TIMESTAMP])), then I match it up with the time it ended (MAX([TIMESTAMP])) in the next row (see the self-join on RowNum). The WHERE eliminates the last row which has no end time. The results I get are:

EQUIP_ID | START                   | END                     | STATUS 
---------+-------------------------+-------------------------+-------
       1 | 2012-05-21 13:00:00.000 | 2012-05-21 13:05:00.000 |      1
       1 | 2012-05-21 13:05:00.000 | 2012-05-21 13:09:00.000 |      0
       1 | 2012-05-21 13:09:00.000 | 2012-05-21 13:13:00.000 |      1
       1 | 2012-05-21 13:13:00.000 | 2012-05-21 13:14:00.000 |      0
       1 | 2012-05-21 13:14:00.000 | 2012-05-21 13:15:00.000 |      1

回答2:

This approach first filters out the repeated on and repeated off rows. With only status switches left, the "end time" can be retrieved by searching for the next row based on row_number.

; with  numbered as
        (
        select  row_number() over (partition by equip_id order by timestamp) rn
        ,       *
        from    YourTable
        )
,       nodups as
        (
        select  row_number() over (partition by cur.equip_id order by cur.timestamp) rn
        ,       cur.equip_id
        ,       cur.timestamp
        ,       cur.status
        from    numbered cur
        left join    
                numbered prev
        on      cur.rn = prev.rn + 1
                and cur.status = prev.status
        where   prev.id is null
        )
select  cur.rn
,       cur.equip_id
,       cur.timestamp as StartTime
,       next.timestamp as EndTime
,       cur.status
from    nodups cur
left join    
        nodups next
on      next.rn = cur.rn + 1
        and next.equip_id = cur.equip_id

Live example at SQL Fiddle.