sql - 是否可以在 T-SQL 中按彼此的差异对行进行分组

Question

是否可以编写一个 SQL 查询，根据日期时间列值与相邻行值的差异对行进行分组？

让我举个例子...我有一个像这样的 SQL 查询:

SELECT
    Id,
    StartTime,
    EndTime,
    datediff(second, max(StartTime), EndTime)) as Duration
FROM Timings
ORDER BY StartTime

返回的结果是这样的:

| ID | StartTime           | EndTime             | Duration
| 1  | 2017-10-06 10:59:48 | 2017-10-06 10:59:58 | 10
| 2  | 2017-10-06 11:00:02 | 2017-10-06 11:00:06 | 4
| 3  | 2017-10-06 11:00:15 | 2017-10-06 11:00:22 | 7
| 4  | 2017-10-06 11:00:30 | 2017-10-06 11:00:39 | 9
| 5  | 2017-10-06 15:34:31 | 2017-10-06 15:34:45 | 14
| 6  | 2017-10-06 15:34:48 | 2017-10-06 15:34:56 | 8
| 7  | 2017-10-06 15:34:52 | 2017-10-06 15:34:59 | 7

这里重要的是时间分为两批，前四批都在上午 11 点左右完成，后两批在下午 3 点半左右完成。

我想获取每批计时的开始/结束时间、平均时长以及一组计时的详细信息。为此，我需要一种按批处理对时间进行分组的方法，其中批处理定义为按开始时间排序时，在一个结束时间和下一个开始时间之间少于 30 秒的时间组。可能吗？

真实情况的一些相关笔记...

• 一个批处理的唯一真正显着特征是一个计时结束与下一个计时开始之间的差距明显更大。硬编码一个固定的时间限制是可以的。
• 在此示例中，一天可能有多个批处理，因此按日期分组已过时。
• 实际上，批处理的长度可以从几分钟到几个小时不等，只包含几个时间，也可以包含数千个时间。没有设定批处理发生的时间。
• 注意第 6 行和第 7 行的时间。多个时间确实平行重叠，一个结束和下一个开始之间的巨大差距仍然存在，但作为识别批处理的方式仍然存在.

Answer 1

在 SQL Server 2012+ 中:

在 common table expression 中使用窗口函数 lag()获取当前行 starttime 的 datediff() 与 endtime 的前一行值相比，然后 sum() () 使用条件聚合(与硬编码值进行比较)生成 batch:

;with cte as (
select *
  , datediff(second,lag(endtime) over (order by starttime),starttime) as prev_dat
from timings
)
select id, starttime, endtime, duration
  , sum(case when coalesce(prev_dat,31)>30 then 1 else 0 end) over (
    order by starttime
    ) as batch
from cte

rextester 演示:http://rextester.com/OVNF90739

返回:

+----+---------------------+---------------------+----------+-------+
| id |      starttime      |       endtime       | duration | batch |
+----+---------------------+---------------------+----------+-------+
|  1 | 2017-10-06 10:59:48 | 2017-10-06 10:59:58 |       10 |     1 |
|  2 | 2017-10-06 11:00:02 | 2017-10-06 11:00:06 |        4 |     1 |
|  3 | 2017-10-06 11:00:15 | 2017-10-06 11:00:22 |        7 |     1 |
|  4 | 2017-10-06 11:00:30 | 2017-10-06 11:00:39 |        9 |     1 |
|  5 | 2017-10-06 15:34:31 | 2017-10-06 15:34:45 |       14 |     2 |
|  6 | 2017-10-06 15:34:48 | 2017-10-06 15:34:56 |        8 |     2 |
|  7 | 2017-10-06 15:34:52 | 2017-10-06 15:34:59 |        7 |     2 |
+----+---------------------+---------------------+----------+-------+