Можно ли группировать строки по разнице в T-SQL?

Можно ли написать SQL-запрос, который группирует строки на основе значения значения столбца datetime от значения соседней строки?

Позвольте мне привести пример... У меня есть запрос 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:00, а вторые - около половины третьего.

Я хочу получить подробную информацию о времени начала / окончания каждой партии таймингов, средней продолжительности и количестве таймингов в группе. Чтобы сделать это, мне нужен способ группировки таймингов по партиям, где партия определяется как группа таймингов с менее чем 30 секундами между концом одного и началом следующего, когда они упорядочены по времени начала. Является ли это возможным?

Некоторые заметки о реальной ситуации...

  • Единственная действительно отличительная особенность пакета состоит в том, что разрыв между концом одного времени и началом следующего будет значительно больше. Жесткое кодирование с фиксированным сроком будет в порядке.
  • Как и в этом примере, в день может быть несколько партий, поэтому нет группировки по дате.
  • В действительности партии могут быть чем угодно, от нескольких минут до нескольких часов, и содержать всего несколько таймингов или несколько тысяч. Нет заданного времени, когда происходят партии.
  • Обратите внимание на временные интервалы в строках 6 и 7. Множественные временные интервалы происходят параллельно, перекрывая друг друга, хотя разница между концом одного и началом следующего по-прежнему остается способом определения партий.
Источник

2 ответа

Решение

В SQL Server 2012+:

Использование функции окна lag() в общем табличном выражении, чтобы получить datediff() текущего ряда starttime по сравнению с предыдущим значением строки для endtime, а потом sum() over() с условной агрегацией (сравнение с жестко закодированным значением) для генерации 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

демонстрационный ролик: 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 |
+----+---------------------+---------------------+----------+-------+
Источник

Вы можете сгруппировать по дате и времени

group by DATEPART(StartTime, yyyy) + DATEPART(StartTime, MM) + DATEPART(StartTime, DD)

Вы также должны изменить, чтобы выбрать, чтобы соответствовать

Источник
Другие вопросы по тегам