时间戳

首页 > 解决方案 > 聚合行开始和结束日期 < 10 分钟

sql - 聚合行开始和结束日期 < 10 分钟

问题描述

我想在 SQL Server 数据库中进行以下转换。

聚合 Tag_of_thing 和 Status_of_thing 使得......

请注意，在这种情况下，结束班次是上面的行，请参见示例表

  1. 如果Start_shift_date_time = End_shift_date_time然后聚合行，使得 start = min(Start_shift_date_time ) 和 finish = max(End_shift_date_time )

  2. 如果Start_shift_date_time <= End_shift_date_time + 10mins然后聚合行，使得 start = min(Start_shift_date_time ) 和 finish = max(End_shift_date_time )

原始源数据表（让我们将此表称为输入）

Tag_of_thing    Status_of_thing Start_shift_date_time   End_shift_date_time Other_column_I_dont_care_about
A123    T123    04/08/2020 15:07:02 04/08/2020 18:00:00 56110
A123    T123    04/08/2020 18:00:00 05/08/2020 01:27:41 32473
A123    T123    05/08/2020 06:15:41 05/08/2020 06:00:00 26808
A123    T124    05/08/2020 06:00:00 05/08/2020 18:00:00 23969
A123    T124    05/08/2020 18:00:00 06/08/2020 06:00:00 29613
A123    T124    06/08/2020 06:00:00 06/08/2020 10:48:00 7276
B124    G1  03/08/2020 12:43:02 03/08/2020 18:00:00 29806
B124    G1  03/08/2020 18:00:00 03/08/2020 23:03:41 46101
B124    G1  03/08/2020 23:07:41 04/08/2020 03:55:41 15510
C124    R1  03/08/2020 12:43:02 03/08/2020 18:00:00 47527
C124    R1  03/08/2020 18:00:00 03/08/2020 23:03:41 16708
C124    R1  03/08/2020 23:18:41 04/08/2020 04:06:41 3247

在此处输入图像描述

转换后的输出数据表

Tag_of_thing    Status_of_thing Start_shift_date_time   End_shift_date_time
A123    T123    04/08/2020 15:07:02 05/08/2020 01:27:41
A123    T123    05/08/2020 06:15:41 05/08/2020 06:00:00
A123    T124    05/08/2020 06:00:00 06/08/2020 10:48:00
B124    G1  03/08/2020 12:43:02 04/08/2020 03:55:41
C124    R1  03/08/2020 12:43:02 03/08/2020 23:03:41
C124    R1  03/08/2020 23:18:41 04/08/2020 04:06:41

在此处输入图像描述

如果您需要更多示例或转换过程的不同解释，请告诉我

资源

类似的差距和岛屿问题：https ://bertwagner.com/2019/03/12/gaps-and-islands/

标签： sqlsql-servergaps-and-islands

解决方案

如果我理解正确，这是一个差距和孤岛问题 - 扭曲的是，您允许在行之间最多 10 分钟进行聚合。

select tag_of_thing, status_of_thing, grp, min(start_shift_date_time), max(end_shift_date_time) 
from (select t.*,
             sum(case when prev_esdt > dateadd(minute, -10, start_shift_date_time)
                      then 0 else 1
                 end)  over (partition by tag_of_thing, status_of_thing order by start_shift_date_time) as grp
      from (select t.*,
                   lag(end_shift_date_time) over (partition by tag_of_thing, status_of_thing order by start_shift_date_time) as prev_esdt
            from t
           ) t
     ) t
group by tag_of_thing, status_of_thing, grp
order by tag_of_thing, status_of_thing, min(start_shift_date_time);

这通过将先前的结束时间与当前的开始时间进行比较来确定新组的第一行的位置。然后，该组是这些值的累积和，最后一步是聚合。

推荐阅读