如何在时间范围内从组中创建一条记录

Question

请帮助完成下面的SQL挑战。我得到的SQL server表有三列: item no，start & End time stamps，请看下面的示例数据。要求是找到同一项目编号的20分钟时间范围内的一组事务(下面以粗体突出显示)，然后创建一个新的单行，其中开始时间戳从第一行开始，结束时间戳从该数据集中的最后一行开始。有人能帮个忙吗？

源样本数据：

ItemNo     StartTstp               EndTstp
1100    2018-10-10 5:47:00      2018-10-10 6:28:00
1100    2018-10-10 7:47:00      2018-10-10 7:48:00
1100    2018-10-10 7:48:00      2018-10-10 7:50:00
1100    2018-10-10 7:50:00      2018-10-10 7:53:00
1100    2018-10-10 13:10:00     2018-10-10 13:20:00
1100    2018-10-10 16:10:00     2018-10-10 16:30:00
1101    2018-10-10 9:50:00      2018-10-10 9:53:00

输出：

ItemNo  StartTstp               EndTstp
1100    2018-10-10 5:47:00      2018-10-10 6:28:00
1100    2018-10-10 7:47:00      2018-10-10 7:53:00
1100    2018-10-10 13:10:00     2018-10-10 13:20:00
1100    2018-10-10 16:10:00     2018-10-10 16:30:00
1101    2018-10-10 9:50:00      2018-10-10 9:53:00

谢谢。

screenshots of sample & result

Answer 1

根据我对这个问题的理解，你可以尝试这个CTE：

;WITH cte AS 
(
    SELECT
        t1.ItemNo,
        t1.StartTstp,
        MAX(t2.EndTstp) AS EndTstp
    FROM YourTable AS t1
        JOIN YourTable AS t2 ON
            t1.ItemNo = t2.ItemNo
            AND DATEDIFF(MI, t1.StartTstp, t2.StartTstp) BETWEEN 0 AND 20
    GROUP BY
        t1.ItemNo,
        t1.StartTstp
)
SELECT
    ItemNo,
    MIN(StartTstp) AS StartTstp,
    EndTstp
FROM cte
GROUP BY
    ItemNo,
    EndTstp;

这将产生与样本数据的预期输出，我使用其他日期集进行了测试，结果仍然与预期一致。但是，请注意，这只检查了整整20分钟，如果您想包括秒，那么where子句应该进行调整。

Answer 2

使用lag()获取以前的EndTstp。有了这些信息，就可以确定分组何时开始。开始数的累积和定义了一个组。。。然后聚合：

select item, min(StartTstp), max(endTstp)
from (select t.*,
             sum(case when prev_endtstp >= StartTstp then 0 else 1 end) over
                 (partition by itemno order by StartTstp) as grp
      from (select t.*,
                   lag(EndTstp) over (partition by itemno order by StartTstp) as prev_endtstp
            from t
           ) t
     ) t
group by itemno, grp