Группировка результатов SQL только для непрерывных интервалов времени (oracle sql)

У меня проблемы с непрерывными временными интервалами. У меня есть следующие данные в таблице.

ID      startDate   endDate
------- ----------- ------------
5549    2008-05-01  4712-12-31
5567    2008-04-17  2008-04-30 1
5567    2008-05-01  2008-07-31 1
5567    2008-09-01  4712-12-31 2
5569    2008-01-01  2008-08-31
5569    2008-09-01  2008-09-20
5569    2008-11-01  4712-12-31  
5589    2008-04-18  2008-04-30
5589    2008-05-01  4712-12-31
5667    2008-05-01  2008-05-31
5667    2008-06-01  2008-07-31
5667    2008-08-01  2008-09-30
5667    2008-09-30  2008-12-31
5828    2008-06-03  4712-12-31
5867    2008-06-03  4712-12-31
6167    2008-11-01  4712-12-31

В заданный период времени приведенные ниже идентификаторы не имеют непрерывных интервалов времени, я хочу исключить их. Я хочу исключить идентификатор (5567,5569), где нет непрерывных интервалов времени. Результаты должны быть такими, как показано ниже:

ID      START_DATE  END_DATE
------- ----------- -----------
5549    2008-05-01  4712-12-31
5589    2008-04-18  4712-12-31
5667    2008-05-01  2008-12-31
5828    2008-06-03  4712-12-31
5867    2008-06-03  4712-12-31
6167    2008-11-01  4712-12-31

Может ли кто-нибудь помочь этому сценарию?

Прямо сейчас я использую ниже SQL для непрерывных результатов интервала времени

  SELECT id, min (start_date) period_start, max (end_date) period_end
    FROM (SELECT id,
                 start_date,
                 end_date,
                 max (contig) OVER (PARTITION BY id ORDER BY end_date)
                     contiguous_group
            FROM (SELECT id,
                         start_date,
                         end_date,
                         CASE
                             WHEN    lag (end_date)
                                     OVER (PARTITION BY id ORDER BY end_date) !=
                                         start_date - 1
                                  OR row_number ()
                                     OVER (PARTITION BY id ORDER BY end_date) =
                                         1 THEN
                                 row_number ()
                                     OVER (PARTITION BY id ORDER BY end_date)
                             ELSE
                                 NULL
                         END
                             contig
                    FROM t2))
GROUP BY id, contiguous_group
ORDER BY id, period_start

sql gaps-and-islands
person Robert    schedule 10.02.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Попробуйте что-то вроде следующего:

WITH mytbl AS (
SELECT 5549 as id, to_date('2008-05-01', 'yyyy-mm-dd') start_date, to_date('4712-12-31', 'yyyy-mm-dd') end_date FROM dual
UNION ALL SELECT 5567, to_date('2008-04-17', 'yyyy-mm-dd'), to_date('2008-04-30', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5567, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('2008-07-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5567, to_date('2008-09-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5569, to_date('2008-01-01', 'yyyy-mm-dd'), to_date('2008-08-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5569, to_date('2008-09-01', 'yyyy-mm-dd'), to_date('2008-09-20', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5569, to_date('2008-11-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5589, to_date('2008-04-18', 'yyyy-mm-dd'), to_date('2008-04-30', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5589, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5667, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('2008-05-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5667, to_date('2008-06-01', 'yyyy-mm-dd'), to_date('2008-07-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5667, to_date('2008-08-01', 'yyyy-mm-dd'), to_date('2008-09-30', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5667, to_date('2008-09-30', 'yyyy-mm-dd'), to_date('2008-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5828, to_date('2008-06-03', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 5867, to_date('2008-06-03', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual
UNION ALL SELECT 6167, to_date('2008-11-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd') FROM dual),
     derivedTbl AS
         (SELECT id, start_ end_date,
                 lead (start_date) OVER (PARTITION BY id ORDER BY start_date)
                     AS next_start_date
            FROM mytbl),
     t AS
         (SELECT id, start_date, end_date
            FROM derivedTbl t1
           WHERE NOT EXISTS
                         (SELECT 1
                            FROM derivedTbl t2
                           WHERE     t2.id = t1.id
                                 AND t2.next_start_date IS NOT NULL
                                 AND t2.next_start_date > t2.end_date + 1))
  SELECT id, min (start_date) period_start, max (end_date) period_end
    FROM t
GROUP BY id
ORDER BY id;

ВЫВОД:

ID      PERIOD_START  PERIOD_END
------- ------------- ----------
5549    2008-05-01    4712-12-31
5589    2008-04-18    4712-12-31
5667    2008-05-01    2008-12-31
5828    2008-06-03    4712-12-31
5867    2008-06-03    4712-12-31
6167    2008-11-01    4712-12-31
person Rachcha    schedule 10.02.2014

arrow_upward
0
arrow_downward

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

select id, min(start_date) as period_start, max(end_date) as period_end
from (select t.*, sum(seqflag) over (id order by start_date) as groupid
      from (select t2.*,
                   (case when end_date = lag(end_date) over (partition by id order by end_date) 
                         then 0 else 1
                    end) as seqflag
            from t2 t2
           ) t
     ) t
group by id, groupid;

Скрипт SQL находится здесь.

person Gordon Linoff    schedule 10.02.2014
comment
Привет, Гордан и Рачача. Большое спасибо за вашу быструю помощь, мы проверим и сообщим вам. Я немного беден в sql.... есть ли способ получить общий sql?. Прямо сейчас в базе данных у нас есть миллионы записей, sql должен проверять непрерывные временные интервалы и должен устранять идентификаторы интервалов. Пожалуйста, дайте мне направление. заранее спасибо - person Robert; 12.02.2014