Периоды разделения даты проверки являются непрерывными

Question

У меня есть данные в таблице Ingres что-то вроде этого;

REF     FROM_DATE   TO_DATE 
A       01.04.1997  01.04.1998
A       01.04.1998  27.05.1998
A       27.05.1998  01.04.1999

B       01.04.1997  01.04.1998
B       01.04.1998  26.07.1998
B       01.04.2012  01.04.2013

У некоторых ссылок есть непрерывные периоды от минимума (from_date) до максимума (to_date), но у некоторых есть промежутки в периоде.

Я хотел бы знать, как в Ingres SQL определить, какие ссылки имеют пробелы в периодах дат.

Я делаю это в виде сценария оболочки Unix, вызывающего команду Ingres sql.

Пожалуйста, порекомендуйте.

Answer 1

Я не знаком с функциями даты в Ingres. Позвольте мне предположить, что - получает разницу между двумя датами в днях.

Если в данных нет совпадений, вы можете довольно легко делать то, что хотите. Если пропусков нет, разница между минимальной и максимальной датой совпадает с суммой различий в каждой строке. Если разница больше 0, то есть пробелы.

Так:

select ref,
       ((max(to_date) - min(from_date)) -
        sum(to_date - from_date)
       ) as total_gaps
from t
group by ref;

Я считаю, что это будет работать в вашем случае. В других случаях может возникнуть проблема "off-by-1" в зависимости от того, включена или нет дата окончания в период.

Answer 2

Этот запрос работает в SQL SERVER. PARTITION команда ANSI SQL, я не знаю, поддерживает ли ее INGRES. если раздел поддерживается, вероятно, у вас будет эквивалент Dense_Rank()

select * 
INTO #TEMP
from (
select 'A' as Ref, Cast('1997-01-04' as DateTime) as From_date, Cast('1998-01-04' as DateTime) as to_date
union
select 'A' as Ref, Cast('1998-01-04' as DateTime) as From_date, Cast('1998-05-27' as DateTime) as to_date
union
select 'A' as Ref, Cast('1998-05-27' as DateTime) as From_date, Cast('1999-01-04' as DateTime) as to_date
union
select 'B' as Ref, Cast('1997-01-04' as DateTime) as From_date, Cast('1998-01-04' as DateTime) as to_date
union
select 'B' as Ref, Cast('1998-01-04' as DateTime) as From_date, Cast('1998-07-26' as DateTime) as to_date
union
select 'B' as Ref, Cast('2012-01-04' as DateTime) as From_date, Cast('2013-01-04' as DateTime) as to_date
) X

    SELECT *
    FROM
    (
          SELECT Ref, Min(NewStartDate) From_Date, MAX(To_Date) To_Date, COUNT(1) OVER (PARTITION BY Ref ) As [CountRanges]
          FROM
          (

                SELECT Ref, From_Date, To_Date,
                      NewStartDate = Range_UNTIL_NULL.From_Date + NUMBERS.number,
                      NewStartDateGroup =     DATEADD(d, 
                                                  1 - DENSE_RANK() OVER (PARTITION BY Ref ORDER BY Range_UNTIL_NULL.From_Date + NUMBERS.number), 
                                                  Range_UNTIL_NULL.From_Date + NUMBERS.number)
                FROM 
                (

                    --This subquery is necesary needed to "expand the To_date" to the next day and allowing it to be null 
                      SELECT 
                            REF, From_date, DATEADD(d, 1, ISNULL(To_Date, From_Date)) AS to_date
                      FROM #Temp T1
                      WHERE
                            NOT  EXISTS (   SELECT * 
                                            FROM #Temp t2 
                                            WHERE  T1.Ref = T2.Ref and T1.From_Date > T2.From_Date AND T2.To_Date IS NULL
                                        )
                )  AS Range_UNTIL_NULL
                CROSS APPLY  Enumerate ( ABS(DATEDIFF(d, From_Date, To_Date))) AS NUMBERS
                      ) X
          GROUP BY Ref, NewStartDateGroup
    ) OVERLAPED_RANGES_WITH_COUNT
--  WHERE OVERLAPED_RANGES_WITH_COUNT.CountRanges >= 2  --This filter is for identifying ranges that have at least one gap
    ORDER BY Ref, From_Date

Результат для данного примера:

Ref  From_Date               To_Date                 CountRanges
---- ----------------------- ----------------------- -----------
A    1997-01-04 00:00:00.000 1999-01-05 00:00:00.000 1
B    1997-01-04 00:00:00.000 1998-07-27 00:00:00.000 2
B    2012-01-04 00:00:00.000 2013-01-05 00:00:00.000 2

как вы можете видеть, у тех ссылок, имеющих "CountRanges" > 1, есть хотя бы один пробел

Этот ответ выходит далеко за рамки первоначального вопроса, потому что:

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