SQL-запрос для отображения разрывов между несколькими диапазонами дат

Я работаю над проектом SSRS/SQL и пытаюсь написать запрос, чтобы получить промежутки между датами, и я совершенно не понимаю, как это написать. В основном у нас есть ряд устройств, которые можно запланировать для использования, и мне нужен отчет для показать, когда они не используются.

У меня есть таблица с идентификатором устройства, временем EventStart и EventEnd, мне нужно выполнить запрос, чтобы получить время между этими событиями для каждого устройства, но я не совсем уверен, как это сделать.

Например:

Device 1 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 1 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`    
Device 1 Event C runs from `02/01/2012 18:00 - 02/01/2012 20:00`    
Device 2 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 2 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`

Мой запрос должен иметь результат

`Device 1 01/01/2012 10:00 - 01/01/2012 18:00`
`Device 1 01/01/2012 20:00 - 02/01/2012 18:00`
`Device 2 01/01/2012 10:00 - 01/01/2012 18:00`

В этой таблице будет в среднем около 4-5 устройств, а может и 200-300+ событий.

Обновления:

Хорошо, я обновлю это, чтобы попытаться дать немного больше информации, так как я, кажется, не слишком хорошо это объяснил (извините!)

Я имею дело с таблицей, в которой есть сведения о событиях. Каждое событие представляет собой бронирование симулятора полета. У нас есть несколько симуляторов полета (называемых в таблице устройствами), и мы пытаемся создать отчет SSRS, который мы можем дать клиенту, чтобы показать дни / время, когда каждый сим доступен.

Поэтому я собираюсь передать параметр даты начала/окончания и выбрать все доступные места между этими датами. Затем результаты должны отображаться примерно так:

