Блог

Я пытаюсь свернуть строки с последовательной временной шкалой в течение одного дня в одну строку, но возникла проблема из-за разрыва во времени. Например, мой набор данных выглядит следующим образом.

 Date            StartTime     EndTime      ID
2017-12-1       09:00:00      11:00:00    12345
2017-12-1       11:00:00      13:00:00    12345
2018-09-08      09:00:00      10:00:00    78465
2018-09-08      10:00:00      12:00:00    78465
2018-09-08      15:00:00      16:00:00    78465
2018-09-08      16:00:00      18:00:00    78465

 

Как видно, первые две строки можно просто объединить вместе без каких-либо проблем, потому что в течение этого дня нет временного разрыва. Однако. для записей на 2019-09-08 есть промежуток между 12:00 и 15:00. И я хотел бы объединить эти четыре записи в две разные строки следующим образом:

 Date            StartTime     EndTime      ID
2017-12-1       09:00:00      13:00:00    12345
2018-09-08      09:00:00      12:00:00    78465
2018-09-08      15:00:00      18:00:00    78465

 

Другими словами, я хочу сворачивать строки только тогда, когда переменные времени являются последовательными в течение одного и того же дня для одного и того же идентификатора.

Может ли кто-нибудь, пожалуйста, помочь мне с этим? Я попытался создать уникальную группу, используя функции ЗАДЕРЖКИ и опережения, но это не сработало.

Вы можете использовать рекурсивный cte. Сгруппируйте его как ту же группу, если время окончания совпадает со временем следующего запуска. А затем найдите MIN() и MAX()

 with cte as
(
    select  rn = row_number() over (partition by [ID], [Date] order by [StartTime]),
            *
    from    tbl
),
rcte as
(
    -- anchor member
    select  rn, [ID], [Date], [StartTime], [EndTime], grp = 1
    from    cte
    where   rn  = 1

    union all

    -- recursive member
    select  c.rn, c.[ID], c.[Date], c.[StartTime], c.[EndTime],
            grp = case  when    r.[EndTime] = c.[StartTime] 
                        then    r.grp
                        else    r.grp   1
                        end
    from    rcte r
            inner join cte c    on  r.[ID]      = c.[ID]    
                               and  r.[Date]    = c.[Date]
                               and  r.rn        = c.rn - 1
)
select  [ID], [Date], 
        min([StartTime]) as StartTime, 
        max([EndTime]) as EndTime
from    rcte
group by [ID], [Date], grp
 

db<>демо-версия скрипки

Если у вас нет особых возражений против сворачивания непоследовательных строк, которые являются последовательными для этого идентификатора, вы можете просто использовать GROUP BY :

 SELECT
    Date,
    StartTime = MIN(StartTime),
    EndTime = MAX(EndTime),
    ID
FROM table
GROUP BY ID, Date
 

В противном случае вы можете использовать решение, основанное на ROW_NUMBER :

 SELECT
    Date,
    StartTime,
    EndTime,
    ID
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Date, ID ORDER BY StartTime)
    FROM table
) t
WHERE rn = 1
 

Это пример проблемы пробелов и островов — на самом деле довольно простой пример. Идея состоит в том, чтобы присвоить каждой строке группировку «остров», указав, что они должны быть объединены, поскольку они перекрываются. Затем агрегируйте.

Как вы назначаете остров? В этом случае посмотрите на предыдущий endtime , и если он отличается от starttime предыдущего, то в ряду начинается новый остров. Вуаля! Совокупная сумма стартового флага идентифицирует каждый остров.

Как SQL:

 select id, date, min(starttime), max(endtime)
from (select t.*,
             sum(case when prev_endtime = starttime then 0 else 1 end) over (partition by id, date order by starttime) as grp
      from (select t.*,
                   lag(endtime) over (partition by id, date order by starttime) as prev_endtime
            from t
           ) t
     ) t
group by id, date, grp;
 

Вот скрипка db<> .

Примечание: Это предполагает, что периоды времени никогда не охватывают несколько дней. Код может быть очень легко изменен, чтобы справиться с этим … но с оговоркой. Время начала и окончания следует сохранять как datetime (или связанную временную метку), а не разделять дату и время на разные столбцы. Почему? SQL Server не поддерживает '24:00:00' как допустимое время.