Острова и пробелы

WITH drivers_data AS
(
    SELECT *,
           row_num =     ROW_NUMBER()
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime),

           row_num_all = ROW_NUMBER()
                         OVER (PARTITION BY loginId,
                                            tractorId
                               ORDER BY messageTime),

           first_date =  FIRST_VALUE (messageTime)
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime),

           last_date =   LAST_VALUE (messageTime)
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM @t
)
SELECT loginId, tractorId, first_date, last_date, driverStatus
FROM drivers_data
WHERE row_num = 1
ORDER BY row_num_all;

ВЫХОД:

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:56:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:57:00 | 2018-10-08 09:07:00 | 1            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-12-06 08:07:00 | 2018-12-06 09:07:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-12-08 08:07:00 | 2018-12-08 09:07:00 | 3            |
+----------+-----------+---------------------+---------------------+--------------+

Я попытаюсь объяснить, что здесь происходит:

1. row_num Используется для нумерации строк, которые ограничены датой и статусом драйвера. Нам нужно приведение, так как нам нужна часть даты без времени.
2. row_num_all Это ключевой атрибут, поскольку он позволяет в конечном итоге сортировать строки по вхождению. Это окно не ограничено статусом, так как нам нужна нумерация всех данных водителя.
3. first_date Функция FIRST_VALUE удобна для наших целей. Он просто извлекает первое вхождение даты и времени.
4. last_date Правильно предположить, что для последней даты нам нужна LAST_VALUE оконная функция. Но использовать его сложно и требует дополнительных объяснений. Как видите, я явно использую специальное кадрирование ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Но почему? Позволь мне объяснить. Возьмем часть вывода для даты 10/8/2018 и статуса 2 с кадрированием по умолчанию. Получаем следующие результаты:

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:37:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:56:00 | 2            | 
+----------+-----------+---------------------+---------------------+--------------+

Как видите, последняя дата неверна! Это происходит потому, что LAST_VALUE использует фрейм по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - это означает, что последняя строка всегда текущая строка в окне. Вот что происходит под капотом. Создаются три окна. Каждая строка получает свое окно. Затем он извлекает последнюю строку из окна:

Окно для 1-й строки

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
+----------+-----------+---------------------+---------------------+--------------+

Окно для 2-й строки

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:37:00 | 2            |
+----------+-----------+---------------------+---------------------+--------------+

Окно для 3-й строки

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:37:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:56:00 | 2            | 
+----------+-----------+---------------------+---------------------+--------------+

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

5. Далее идет WHERE row_num = 1. Все просто: поскольку все строки содержат одинаковую информацию о первой и последней дате, нам нужна только первая строка.

6. Заключительная часть ORDER BY row_num_all. Здесь вы получите правильный заказ.

P.S.

1. Ваш желаемый вывод неверен. Для даты 8/10/18 8:57 AM и статуса 1 последняя дата должна быть 10/8/2018 9:07 AM, а не 10/8/2018 9:04 AM, как вы упомянули.

2. Также отсутствует вывод для даты 12/6/2018 и статуса 2.

ОБНОВИТЬ:

Вот иллюстрации того, как работают FIRST_VALUE и LAST_VALUE.

Все три фигуры состоят из следующих частей:

1. Данные запроса Это результат запроса.
2. Исходный запрос Исходные исходные данные.
3. Windows Это промежуточные этапы расчетов.
4. Фрейм Указывает, какой фрейм используется.
5. Зеленая ячейка Спецификация окна.

Вот что происходит под капотом:

1. Во-первых, SQL Server создает разделы для всех упомянутых полей. На рисунке это partition столбец.
2. Каждый раздел может иметь рамку: стандартную или пользовательскую. Рамка по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это означает, что строка получает окно между началом раздела и текущей строкой. Если вы не укажете фрейм, в игру вступает фрейм по умолчанию.
3. Каждый кадр создает окно для каждой строки. На рисунках эти окна находятся в столбцах с row 1 по row 2 и отмечены цветом. Номер строки соответствует полю row_num_all.
4. Строка работает только в пределах своего окна.

1. ПЕРВОЕ_ЗНАЧЕНИЕ

Чтобы получить первое свидание, мы можем использовать удобную оконную функцию FIRST_VALUE. Как видите, здесь мы используем фрейм по умолчанию. Это означает, что для каждой строки окно будет находиться между началом окна и текущей строкой. Для первого свидания это как раз то, что нам нужно. Каждая строка будет извлекать значение из первой строки. Первая дата находится в поле «first_date».

2. LAST_VALUE - неправильный кадр

Теперь нам нужно рассчитать последнюю дату. Последняя дата находится в последней строке раздела, поэтому мы можем использовать оконную функцию LAST_VALUE. Как я упоминал ранее, если мы не указываем фрейм, используется фрейм по умолчанию. Как видно на рисунке, рамка всегда заканчивается в текущей строке - это неверно, потому что нам нужна дата из последней строки окна. Поле last_date показывает нам неправильные результаты - оно отражает дату из текущей строки.

3. LAST_VALUE - правильный кадр

Чтобы исправить ситуацию с получением последней даты, нам нужно изменить кадр, на котором будет работать LAST_VALUE: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Как видите, окно для каждой строки теперь находится между текущей строкой и концом раздела. В этом случае LAST_VALUE будет правильно извлекать дату из последней строки окна. Теперь результат в поле last_date правильный.

Приведенное ниже решение идентифицирует каждый раз, когда начинается остров (когда изменяется driverStatus) в каждой комбинации loginID / tractorID, а затем присваивает номер «id» этому острову.

После этого можно просто min/max найти, когда этот остров начинается и заканчивается.

Ответ:

select b.loginId
, b.tractorId
, min(b.messageTime) as startTime
, max(b.messageTime) as endTime
, b.driverStatus
from (
    select a.loginId
    , a.tractorId
    , a.messageTime
    , a.driverStatus
    , a.is_island_start_flg
    , sum(a.is_island_start_flg) over (partition by a.loginID, a.tractorID order by a.messageTime asc) as island_nbr --assigning the "id" number to the island
    from (
        select st.loginId
        , st.tractorId
        , st.messageTime
        , st.driverStatus
        , iif(lag(st.driverStatus, 1, st.driverStatus) over (partition by st.loginID, st.tractorId order by st.messageTime asc) = st.driverStatus, 0, 1) as is_island_start_flg --identifying start of island
        from @SomeTable as st
        ) as a
    ) as b
group by b.loginId
, b.tractorId
, b.driverStatus
, b.island_nbr --purposefully in the group by, to make sure each occurrence of a status is in final results
order by b.loginId asc
, b.tractorId asc
, min(b.messageTime) asc

Когда вы пропускаете последние три записи выборочных данных (поскольку это не входит в ожидаемый результат вопроса, как сказал JohnyL), этот запрос выдает точный результат вопроса.

SELECT 
  t.loginId, 
  t.tractorId, 
  startTime = MIN(messageTime), 
  endTime   = MAX(messageTime),
  driverStatus 
FROM @someTable t
GROUP BY loginId, tractorId, driverStatus
ORDER BY MIN(messageTime);

Полученные результаты:

loginId        tractorId  startTime               endTime                 driverStatus
-------------- ---------- ----------------------- ----------------------- ------------
driver35       23533      2018-10-08 08:33:00.000 2018-10-08 08:56:00.000 2
driver35       23533      2018-10-08 08:57:00.000 2018-10-08 09:07:00.000 1
driver35       23533      2018-12-08 08:07:00.000 2018-12-08 09:07:00.000 3