sql - 如何确定数据集中的开始和结束事件？

Question

我的数据集中有很多事件，我想从另一个角度查看数据。这就是为什么我需要指定开始和结束时刻，但是如何呢？

我已经尝试确定手动和自动的 MIN 和 MAX 时刻。但是我如何确定其他开始和结束时刻？

UPDATE Test_Table
    SET Activity = 'Start Manual'
    FROM Test_Table b, 
    (
        SELECT ID,  MIN(rownumber) as a FROM Test_Table
        WHERE Activity = 'Manual'
        GROUP BY ID) a
    WHERE   b.rownumber <= a.a AND Activity = 'Manual'
    and b.ID = a.ID

UPDATE Test_Table
    SET Activity = 'End Manual'
    FROM Test_Table b, 
    (
        SELECT ID,  MAX(rownumber) as a FROM Test_Table
        WHERE Activity = 'Manual'
        GROUP BY ID) a
    WHERE   b.rownumber >= a.a AND Activity = 'Manual'
    and b.ID = a.ID

UPDATE Test_Table
    SET Activity = 'Start Automatic'
    FROM Test_Table b, 
    (
        SELECT ID,  MIN(rownumber) as a FROM Test_Table
        WHERE Activity = 'Automatic'
        GROUP BY ID) a
    WHERE   b.rownumber <= a.a AND Activity = 'Automatic'
    and b.ID = a.ID

UPDATE Test_Table
    SET Activity = 'End Automatic'
    FROM Test_Table b, 
    (
        SELECT ID,  MAX(rownumber) as a FROM Test_Table
        WHERE Activity = 'Automatic'
        GROUP BY ID) a
    WHERE   b.rownumber >= a.a AND Activity = 'Automatic'
    and b.ID = a.ID

这是表的值的示例:

ID  Activity    Datetime        Rownumber        Type_Activity
A   Manual  2019-04-23 11:17:12     1            0
A   Manual  2019-04-23 11:17:57     2            0
A   Manual  2019-04-23 11:18:53     3            0
A   Manual  2019-04-23 11:19:49     4            0
A   Automatic 2019-04-23 11:26:50   5            1
A   Automatic 2019-04-23 11:42:30   6            1
A   Automatic 2019-04-23 11:43:31   7            1
A   Automatic 2019-04-23 11:44:32   8            1
A   Manual  2019-04-24 03:35:06     9            0
A   Manual  2019-04-24 03:36:01     10           0
A   Manual  2019-04-24 03:36:57     11           0
A   Manual  2019-04-24 03:37:48     12           0
A   Manual  2019-04-24 03:38:44     13           0
A   Manual  2019-04-24 03:39:40     14           0
A   Manual  2019-04-24 03:40:46     15           0
A   Manual  2019-04-24 03:41:52     16           0
A   Automatic 2019-04-24 04:26:50   17           1
A   Automatic 2019-04-24 04:42:30   18           1
A   Automatic 2019-04-24 04:43:31   19           1
A   Automatic 2019-04-24 04:44:32   20           1

我期望以下内容:

ID  Activity    Datetime                    Activity2
A   Manual  2019-04-23 11:17:12.000         Start Manual
A   Manual  2019-04-23 11:17:57.000 
A   Manual  2019-04-23 11:18:53.000 
A   Manual  2019-04-23 11:19:49.000         End Manual
A   Automatic 2019-04-23 11:26:50.000       Start Automatic
A   Automatic 2019-04-23 11:42:30.000   
A   Automatic 2019-04-23 11:43:31.000   
A   Automatic 2019-04-23 11:44:32.000       End Automatic
A   Manual  2019-04-24 03:35:06.000         Start Manual
A   Manual  2019-04-24 03:36:01.000 
A   Manual  2019-04-24 03:36:57.000 
A   Manual  2019-04-24 03:37:48.000 
A   Manual  2019-04-24 03:38:44.000 
A   Manual  2019-04-24 03:39:40.000 
A   Manual  2019-04-24 03:40:46.000 
A   Manual  2019-04-24 03:41:52.000         End Manual
A   Automatic 2019-04-24 04:26:50           Start Automatic
A   Automatic 2019-04-24 04:42:30   
A   Automatic 2019-04-24 04:43:31   
A   Automatic 2019-04-24 04:44:32           End Automatic

Answer 1

使用滞后/领先

Select t1.*,
       case 
           when lag(activity) over(partition by ID order by datetime) <> activity then 'Start '+Activity
           when lead(activity) over(partition by ID order by datetime) <> activity then 'End '+Activity
       end as Act2
from MyTable t1

或者自己加入

with CTE as
(
select t1.*, row_number() over (partition by ID order by datetime) rn
from MyTable t1
)
select t1.*,
       case 
         when t1.Activity <> t2.activity then 'Start '+t1.Activity
         when t1.Activity <> t3.Activity then 'End ' +t1.Activity 
       end as Act2
from CTE t1
left join CTE t2
on t2.rn = t1.rn+1
left join CTE t3
on t3.rn = t1.rn-1

注意:任何时候一个事件发生一次，只会有一个“开始”