Last active
April 13, 2020 22:31
-
-
Save aykononov/c34092e3d4cb6b2e2e4fe5b4e50ad494 to your computer and use it in GitHub Desktop.
Аналитические функции (9)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Аналитические функции (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; |
Author
aykononov
commented
Nov 14, 2019
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment