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

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

/***** DATA EXAMPLE *****/
DECLARE @T TABLE (d1 DATETIME, d2 DATETIME)
INSERT INTO @T (d1, d2)
        SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31' 
  UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10' 
  UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09' 
  UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08' 
  UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16' 
  UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13' 

/***** INTERVAL ANALYSIS *****/
WHILE (1=1)  BEGIN
  UPDATE t1 SET t1.d2 = t2.d2
  FROM @T AS t1 INNER JOIN @T AS t2 ON 
            DATEADD(day, 1, t1.d2) BETWEEN t2.d1 AND t2.d2 
  IF @@ROWCOUNT = 0 BREAK
END

/***** RESULT *****/
SELECT StartDate = MIN(d1) , EndDate = d2
FROM @T
GROUP BY d2
ORDER BY StartDate, EndDate

/***** OUTPUT *****/
/*****
StartDate   EndDate
2010-01-01  2010-06-13 
2010-06-15  2010-08-16 
2010-11-01  2010-12-31 
*****/

Ответ 1

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

На Интервалы Даты Упаковки есть другая тема.

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

SELECT 
       s1.StartDate,
       --t1.EndDate 
       MIN(t1.EndDate) AS EndDate
FROM @T s1 
INNER JOIN @T t1 ON s1.StartDate <= t1.EndDate
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate) 
WHERE NOT EXISTS(SELECT * FROM @T s2 
                 WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate) 
GROUP BY s1.StartDate 
ORDER BY s1.StartDate

Результат:

StartDate  | EndDate
2010-01-01 | 2010-06-13
2010-06-15 | 2010-06-25
2010-06-26 | 2010-08-16
2010-11-01 | 2010-12-31

Ответ 2

Вы спрашивали об этом еще в 2010 году, но не указали какую-либо конкретную версию.

Ответ для людей на SQL Server 2012+

WITH T1
     AS (SELECT *,
                MAX(d2) OVER (ORDER BY d1) AS max_d2_so_far
         FROM   @T),
     T2
     AS (SELECT *,
                CASE
                  WHEN d1 <= DATEADD(DAY, 1, LAG(max_d2_so_far) OVER (ORDER BY d1))
                    THEN 0
                  ELSE 1
                END AS range_start
         FROM   T1),
     T3
     AS (SELECT *,
                SUM(range_start) OVER (ORDER BY d1) AS range_group
         FROM   T2)
SELECT range_group,
       MIN(d1) AS d1,
       MAX(d2) AS d2
FROM   T3
GROUP  BY range_group

Который возвращается

+-------------+------------+------------+
| range_group |     d1     |     d2     |
+-------------+------------+------------+
|           1 | 2010-01-01 | 2010-06-13 |
|           2 | 2010-06-15 | 2010-08-16 |
|           3 | 2010-11-01 | 2010-12-31 |
+-------------+------------+------------+

