计算打开和关闭状态行之间的时间SQL Server [英] Calculate time between On and Off Status rows SQL Server

问题描述

我正在使用SQL Server 2008，如何计算开和关状态之间的时间?我有下表(按timestamp排序):

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...

推荐答案

这是我的看法.假设您的表名为"MyData":

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])

它利用ROW_NUMBER()功能来确定每个EQUIP_ID的状态变化.然后，它仅查找状态何时开始(MIN([TIMESTAMP]))，然后在下一行中将其与状态结束的时间(MAX([TIMESTAMP]))相匹配(请参见RowNum上的自联接). WHERE消除没有结束时间的最后一行.我得到的结果是:

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

这篇关于计算打开和关闭状态行之间的时间SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！