Device   Available_From       Available_To
 1       01/01/2012 10:00    01/01/2012 18:00`
 1       01/01/2012 20:00    02/01/2012 18:00`
 2       01/01/2012 10:00    01/01/2012 18:00`

Также события могут иногда перекрываться, хотя это очень редко и из-за неверных данных, не имеет значения, что событие на одном устройстве перекрывает событие на другом устройстве, поскольку мне нужно знать доступность для каждого устройства отдельно.


sql stored-procedures gaps-and-islands
person Purplegoldfish    schedule 07.03.2012    source источник
comment
Идентификатор устройства имеет значение? Или вы хотите найти время между датой окончания и датой следующего начала, независимо от устройства?   -  person Michael Fredrickson    schedule 07.03.2012
comment
Могут ли события накладываться друг на друга? Например, учитывая приведенные выше примеры данных, можно ли также запустить событие C для устройства 3 с 01.01.2012 09:00 по 01.01.2012 11:00?   -  person    schedule 07.03.2012
comment
Я добавил тег пробелов и островов. Здесь было довольно много вопросов G&I. Кстати: проблема плохо определена: что такое событие? где он нужен в решении?   -  person wildplasser    schedule 07.03.2012
comment
спасибо @wildplasser Я обновил вопрос, чтобы добавить больше деталей.   -  person Purplegoldfish    schedule 08.03.2012
comment
{device=1, event='C'} работает в обратном направлении? Кстати: пожалуйста, используйте форматы даты ISO. (МДЯ зло...)   -  person wildplasser    schedule 08.03.2012
comment
@wildplasser извините, опечатка, формат ДД/ММ/ГГГГ   -  person Purplegoldfish    schedule 08.03.2012

Ответы (5)


arrow_upward
24
arrow_downward

Запрос:

Если предположить, что поля, содержащие интервал, названы Start и Finish, а таблица названа YOUR_TABLE, запрос...

SELECT Finish, Start
FROM
    (
        SELECT DISTINCT Start, ROW_NUMBER() OVER (ORDER BY Start) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Start > T2.Start AND T1.Start < T2.Finish
            )
        ) T1
    JOIN (
        SELECT DISTINCT Finish, ROW_NUMBER() OVER (ORDER BY Finish) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Finish > T2.Start AND T1.Finish < T2.Finish
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
    Finish < Start

... дает следующий результат для ваших тестовых данных:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

Важным свойством этого запроса является то, что он будет работать и с перекрывающимися интервалами.

Алгоритм:

1. Объединить перекрывающиеся интервалы

Подзапрос T1 принимает только те начала интервалов, которые находятся вне других интервалов. Подзапрос T2 делает то же самое для концов интервала. Это то, что устраняет перекрытия.

DISTINCT важен в случае, если есть два идентичных начала (или конца) интервала, которые оба находятся вне других интервалов. WHERE Finish < Start просто удаляет все пустые интервалы (т.е. продолжительность 0).

Мы также прикрепляем номер строки относительно временного порядка, который понадобится на следующем шаге.

T1 дает:

Start                       RN
2012-01-01 08:00:00.000     1
2012-01-01 18:00:00.000     2

T2 дает:

Finish                      RN
2012-01-01 10:00:00.000     1
2012-01-01 20:00:00.000     2

2. Реконструируйте результат

Теперь мы можем реконструировать либо «активные», либо «неактивные» интервалы.

неактивные интервалы восстанавливаются путем сопоставления конца предыдущего интервала с началом следующего, поэтому - 1 в предложении ON. По сути, мы положили...

Finish                      RN
2012-01-01 10:00:00.000     1

...а также...

Start                       RN
2012-01-01 18:00:00.000     2

... вместе, в результате чего:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

(Активные интервалы можно реконструировать, поместив строки из T1 рядом со строками из T2, используя JOIN ... ON T1.RN = T2.RN и вернув WHERE.)

Пример:

Вот чуть более реалистичный пример. Следующие тестовые данные:

Device      Event      Start                      Finish
Device 1    Event A    2012-01-01 08:00:00.000    2012-01-01 10:00:00.000
Device 2    Event B    2012-01-01 18:00:00.000    2012-01-01 20:00:00.000
Device 3    Event C    2012-01-02 11:00:00.000    2012-01-02 15:00:00.000
Device 4    Event D    2012-01-02 10:00:00.000    2012-01-02 12:00:00.000
Device 5    Event E    2012-01-02 10:00:00.000    2012-01-02 15:00:00.000
Device 6    Event F    2012-01-03 09:00:00.000    2012-01-03 10:00:00.000

Дает следующий результат:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000
2012-01-01 20:00:00.000     2012-01-02 10:00:00.000
2012-01-02 15:00:00.000     2012-01-03 09:00:00.000
person Branko Dimitrijevic    schedule 07.03.2012
comment
Использование Distinct не будет работать, если несколько дат начала или окончания имеют одинаковые значения, потому что Distinct применяется к дате и номеру строки, а поскольку номер строки уникален, по существу ничего не делает. Мне пришлось преобразовать отдельные запросы в Group By. - person Andre; 30.11.2016
comment
@Andre, не могли бы вы вставить группу в качестве примера. - person AJ AJ; 19.07.2018

arrow_upward
5
arrow_downward

Первый ответ - но см. ниже окончательный ответ с дополнительными ограничениями, добавленными OP.

-- Если вы хотите получить следующее startTime после самого последнего endTime и избежать перекрытий, вам нужно что-то вроде:

select
    distinct
    e1.deviceId,
    e1.EventEnd,
    e3.EventStart
from Events e1 
join Events e3 on e1.eventEnd < e3.eventStart     /* Finds the next start Time */
and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd)
and not exists (select *                          /* Eliminates an e1 rows if it is overlapped */
                from Events e5 
                where e5.eventStart < e1.eventEnd
                    and e5.eventEnd > e1.eventEnd)

Для случая ваших трех строк:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')

Это дает 1 результат:

January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800

Однако я предполагаю, что вы, вероятно, захотите также сопоставить DeviceId. В этом случае при объединении вы должны добавить e1.DeviceId = e3.DeviceId и e1.deviceId = e5.deviceId.

SQL Fiddle здесь: http://sqlfiddle.com/#!3/3899c/8

--

ОК, окончательное редактирование. Вот запрос, добавляющий идентификаторы устройств и добавляющий отдельные учетные записи для одновременно завершающихся событий:

SELECT distinct
    e1.DeviceID,
    e1.EventEnd as LastEndTime,
    e3.EventStart as NextStartTime
FROM Events e1 
join Events e3 on e1.eventEnd < e3.eventStart
     and e3.deviceId = e1.deviceId
     and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd
                    and e5.deviceId = e3.deviceId)
where not exists (select * from Events e7 
                    where e7.eventStart < e1.eventEnd
                      and e7.eventEnd > e1.eventEnd
                      and e7.deviceId = e1.deviceId)
order by e1.deviceId, e1.eventEnd

Присоединение к e3 находит следующий старт. Присоединение к e5 гарантирует, что это самое раннее время начала после текущего времени окончания. Соединение с e7 удаляет строку, если время окончания рассматриваемой строки перекрывается другой строкой.

Для этих данных:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')
insert into Events values (2, '01/02/2012 11:00', '01/02/2012 15:00')
insert into Events values (1, '01/02/2012 10:00', '01/02/2012 12:00')
insert into Events values (2, '01/02/2012 10:00', '01/02/2012 15:00')
insert into Events values (2, '01/03/2012 09:00', '01/03/2012 10:00')

Вы получаете этот результат:

1   January, 01 2012 10:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800
2   January, 01 2012 20:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 02 2012 15:00:00-0800  January, 03 2012 09:00:00-0800

SQL Fiddle здесь: http://sqlfiddle.com/#!3/db0fa/3

person Mike Ryan    schedule 07.03.2012

arrow_upward
3
arrow_downward

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

DECLARE @Events TABLE (
    DeviceID INT,
    EventStart DATETIME,
    EventEnd DATETIME
)

INSERT INTO @Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO @Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')

SELECT
    e1.DeviceID,
    e1.EventEnd,
    e2.EventStart
FROM 
    @Events e1 
    JOIN @Events e2 
        ON e2.EventStart = (
            SELECT MIN(EventStart)
            FROM @Events
            WHERE EventStart > e1.EventEnd
        )
person Michael Fredrickson    schedule 07.03.2012
comment
Но это не касается перекрывающихся событий. См. выше спрашивающего о 9:00 - 11:00. В этом случае это приведет к ошибке и даст две строки — одну с 10:00 до 18:00 и вторую с 11:00 до 18:00. Например, см.: sqlfiddle.com/#!3/6e49f/1. - person Mike Ryan; 07.03.2012
comment
Спасибо, это похоже на действительно хорошую отправную точку для моего запроса. - person Purplegoldfish; 07.03.2012

arrow_upward
2
arrow_downward

Это решает вашу проблему:

Второй видится более актуальным

«Есть таблица, в которой два столбца — DateFrom и DateTo. Оба столбца содержат значения даты и времени. Как найти недостающие диапазоны дат или, другими словами, все диапазоны дат, которые не охвачены ни одной из записей в таблице?

person bPratik    schedule 07.03.2012

arrow_upward
2
arrow_downward

Вот решение Postgres, которое я только что сделал, которое не включает хранимые процедуры:

SELECT minute, sum(case when dp.id is null then 0 else 1 end) as s 
FROM generate_series( 
   '2017-12-28'::timestamp,
   '2017-12-30'::timestamp,
   '1 minute'::interval
) minute 
left outer join device_periods as dp
on minute >= dp.start_date and minute < dp.end_date 
group by minute order by minute

Функция generate_series создает таблицу, в которой есть одна строка для каждой минуты в диапазоне дат. Вы можете изменить интервал на 1 секунду, если быть более точным. Это специфичная для postgres функция, но, возможно, что-то подобное существует и в других движках.

Этот запрос даст вам все заполненные и пустые минуты. Вы можете обернуть этот запрос во внешний запрос, который может группироваться по часам, дням или выполнять некоторые операции с оконными функциями, чтобы получить точный результат, который вам нужен. Для моих целей мне нужно было только посчитать, есть ли пробелы или нет.

person Martin Taleski    schedule 04.01.2018