Аналитические функции (9)

analytical_9.sql

/* Аналитические функции (9) 
Создать таблицу  из произвольного количества >1000 строк с двумя псевдослучайными моментами времени из одних суток: 
начало телефонного разговора, конец телефонного разговора. Длительность разговора не должна превышать 200 секунд. 
Момент начала и конца разговора включаются в разговор. 
Написать функцию, которая для произвольного момента времени определяет число разговоров в данный момент времени. 
Написать запрос, который определит максимальное число одновременных разговоров в течение суток.
*/
--Подготавливаем тестовые данные.
CREATE TABLE Random_Dates_TAB (ID NUMBER GENERATED ALWAYS AS IDENTITY,start_date DATE, end_date DATE);

-- Заполняем таблицу тестовыми данными
DECLARE
  l_hours_in_day NUMBER := 24;
  l_mins_in_day  NUMBER := 24 * 60;
  l_secs_in_day  NUMBER := 24 * 60 * 60;
  l_start_date   DATE;
BEGIN
  FOR i IN 1 .. 1100
  LOOP
    l_start_date := SYSDATE +
                    TRUNC(dbms_random.value(0, 1000)) / l_hours_in_day +
                    TRUNC(DBMS_RANDOM.value(0, 1000)) / l_mins_in_day +
                    TRUNC(DBMS_RANDOM.value(0, 1000)) / l_secs_in_day;
    INSERT INTO random_dates_t
      (start_date, end_date)
    VALUES
      (l_start_date, (l_start_date + 200 / 24 / 60 / 60));
  END LOOP;
END;


-- Определяем типы
CREATE OR REPLACE TYPE t_record AS OBJECT (v_date DATE, v_count NUMBER);
CREATE OR REPLACE TYPE TabCount AS TABLE OF t_record;

/* Функция, которая для произвольного момента времени определяет число разговоров в данный момент времени. */
CREATE OR REPLACE FUNCTION FNC_Get_Counts_Number 
RETURN TabCount 
    IS
  l_Count TabCount;
BEGIN
  SELECT t_record(TRUNC(t.start_date), COUNT(*))
    BULK COLLECT
    INTO l_Count
    FROM random_dates_t t
   WHERE TRUNC(t.start_date) IN
         (SELECT to_date(TRUNC(dbms_random.value(to_char(MIN(t.start_date), 'J'), to_char(MAX(t.end_date), 'J'))), 'J') AS rd
            FROM random_dates_t t)
   GROUP BY TRUNC(t.start_date);
  RETURN l_Count;

END FNC_Get_Counts_Number;

-- Вызов функции
SELECT * FROM TABLE(FNC_Get_Counts_Number);

/* Запрос, который определит максимальное число одновременных разговоров в течение суток */
SELECT max_secs_in_day.*
  FROM (SELECT t3.s_date,
               SUM(sum_secs) AS sum_secs_in_day,
               row_number() OVER(ORDER BY SUM(sum_secs) DESC) AS rn
          FROM (SELECT t2.s_date,
                       t2.sum_secs
                  FROM (SELECT TRUNC(t.start_date) AS s_date,
                               (t.end_date - LEAD(t.start_date)
                                OVER(ORDER BY t.start_date)) * 86400 AS sum_secs
                          FROM random_dates_t t
                         ORDER BY t.start_date) t2
                 WHERE t2.sum_secs > 0) t3
         GROUP BY t3.s_date
         ORDER BY sum_secs_in_day DESC) max_secs_in_day
 WHERE max_secs_in_day.rn = 1;

Создать таблицу  из произвольного количества >1000 строк с двумя псевдослучайными моментами времени из одних суток: начало телефонного разговора, конец телефонного разговора. Длительность разговора не должна превышать 200 секунд. Момент начала и конца разговора включаются в разговор.
Написать функцию, которая для произвольного момента времени определяет число разговоров в данный момент времени.
Написать запрос, который определит максимальное число одновременных разговоров в течение суток