DATEADD(DAY, 1 используется, потому что ваши желаемые результаты показывают, что вы хотите, чтобы период, заканчивающийся на 2010-06-25 был свернут в один, начиная с 2010-06-26. В других случаях использования это может потребовать корректировки.

Ответ 3

Вот решение с помощью всего трех простых сканирований. Нет CTE, нет рекурсии, нет объединений, нет обновлений таблиц в цикле, нет "группировки по" - в результате это решение должно масштабироваться лучше (я думаю). Я думаю, что количество сканов может быть уменьшено до двух, если минимальные и максимальные даты известны заранее; самой логике нужно только два сканирования - найти пробелы, примененные дважды.

declare @datefrom datetime, @datethru datetime

DECLARE @T TABLE (d1 DATETIME, d2 DATETIME)

INSERT INTO @T (d1, d2)

SELECT '2010-01-01','2010-03-31' 
UNION SELECT '2010-03-01','2010-06-13' 
UNION SELECT '2010-04-01','2010-05-31' 
UNION SELECT '2010-06-15','2010-06-25' 
UNION SELECT '2010-06-26','2010-07-10' 
UNION SELECT '2010-08-01','2010-08-05' 
UNION SELECT '2010-08-01','2010-08-09' 
UNION SELECT '2010-08-02','2010-08-07' 
UNION SELECT '2010-08-08','2010-08-08' 
UNION SELECT '2010-08-09','2010-08-12' 
UNION SELECT '2010-07-04','2010-08-16' 
UNION SELECT '2010-11-01','2010-12-31' 

select @datefrom = min(d1) - 1, @datethru = max(d2) + 1 from @t

SELECT 
StartDate, EndDate
FROM
(
    SELECT 
    MAX(EndDate) OVER (ORDER BY StartDate) + 1 StartDate,
    LEAD(StartDate ) OVER (ORDER BY StartDate) - 1 EndDate
    FROM
    (
        SELECT 
        StartDate, EndDate
        FROM
        (
            SELECT 
            MAX(EndDate) OVER (ORDER BY StartDate) + 1 StartDate,
            LEAD(StartDate) OVER (ORDER BY StartDate) - 1 EndDate 
            FROM 
            (
                SELECT d1 StartDate, d2 EndDate from @T 
                UNION ALL 
                SELECT @datefrom StartDate, @datefrom EndDate 
                UNION ALL 
                SELECT @datethru StartDate, @datethru EndDate
            ) T
        ) T
        WHERE StartDate <= EndDate
        UNION ALL 
        SELECT @datefrom StartDate, @datefrom EndDate 
        UNION ALL 
        SELECT @datethru StartDate, @datethru EndDate
    ) T
) T
WHERE StartDate <= EndDate

Результат:

StartDate   EndDate
2010-01-01  2010-06-13
2010-06-15  2010-08-16
2010-11-01  2010-12-31

Ответ 4

В этом решении я создал временную таблицу календаря, которая хранит значение для каждого дня в диапазоне. Этот тип таблицы можно сделать статическим. Кроме того, я сохраняю 400 нечетных дат, начиная с 2009-12-31. Очевидно, что если ваши даты охватывают более широкий диапазон, вам потребуется больше значений.

Кроме того, это решение будет работать только с SQL Server 2005+, поскольку я использую CTE.

With Calendar As
    (
    Select DateAdd(d, ROW_NUMBER() OVER ( ORDER BY s1.object_id ), '1900-01-01') As [Date]
    From sys.columns as s1
        Cross Join sys.columns as s2
    )
    , StopDates As
    (
    Select C.[Date]
    From Calendar As C
        Left Join @T As T
            On C.[Date] Between T.d1 And T.d2
    Where C.[Date] >= ( Select Min(T2.d1) From @T As T2 )
        And C.[Date] <= ( Select Max(T2.d2) From @T As T2 )
        And T.d1 Is Null
    )
    , StopDatesInUse As
    (
    Select D1.[Date]
    From StopDates As D1
        Left Join StopDates As D2
            On D1.[Date] = DateAdd(d,1,D2.Date)
    Where D2.[Date] Is Null
    )
    , DataWithEariestStopDate As 
    (
    Select *
    , (Select Min(SD2.[Date])
        From StopDatesInUse As SD2
        Where T.d2 < SD2.[Date] ) As StopDate
    From @T As T
    )
Select Min(d1), Max(d2)
From DataWithEariestStopDate
Group By StopDate
Order By Min(d1)

EDIT Проблема с использованием дат в 2009 году не имеет ничего общего с окончательным запросом. Проблема в том, что таблица "Календарь" недостаточно велика. Я запустил таблицу календаря в 2009-12-31. Я пересмотрел его в 1900-01-01.

Ответ 5

Попробуйте это

;WITH T1 AS
(
    SELECT d1, d2, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
    FROM @T
), NUMS AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
    FROM T1 A
    CROSS JOIN T1 B
    CROSS JOIN T1 C
), ONERANGE AS 
(
    SELECT DISTINCT DATEADD(DAY, ROW_NUMBER() OVER(PARTITION BY T1.R ORDER BY (SELECT 0)) - 1, T1.D1) AS ELEMENT
    FROM T1
    CROSS JOIN NUMS
    WHERE NUMS.R <= DATEDIFF(DAY, d1, d2) + 1
), SEQUENCE AS
(
    SELECT ELEMENT, DATEDIFF(DAY, '19000101', ELEMENT) - ROW_NUMBER() OVER(ORDER BY ELEMENT) AS rownum
    FROM ONERANGE
)
SELECT MIN(ELEMENT) AS StartDate, MAX(ELEMENT) as EndDate
FROM SEQUENCE
GROUP BY rownum

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

Затем определите взаимосвязь между тем, как увеличивается число дат и как ведут себя номера строк. Разница остается постоянной в пределах существующего диапазона/острова. Как только вы попадаете на новый остров данных, разница между ними увеличивается, потому что дата увеличивается более чем на 1, а число строк увеличивается на 1.

Ответ 6

Идея состоит в том, чтобы смоделировать алгоритм сканирования интервалов слияния. Мое решение гарантирует, что оно работает в широком спектре реализаций SQL. Я тестировал его на MySQL, Postgres, SQL-Server 2017, SQLite и даже Hive.

Предполагается, что схема таблицы следующая.

CREATE TABLE t (
  a DATETIME,
  b DATETIME
);

Мы также предполагаем, что интервал полуоткрыт, как [a, b).

Когда (a, i, j) находится в таблице, это показывает, что есть j интервалов, покрывающих a, и я интервалов, покрывающих предыдущую точку.

CREATE VIEW r AS 
SELECT a,
       Sum(d) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS i,
       Sum(d) OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) AS j
FROM  (SELECT a, Sum(d) AS d
       FROM   (SELECT a,  1 AS d FROM t
               UNION ALL
               SELECT b, -1 AS d FROM t) e
       GROUP  BY a) f;

Мы производим все конечные точки в объединении интервалов и соединяем соседние. Наконец, мы производим набор интервалов, выбирая только нечетные строки.

SELECT a, b
FROM (SELECT a,
             Lead(a)      OVER (ORDER BY a) AS b,
             Row_number() OVER (ORDER BY a) AS n
      FROM   r
      WHERE  j=0 OR i=0 OR i is null) e
WHERE  n%2 = 1;

Я создал образец DB-Fiddle и SQL-Fiddle. Я также написал сообщение в блоге о интервалах объединения в SQL.

Ответ 7

Это мой вопрос. Как свернуть перекрывающиеся временные интервалы - конец может быть нулевым, но начало никогда не нулевым

Приведенный ниже код не работает с моими данными. ВЫБЕРИТЕ s1.StartDate, --t1.EndDate MIN (t1.EndDate) AS EndDate ОТ @T s1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ @T t1 ВКЛ s1.StartDate <= t1.EndDate И НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ @T t2, ГДЕ t1.EndDate> = t2.StartDate И t1.EndDate <t2.EndDate), ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ @T s2, ГДЕ s1.StartDate> s2.StartDate И s1.StartDate <= s2.EndDate) GROUP BY s1.StartDate ORDER BY s1.StartDate