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

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

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 - дата окончания интервала

Удаление дублей из таблицы

Необходимо удалить задублированные строки в таблице. Задача более типична для Warehousing (OLAP), чем  OLTP.

Для определения дубликатов используем функцию row_number(). 

Уникальность строк в нижеследующем примере будем определятя по значениям в колонках skp_client и contact. Это не означает, что в таблице есть уникальный индекс по этим двум колонкам. Сортируем задублированные строки по колонке change_time в обратном порядке, самые последние строки стоят на первом месте.

Решение:

DELETE FROM owner_dwh.ft_pilot_skill trg

WHERE  trg.rowid IN (SELECT src.rowid

                     FROM   (SELECT /*+ parallel(8) */

                              ROWID,

                              row_number() over(PARTITION BY skp_client, contact

                                                ORDER BY change_time DESC) rn

                             FROM   owner_dwh.ft_pilot_skill t) src

                     WHERE  rn > 1);

Генерация данных

Сформировать выборку, состоящую из одной колонки, в которой расположены последовательные числовые значения от 1 до 500. После каждой 5-й строки результата должна быть пустая строка. Реализовать на Oracle SQL (без использования PL/SQL).

Решение:

SELECT decode(mlvl, 0, NULL, mlvl) AS vals

FROM   (SELECT decode(MOD(LEVEL / 2 - 0.5, 5), 0, 0, LEVEL / 2) mlvl

        FROM   dual

        WHERE  LEVEL > 1

        CONNECT BY LEVEL <= 1000)

WHERE  trunc(mlvl) = mlvl

OR     mlvl = 0;