Использование PARTITION LEFT JOIN

Есть таблица с перечнем временных зон (days):

Есть данные о начислениях по абоненту по временным зонам (usrs):

Необходимо получить результат: если начисления по абоненту по данной временной зоне не было, то выдавать название абонента, временную зону и начисление, равное 0. Т.е.:

Решение:

SELECT u.ur,

       d.name,

       nvl(u.n, 0) as val

FROM   days d

LEFT   JOIN usrs u PARTITION BY(u.ur) ON d.name = u.d;

Нарастающий итог с условием

Есть таблица (hnull) с начислениями какой-либо величины за каждый год:

Необходимо без применения PL/SQL посчитать величину начисления нарастающим итогом (growth) в порядке увеличения года (year). При этом если начисление текущего года (val) меньше нарастающего итога предыдущих годов, то этот месяц не вносит свой вклад в нарастающий итог. Т.е. результат будет:

Решение:

SELECT val, year, growth

FROM  hnull

MODEL

DIMENSION BY (YEAR)

MEASURES (val, 0 growth)

RULES(

       growth [YEAR] = case

          when val[cv()] <  nvl(growth[cv()-1],0) then growth[cv()-1]

        else

          val[cv()] + nvl(growth[cv()-1],0)

       end

)

ORDER  BY YEAR;

Расписание запусков

Есть матрица расписания запусков:

Первая строка – 15-и минутные интервалы, вторая строка часовые интервалы, третья строка
дни недели, четвертая дни месяца, пятая месяцы года. С помощью данной матрицы задается периодичность запусков.

Требуется написать функцию на Oracle PL/SQL, которая бы возвращала дату следующего запуска (тип Date) от двух входных параметров:

Первый параметр (тип Date): дата, от которой ведется отчет;

Второй параметр (тип Varchar2): это текстовая переменная, в которой перечислены все выбранные ячейки. Ячейки разделены «,» (запятой), а строки разделены «;» (точкой с запятой), например, для данного рисунка расписание будет выглядеть следующим образом: 0,45;0,4,8,12,17,22;2,6;1,2,3,4,5,11,18,24;1,2,3,9,11;

Контрольный пример:

Дата отсчета: 09.07.2010 23:36

Строка: 0,45;12;1,2,6;3,6,14,18,21,24,28;1,2,3,4,5,6,7,8,9,10,11,12;

Результат: 18.07.2010 12:00

Примечание. В данном примере, используется американский календарь, в котором 1 – это воскресенье, 2 – понедельник и т.д.

Решение: 

DECLARE

    p_interval   VARCHAR2(250);

    d1           TIMESTAMP := SYSDATE;

    d2           TIMESTAMP := d1;

    p_result_day TIMESTAMP;

    v_byminute  VARCHAR2(50);

    v_byhour    VARCHAR2(50);

    v_byweekday VARCHAR2(50);

    v_bymontday VARCHAR2(50);

    v_bymonth   VARCHAR2(50);

    v_slr_name  VARCHAR2(30) := 'slr' || to_char(systimestamp, 'yyymmddhh24missff');

BEGIN

    p_interval  := '0,15,45;0,4,8,12,17,22;2,4,5,6;1,2,3,4,5,11,18,24;1,2,3,9,11;'; -- в конце можно не ставить ;

    v_byminute  := regexp_substr(p_interval, '[^;]+', 1, 1);

    v_byhour    := regexp_substr(p_interval, '[^;]+', 1, 2);

    v_byweekday := regexp_substr(p_interval, '[^;]+', 1, 3);

    v_bymontday := regexp_substr(p_interval, '[^;]+', 1, 4);

    v_bymonth   := regexp_substr(p_interval, '[^;]+', 1, 5);

    -- преобразуем дни в наименования

    SELECT listagg(regexp_substr('SUN|MON|TUE|WED|THU|FRI|SAT', '[^|]+', 1, dy),',') within GROUP(ORDER BY dy)

    INTO   v_byweekday

    FROM   (SELECT regexp_substr(v_byweekday, '[^,]+', 1, LEVEL) dy

            FROM   dual

            CONNECT BY LEVEL <= regexp_count(v_byweekday, ',') + 1);

    -- создаем уникальный календарь

    dbms_scheduler.create_schedule(schedule_name => v_slr_name,

    repeat_interval => 'FREQ=YEARLY;BYMONTHDAY='||v_bymontday||';BYDAY=' || v_byweekday||';BYHOUR='||v_byhour||';BYMINUTE='||v_byminute);

    dbms_scheduler.evaluate_calendar_string(calendar_string   => v_slr_name,

                                            start_date        => d1,

                                            return_date_after => d2,

                                            next_run_date     => p_result_day);

    dbms_output.put_line('Next date is ' || to_char(p_result_day, 'dd.mm.yyyy hh24:mi:ss'));

    -- удаляем созданный календарь

    dbms_scheduler.drop_schedule(schedule_name => v_slr_name);

END;

/

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

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

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