我使用的数据库根据部件 ID 及其活动时间记录事件。我遇到的问题是这些事件被截断以适应一天。如果某个部件的活动时间延续到第二天，则该事件将按其关联的天数进行拆分。在这种情况下，第二天的活动开始时间戳与前一天的活动结束时间戳匹配。 我正在尝试想出一种方法，将这些拆分事件合并为一条记录，其中包含每个部分处于活动状态时的"真实"开始和结束时间。
下面是数据集的外观示例：
date part_id active_start active_end
1/1/2019 100 1/1/19 8:00 1/1/19 9:30
1/1/2019 100 1/1/19 14:00 1/2/19 0:00
1/2/2019 100 1/2/19 0:00 1/3/19 0:00
1/3/2019 100 1/3/19 0:00 1/4/19 0:00
1/4/2019 100 1/4/19 0:00 1/4/19 8:00
1/7/2019 100 1/7/19 6:00 1/8/19 0:00
1/8/2019 100 1/8/19 0:00 1/9/19 0:00
1/9/2019 100 1/9/19 0:00 1/9/19 11:30
1/11/2019 100 1/11/19 12:00 1/11/19 22:00
1/13/2019 100 1/13/19 14:30 1/14/19 0:00
1/14/2019 100 1/14/19 0:00 1/15/19 0:00
1/15/2019 100 1/15/19 0:00 1/15/19 8:30
我试图将其简化为以下内容：
date part_id active_start active_end
1/1/2019 100 1/1/19 8:00 1/1/19 9:30
1/1/2019 100 1/1/19 14:00 1/4/19 8:00
1/7/2019 100 1/7/19 6:00 1/9/19 11:30
1/11/2019 100 1/11/19 12:00 1/11/19 22:00
1/13/2019 100 1/13/19 14:30 1/15/19 8:30
有~70个不同的部件号，每个部件号在观察期内有多达200个不同的活动事件。活动事件最多可以持续数天。由于我在SQL方面相当缺乏经验，因此任何帮助将不胜感激。
这是一个间隙和孤岛问题，您希望将相邻的行组合在一起。
下面是一个使用窗口函数的解决方案：
select
min(date) date,
part_id,
min(active_start) active_start,
max(active_end) active_end
from (
select
t.*,
sum(case when lag_active_end = active_start then 0 else 1 end)
over(partition by part_id order by active_start) grp
from (
select
t.*,
lag(active_end) over(partition by part_id order by active_start) lag_active_end
from mytable t
) t
) t
group by part_id, grp
最内部的查询检索具有相同part_id的上一条记录的结束日期。中间查询执行一个窗口总和，每次上一个结束日期不等于当前开始日期时，该总和都会增加 1：这将定义相邻行的组。最后，外部查询按组聚合，并计算范围的开始和结束。