datetime - 计算多条记录的重叠时间

Question

我想计算多条记录的重叠时间。我正在尝试使用分析窗口函数来做到这一点，但现在卡住了。

数据集：

ID  FY  WK  Group   StartDate   StartTime   EndTime Category    Dur Overlap
1   19  49  H-IS02  2019/06/03  22:35:00    23:25:00    MBA2    50  00:00:00
2   19  49  H-IS02  2019/06/04  09:40:00    09:55:00    MBD2    15  00:00:00
3   19  49  H-IS02  2019/06/04  10:30:00    10:55:00    MBD2    25  00:00:00
4   19  49  H-IS02  2019/06/04  13:50:00    14:30:00    MBD2    40  00:00:00
5   19  49  H-IS02  2019/06/04  14:05:00    15:15:00    MBD2    70  00:25:00
6   19  49  H-IS02  2019/06/04  19:30:00    01:20:00    MBA2    350 00:00:00
7   19  49  H-IS02  2019/06/06  08:45:00    09:30:00    MBD2    45  00:00:00
8   19  49  H-IS02  2019/06/06  17:30:00    21:40:00    MBA2    250 00:00:00
9   19  49  H-IS02  2019/06/06  19:30:00    20:10:00    MBA2    40  02:10:00
10  19  49  H-IS02  2019/06/07  00:00:00    01:20:00    MBA2    80  00:00:00
11  19  49  H-IS02  2019/06/07  08:30:00    09:20:00    MBD2    50  00:00:00
12  19  49  H-IS02  2019/06/07  09:20:00    16:20:00    MBD2    420 00:00:00
13  19  49  H-IS02  2019/06/07  13:30:00    13:46:00    MBD2    16  02:50:00
14  19  49  H-IS02  2019/06/07  16:06:00    16:20:00    MBD2    14  00:00:00
15  19  49  H-IS02  2019/06/07  17:45:00    23:00:00    MBA2    455 00:00:00
16  19  49  H-IS02  2019/06/07  20:30:00    21:20:00    MBA2    50  00:50:00
17  19  49  H-IS02  2019/06/07  22:30:00    22:45:00    MBA2    15  00:15:00
18  19  49  H-IS02  2019/06/07  23:00:00    23:15:00    MBA2    15  00:00:00

我将我的分区设置为 (Group, StartDate, Category) 并想要计算 EndTime 与上一条记录的 StartTime 的重叠，如果它属于同一数据集，即：StartDate、Group、Category。

持续时间（Dur）计算得很好，但我还需要知道同一数据集中记录的重叠分钟数。上面数据中的记录 5 的开始时间早于记录 4 的结束时间，并且它属于同一个分区。

计算逻辑解释

我将不胜感激有关如何进行此计算的任何指导。

挑战的更多细节：数据是工厂设备的数据。每条记录都包含停机时间信息。我想计算一台机器与另一台机器停机时间重叠的持续时间。两台机器可能同时停机 - 具有确切的开始和结束时间。因此，如果他们的停机时间为每个小时，我的总停机时间为 2 小时，而我的有效停机时间仅为 1 小时。通过计算机器之间的停机时间重叠，我还可以将有效停机时间确定为 TOTAL DOWNTIME - OVERLAP

我首先通过使用分区来解决这个问题，但不认为它会解决我的问题。我看到的挑战是，例如，我可能有 4 条记录应该检查是否有重叠。记录 2 与记录 1 重叠。记录 3 不重叠任何内容，记录 4 与记录 1 重叠。如果偏移量始终为 1 或 n 条记录，我可以使用 LAG 函数。

我当前的 SELECT 语句如下。而且它也没有给我温暖的感觉……它在结果集中创建了额外的记录。

    select DT.fiscalyear, DT.productionweek, DT.group, DT.plantsection, DT.startdate, DT.category,OL.endtime,OL.starttime, 
dt.endtime,DT.starttime, dt.endtime - DT.starttime as Overlap 
from (
select DT.fiscalyear, DT.productionweek, DT.group, DT.PlantSection, DT.StartDate, DT.starttime,dt.MaintenancePlant, 
        DT.EndDate, DT.endtime, DT.category, DT.breakdownduration 
from Labs_Engineering.BV_MOR_Mining_AllDowntime DT) DT

join (
select TEST.startdate, PlantSection,category, max(endtime) endtime, max(starttime) starttime
from Labs_Engineering.BV_MOR_Mining_AllDowntime Test
where productionweek = 49 and fiscalyear = 19
and MaintenancePlant = 9804
and group = 'H-IS02'
and category like 'MBA2'
and (PlantSection ='P33' or PlantSection = 'P34')
group by PlantSection,category,startdate
--where DT.endtime <= Test.starttime
) OL
on DT.StartDate = OL.StartDate
and DT.PlantSection = OL.PlantSection
and DT.category = OL.category
where DT.starttime <= OL.endtime and ol.starttime > dt.starttime;

标签： datetimeteradata