Расчёт интервалов

Таблица статистических данных по маршрутизаторам, описывается так:

CREATE TABLE tld(recdate DATE, -- час, к которому относится строка статистики

                 objname CHAR(4 CHAR), -- имя маршрутизатора

                 traf NUMBER, -- трафик маршрутизатора

                 counter1 NUMBER, -- число занятых каналов маршрутизатора

                 CONSTRAINT test_peak_hour_pk primary key(recdate, objname));

Статистика снимается с маршрутизаторов каждый час и recdate указывает начало этого часа (т.е. всегда в 0 минуту и 0 секунду часа).
В некоторые часы статистика может отсутствовать.

Отчетный интервал времени, заданный двумя моментами времени, включительно: '2011-04-11 00:00' и '2011-04-13 00:59'.
Необходимо вывести все интервалы отсутствия данных в таблице по каждому объекту в пределах отчетного интервала.
Выборка должна содержать поля recdate,  nехт_recdate, objname, где recdate и next_recdate представляют границы интервала отсутствия данных (включительно) и имеют формат вида '2011-04-12 15:00' и '2011-04-12 15:59' соответственно. Выборка должна  быть отсортирована по возрастанию по объекту и затем по дате.
Например, для отчетного интервала сегодня с 0:00 по 23:59 при наличии данных в 1,2 и 12 часы для объекта Х, его интервалы отсутствия данных будут следующими (3 строки): [0:00;0:59;X], [3:00;11:59;X], [13:00;23:59;X].

Решение:

WITH tl AS

(SELECT DISTINCT objname || to_char(tlp.st, 'dd.mm.yyyy hh24:mi:ss') AS ost,

                  objname || to_char(tlp.ed, 'dd.mm.yyyy hh24:mi:ss') oed,

                  tlp.st,

                  tlp.ed,

                  objname

  FROM   tld,

         (SELECT :st_dt + (LEVEL - 1) / 24 AS st,

                 :st_dt + LEVEL / 24 AS ed

          FROM   dual

          CONNECT BY LEVEL <= (:ed_dt - :st_dt) * 24) tlp

  WHERE  tld.recdate >= :st_dt

  AND    tld.recdate <= (:ed_dt - 59 / (24 * 60))

  MINUS

  SELECT objname || to_char(recdate, 'dd.mm.yyyy hh24:mi:ss'),

         objname || to_char((recdate + 1 / 24), 'dd.mm.yyyy hh24:mi:ss'),

         recdate st,

         (recdate + 1 / 24) ed,

         objname

  FROM   tld

  WHERE  recdate >= :st_dt

  AND    recdate <= :ed_dt)

SELECT MIN(st),

       MAX(ed) - 1 / (24 * 60),

       objname

FROM   (SELECT ost,

               oed,

               st,

               ed,

               objname,

               connect_by_root(ost) AS pr

        FROM   tl

        CONNECT BY ost = PRIOR oed

        START  WITH ost IN (SELECT ost

                            FROM   (SELECT ost,

                                           (st - nvl(lag(st) over(ORDER BY objname, st), st)) * 24 AS pr

                                    FROM   tl)

                            WHERE  pr != 1))

GROUP  BY objname, pr

ORDER  BY objname, MIN(st);

Переменные :st_dt - дата начала интервала, :ed_dt - дата окончания интервала