найти дату начала и окончания для смежных дат в нескольких строках

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

Моя таблица содержит эти данные:

> Code (nvarchar) | DateFrom (datetime) | DateTo (datetime)
> ========================================================= 
> 3006            | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 
> 3006            | 2014-06-18 09:00:00 | 2014-06-18 22:00:00 
> 1006            | 2014-06-18 07:00:00 | 2014-06-18 09:00:00
> 1006            | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 
> 1006            | 2014-06-18 08:10:00 | 2014-06-18 18:00:00

Я собираюсь представить это с точки зрения. Он будет сгруппирован по коду.

Я хочу, чтобы этот вывод:

> Code | DateFrom            | DateTo
> =========================================================
> 3006 | 2014-06-08 07:00:00 | 2014-06-18 08:00:00
> 3006 | 2014-06-18 09:00:00 | 2014-06-18 22:00:00
> 1006 | 2014-06-18 07:00:00 | 2014-06-18 18:00:00

Как вы видите, есть ли пробелы между DateTo и DateFrom, я хочу, чтобы они были представлены в виде двух строк. Но если следующий «DateFrom» с тем же кодом начинается до (или в то же время), что и DateTo, я хочу, чтобы вместо этого отображался «DateTo».

Я не понимаю, как я мог бы использовать функцию max() или min() в этом случае. Из-за пробелов, которые могут быть во временных интервалах.

У вас есть какие-нибудь идеи?

Я использую MS SQL 2012

Заранее спасибо!

редактировать: как прокомментировано. Острова могут быть моим решением?


sql sql-server gaps-and-islands row-number
person Andreas    schedule 18.06.2014    source источник
comment
Вы пытались использовать функции LAG() или LEAD()?   -  person Blood-HaZaRd    schedule 18.06.2014
comment
Нет, нет. Я не знаком с этой функцией. Я погуглю. :)   -  person Andreas    schedule 18.06.2014
comment
Я не думаю, что это то, что мне нужно. Это поймает следующую дату, даже если между dateto и datefrom есть промежутки.   -  person Andreas    schedule 18.06.2014
comment
Я думаю, что то, что вы хотите, называется методом gaps-and-islands для определения прогонов/пробелов в данных.   -  person Dave Cullum    schedule 18.06.2014
comment
Это может как-то сработать. Я посмотрю на это!   -  person Andreas    schedule 18.06.2014

Ответы (1)


arrow_upward
8
arrow_downward

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

Code | DateStart           | DateFrom            | Overlap
-----+---------------------+---------------------+---------
1006 | 2014-06-18 07:00:00 | 2014-06-18 19:00:00 | 0 
1006 | 2014-06-18 08:10:00 | 2014-06-18 10:00:00 | 1 
1006 | 2014-06-18 16:00:00 | 2014-06-18 20:30:00 | 0 <- don't overlap with 
                                                        previous but overlap
                                                        with the first

Чтобы улучшить этот PrevStop нужно стать LastStop и иметь максимальное значение предыдущего DateFrom для Code

With N AS (
  SELECT Code, DateFrom, DateTo
       , LastStop = MAX(DateTo) 
                    OVER (PARTITION BY Code ORDER BY DateFrom, DateTo 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN LastStop Is Null Then 1
                          WHEN LastStop < DateFrom Then 1
                          ELSE 0
                    END)
                 OVER (PARTITION BY Code ORDER BY DateFrom, LastStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

Демонстрация SQLFiddle

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING нужен для удаления текущей строки из файла MAX.

Старый ответ

Этот запрос работает только каждый раз, когда период не полностью находится внутри прецедента.

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

With N AS (
  SELECT Code, DateFrom, DateTo
       , PrevStop = LAG(DateTo, 1, NULL) 
                    OVER (PARTITION BY Code ORDER BY DateFrom)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN PrevStop Is Null Then 1
                          WHEN PrevStop < DateFrom Then 1
                          ELSE 0
                     END)
                 OVER (PARTITION BY Code ORDER BY PrevStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

демонстрация SQLFiddle с некоторыми данными, добавленными для проверки блокировать по тому же коду/день

Поиск запроса для стартера блока проверяет каждую строку, являются ли они первыми для кода (PrevStop IS NULL) или находятся ли они за пределами предыдущей (PrevStop < DateFrom).

Оконный SUM извлекает только предыдущую строку с помощью ORDER, чтобы создать значение константы для блока связанных данных, например, с тестовыми данными, которые мы получим

Code | DateStart           | DateFrom            | Starter | Block
-----+---------------------+---------------------+---------+------
1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00 |       1 |     1
1006 | 2014-06-18 08:10:00 | 2014-06-18 06:00:00 |       0 |     1
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       0 |     1
1006 | 2014-06-18 07:00:00 | 2014-06-18 07:30:00 |       1 |     2
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       1 |     3
1006 | 2014-06-18 08:10:00 | 2014-06-18 09:00:00 |       0 |     3
3006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 |       1 |     1
3006 | 2014-06-18 09:00:00 | 2014-06-18 10:00:00 |       1 |     2

группировка по Code и Block получаем результат

person Serpiton    schedule 19.06.2014
comment
Кажется, что это не делает трюк. Я получаю несколько строк с одним и тем же кодом, даже если они находятся в одном временном интервале. - person Andreas; 25.06.2014
comment
Похоже, проблема возникает из-за того, что существует более 1 с одинаковым временем запуска? sqlfiddle.com/#!6/a5c59/1 - person Andreas; 02.07.2014