Здравствуйте, __kain, Вы писали:
__>Наконец принес код. Прошу не вчитываться в него, это только пробный вариант, да и непонятен он будет. Просто взгляните на картинку
Итого, что я вижу — несколько запросов, все под курсоры и далее пошла их ручная обработка в циклах...
Можно я выступлю с радикальным предложением?
А тебе точно надо все это писать на Pl/SQL? Может вынести обработку данных из базы в отдельное приложение, написанное на Java или .NET?
Меня вообще немного коробит, когда в SQL-коде появляются курсоры и циклы, а тут в относительно небольшой процедуре 4 вложенных цикла да еще и условное ветвление в теле, а все запросы — только для курсоров...
Нафига, а?
Императивная обработка данных гораздо лучше делается в императивных языках. А ООП для данной задачи позволит лучше ее декомпозировать, построить более красивое и удобное решение. Из базы тянуть естественно не все, а только то что надо, отдельными запросами (либо более "чистыми" хранимками, либо вообще через view)
SQL хорош для описания декларативных запросов, которые потом база эффективно оптимизирует и выбирает нужные данных. Оптимизировать циклы и прочую императивщину база умеет очень плохо. Фактически, вынеся императивную обработку из процедуры на верхний уровень можно получить даже заметное увеличение производительности (зависит от объемов данных и сложности вычислений над ними).
И уж точно будет удобнее программировать (именно в императивном стиле), чем может предложить PL/SQL...
За Java или .NET меня по голове не полгадят, но сделать по-другому мало мальский читаемый и понимаемый с первого взгляда код, видимо по-другому не выйдет.
Кстати, а что вы думаете на счет объектных возможностей Oracle? Может с помощью них получится нормально сделать?
Здравствуйте, __kain, Вы писали:
__>Здравствуйте, fmiracle! __>За Java или .NET меня по голове не полгадят, но сделать по-другому мало мальский читаемый и понимаемый с первого взгляда код, видимо по-другому не выйдет.
Это аспирантская работа? Расскажи, что это новое и перспективное направление
Серьезно же — я просто не вижу резона, почему это должно строиться чисто на PL SQL. Интерфейс приложения на чем делается? Или тоже возможностми самого Оракла?
__>Кстати, а что вы думаете на счет объектных возможностей Oracle? Может с помощью них получится нормально сделать?
К сожалению, ничего не могу сказать, не работал.
Но можно, например, писать в Оракле хранимки на Java. Непосредственно это я тоже не пробовал, но пробовал аналогичные хранимки на .net в MS SQL — для сложной императивной обработки вполне приятно.
__>Наконец принес код. Прошу не вчитываться в него, это только пробный вариант, да и непонятен он будет. Просто взгляните на картинку
Код написана с использованием курсоров, что сильно усложняет
программу (в принципе, в 95% всех задач курсоры не нужны).
Если переписать все в декларативном виде без курсоров,
программа получится значительно проще.
Здравствуйте, fmiracle, Вы писали:
F>Это аспирантская работа? Расскажи, что это новое и перспективное направление
Нет, это подсистема промышленного сервера
F>Серьезно же — я просто не вижу резона, почему это должно строиться чисто на PL SQL. Интерфейс приложения на чем делается? Или тоже возможностми самого Оракла?
Если все лежит на сервере, то во-первых, клиенту ставить ничего не нужно (учитывая слабость клиентских машин-это существенный довод). И потом, если будет где-то ошибка, в базе данных ее легче поправить будет.
2 Flying Dutchman F>Код написана с использованием курсоров, что сильно усложняет F>программу (в принципе, в 95% всех задач курсоры не нужны). F>Если переписать все в декларативном виде без курсоров, F>программа получится значительно проще.
Не понял, что вы имеете в виду? По-моему так наоборо проще, селектов в коде нету
Здравствуйте, __kain, Вы писали:
__>2 Flying Dutchman F>>Код написана с использованием курсоров, что сильно усложняет F>>программу (в принципе, в 95% всех задач курсоры не нужны). F>>Если переписать все в декларативном виде без курсоров, F>>программа получится значительно проще. __>Не понял, что вы имеете в виду? По-моему так наоборо проще, селектов в коде нету
Ну так с селектами оно проще будет, чем с курсорами. Проблема в том, что (судя по
твоему коду) ты думаешь и пишешь в процедурном стиле, тогда как для
эффективной работы на SQL нужно думать по-другому.
Как хорошо кто-то сказал (кажется, Джо Селко):
Before SQL programmers could begin working with OLTP (On-Line Transaction
Processing) systems, they had to unlearn procedural, record-oriented
programming before moving on to SQLs declarative, set-oriented programming
И этот переход к декларативному программированию сложен для
тех, кто занимался только процедурным программированием.
Для сравнения, мой текущий проект во многом похож на твой.
Я участвую в разработке большой системы для компании — поставщика
временного персонала. Программа предназначена для планирования работников,
расстановки их по сменам и т.д. Также используется несколько типов
расписаний с разнами приоритетами (точнее, это данные о доступности работника
для работы в зависимости от дня недели, например). Для получения
данных о доступности в определенный день нужно накладывать разные
типы расписаний друг на друга, а также генерировать интервалы, как у тебя.
Так вот, если я хочу с учетом всех расписаний и т.д. получить информацию
о доступности работников во времени, у меня это все делается одним запросом,
который состоит из 6 довольно простых select'ов, соединенных при помощи union.
Твой код можно сильно упростить, если переписать его чисто декларативно.
(если и не весь, то большую часть).
Если интересуют подробности, сообщи следующую информацию:
— Структуру используемых таблиц
— Точное описание того, что нужно сделать.
Здравствуйте, __kain, Вы писали:
F>>Серьезно же — я просто не вижу резона, почему это должно строиться чисто на PL SQL. Интерфейс приложения на чем делается? Или тоже возможностми самого Оракла? __>Если все лежит на сервере, то во-первых, клиенту ставить ничего не нужно (учитывая слабость клиентских машин-это существенный довод). И потом, если будет где-то ошибка, в базе данных ее легче поправить будет.
Понятно, но вообще это ничего не значит Та же проблема решается трехзвенкой — application server к которому уже коннектятся пользователи. Простейший пример — веб-приложения (appServer — iis+asp.net, клиент — браузер).
И к клиентам требования минимальны и изменения производятся только на одной машине.
F>>Если переписать все в декларативном виде без курсоров, F>>программа получится значительно проще. __>Не понял, что вы имеете в виду? По-моему так наоборо проще, селектов в коде нету
SQL — декларативный язык, и базы данных ориентированы именно на него. В том числе и по производительности.
Императивные конструкции в хранимках (расширения PlSql) — это костыли, как с точки зрения дизайна, так и эффективности.
Если хочешь программировать чисто в Оракле — изучай глубже SQL и максимально описывай все в запросах, а не пытайся сэмулировать С в Оракле. То есть хранимка остается, но в ней должны быть не итерации по курсорам, а набор запросов (или вообще 1 запрос). Императивные конструкции — в минимальнейшем количестве и наиболее простые (например, условным оператором проверив параметры, или результат запроса, выполнить далее тот или иной запрос). А лучше — вообще без них.
FD>Before SQL programmers could begin working with OLTP (On-Line Transaction
FD>Processing) systems, they had to unlearn procedural, record-oriented
FD>programming before moving on to SQLs declarative, set-oriented programming
Ценная мысль, однако
FD>Твой код можно сильно упростить, если переписать его чисто декларативно. FD>(если и не весь, то большую часть).
FD>Если интересуют подробности, сообщи следующую информацию: FD>- Структуру используемых таблиц FD>- Точное описание того, что нужно сделать.
Для начала приведу упрощенную схему (~6 кб):
Опишу словами. Пользователи объедены в группы, группы выстроены в иерархию. Изменения в верхней точке иерархии спускаются вниз. Если временные интервалы расписания пересекаются, то верхний интервал разбивает нижний. Поясню: пусть нижний интервал начинается в 9:00 и заканчивается в 12:00, то после наложения "верхнего" интервала 10:00-11:00 должно получиться так: 9:00-10:00; 10:00-11:00; 11:00-12:00.
Расписание я организовал так: есть временной шаблон, который повторяется с периодичностью в "Длительность шаблона". Шаблон начинаем применять с "Действительно от". У временного интервала указано смещение во времени относительно начала действия шаблона, указана длительность. В итоге получился гибкий шаблон, под который можно засунуть луюбой график, имеющий период
Итак задача такая: сгенерировать последовательность интервалов с привязкой к конкретной дате, а также учесть иерархию, и приоритет расписания. Если вы очитали до сюда, у Вас крепкие нервы
На мой взгляд, накладывать расписание вниз по иерархии чисто декларативно невозможно.
Здравствуйте, fmiracle, Вы писали:
F>Понятно, но вообще это ничего не значит Та же проблема решается трехзвенкой — application server к которому уже коннектятся пользователи. Простейший пример — веб-приложения (appServer — iis+asp.net, клиент — браузер). F>И к клиентам требования минимальны и изменения производятся только на одной машине.
У нас для трехзвенки Java используется. Но я согласен с Flying Dutchman, в том, что можно сделать проще и на SQL
F>SQL — декларативный язык, и базы данных ориентированы именно на него. В том числе и по производительности. F>Императивные конструкции в хранимках (расширения PlSql) — это костыли, как с точки зрения дизайна, так и эффективности.
F>Если хочешь программировать чисто в Оракле — изучай глубже SQL и максимально описывай все в запросах, а не пытайся сэмулировать С в Оракле. То есть хранимка остается, но в ней должны быть не итерации по курсорам, а набор запросов (или вообще 1 запрос). Императивные конструкции — в минимальнейшем количестве и наиболее простые (например, условным оператором проверив параметры, или результат запроса, выполнить далее тот или иной запрос). А лучше — вообще без них.
F>Сперва непривычно, но оно того стоит.
Интересный и правильный ход мыслей. Я запомню
__>Опишу словами. Пользователи объедены в группы, группы выстроены в иерархию. Изменения в верхней точке иерархии спускаются вниз. Если временные интервалы расписания пересекаются, то верхний интервал разбивает нижний. Поясню: пусть нижний интервал начинается в 9:00 и заканчивается в 12:00, то после наложения "верхнего" интервала 10:00-11:00 должно получиться так: 9:00-10:00; 10:00-11:00; 11:00-12:00.
Я правильно понял, что:
1. "Верхний" интервал — это интервал с большим приоритетом или
2. Стоящий выше в иерархии ?
__>Расписание я организовал так: есть временной шаблон, который повторяется с периодичностью в "Длительность шаблона".
В каких единицах выражается "Длительность шаблона" ?
__>Шаблон начинаем применять с "Действительно от". У временного интервала указано смещение во времени относительно начала действия шаблона, указана длительность.
"Шаблон" — это набор интервалов ?
__>В итоге получился гибкий шаблон, под который можно засунуть луюбой график, имеющий период
Я не совсем в этом разобрался. Не мог бы ты прислать немного данных из таблиц
для примера ? Желательно скрипт в виде набора команд CREATE TABLE для создания
таблиц + INSERT для их заполнения.
__>На мой взгляд, накладывать расписание вниз по иерархии чисто декларативно невозможно.
Здравствуйте, Flying Dutchman, Вы писали:
FD>Я правильно понял, что: FD>1. "Верхний" интервал — это интервал с большим приоритетом или FD>2. Стоящий выше в иерархии ?
Оба раза вы правы Пусть есть иерархическая группа: Университет -> Факультет -> Кафедра -> Группа. Тогда, если у Университета есть расписание, то интервалы университета будут более приоритетным ко всем другим интервалам подчиненных узлов.
FD>В каких единицах выражается "Длительность шаблона" ?
INTERVAL DAY TO SECOND
FD>"Шаблон" — это набор интервалов ?
"Шаблон" — набор интервалов и указание их длительности.
FD>Я не совсем в этом разобрался. Не мог бы ты прислать немного данных из таблиц FD>для примера ? Желательно скрипт в виде набора команд CREATE TABLE для создания FD>таблиц + INSERT для их заполнения.
Понять действительно сразу не получится — я своему дипломному руководителю вживую полчаса рассказывал, а он программер со стажем...
Ну, держитесь, скрипт для создания схемы и данных:
-- Создание таблицы "Тип расписания"CREATE TABLE TimeTableType (
Descript VARCHAR2(128),
Priority NUMBER(1),
id NUMBER(1),
PRIMARY KEY (id)
)
-- Создать таблицу иерархических группCREATE TABLE HierarchyGroup (
id NUMBER,
Descript VARCHAR2(128),
s_parent NUMBER,
PRIMARY KEY (id),
FOREIGN KEY (s_parent) REFERENCES HierarchyGroup (id)
)
-- Создать таблицу расписаниеCREATE TABLE TimeTable (
id NUMBER,
ValidFrom TIMESTAMP,
ValidTo TIMESTAMP,
Duration INTERVAL DAY TO SECOND,
s_HierarchyGroup NUMBER,
s_TimeTableType NUMBER(1),
Descript VARCHAR2(128),
PRIMARY KEY(id),
FOREIGN KEY (s_HierarchyGroup) REFERENCES HierarchyGroup (Id)
)
-- Таблица временных интерваловCREATE TABLE TimeInterval (
id NUMBER,
TimeOffset INTERVAL DAY TO SECOND,
Duration INTERVAL DAY TO SECOND,
s_TimeTable NUMBER,
FOREIGN KEY (s_TimeTable) REFERENCES TimeTable (id),
PRIMARY KEY (id)
)
-- Вставляем данныеBEGIN
INSERT INTO TimeTableType (Id, Priority, Descript) VALUES (1, 1, 'Основное расписание');
INSERT INTO TimeTableType (Id, Priority, Descript) VALUES (2, 2, 'Расписание исключений');
INSERT INTO HierarchyGroup (Id, Descript, s_Parent) VALUES (1, 'Университет (самый верхний узел)', NULL);
INSERT INTO HierarchyGroup (Id, Descript, s_Parent) VALUES (2, 'Факультет АТС (родитель-Университет)', 1);
INSERT INTO HierarchyGroup (Id, Descript, s_Parent) VALUES (3, 'Факультет ДСМ (родитель-Университет)', 1);
INSERT INTO HierarchyGroup (Id, Descript, s_Parent) VALUES (4, 'Кафедра ВТ и АСУ (родитель-факультет АТС)', 2);
INSERT INTO HierarchyGroup (Id, Descript, s_Parent) VALUES (5, 'Кафедра АМС (родитель-факультет АТС)', 2);
INSERT INTO HierarchyGroup (Id, Descript, s_parent) VALUES (6, 'Группа АИ-5-010 (родитель-кафедра ВТ и АСУ)', 4);
INSERT INTO TimeTable (Id, Validfrom, Validto, Duration, s_Hierarchygroup, Descript, s_TimeTableType)
VALUES (1,
TO_TIMESTAMP('01.09.2008', 'DD.MM.YYYY'),
TO_TIMESTAMP('31.12.2008', 'DD.MM.YYYY'),
'+14 00:00:00.0',
6,
'Стандартное расписание',
1);
FOR l_ned IN 1..5 LOOP
INSERT INTO TimeInterval (id, TimeOffset, Duration, s_TimeTable)
VALUES ((l_ned-1) * 5 + 1, '+' || TO_CHAR(l_ned-1) || ' 8:20:00.0', '+00 1:30:00.0', 1);
INSERT INTO TimeInterval (id, TimeOffset, Duration, s_TimeTable)
VALUES ((l_ned-1) * 5 + 2, '+' || TO_CHAR(l_ned-1) || ' 10:05:00.0', '+00 1:30:00.0', 1);
INSERT INTO TimeInterval (id, TimeOffset, Duration, s_TimeTable)
VALUES ((l_ned-1) * 5 + 3, '+' || TO_CHAR(l_ned-1) || ' 12:05:00.0', '+00 1:30:00.0', 1);
INSERT INTO TimeInterval (id, TimeOffset, Duration, s_TimeTable)
VALUES ((l_ned-1) * 5 + 4, '+' || TO_CHAR(l_ned-1) || ' 13:50:00.0', '+00 1:30:00.0', 1);
END LOOP;
INSERT INTO TimeTable (Id, Validfrom, Validto, Duration, s_Hierarchygroup, Descript, s_TimeTableType)
VALUES (2,
TO_TIMESTAMP('03.09.2008', 'DD.MM.YYYY'),
TO_TIMESTAMP('03.09.2008', 'DD.MM.YYYY'),
'+1 00:00:00.0',
1,
'3-го отключили свет, универ закрыли',
2);
INSERT INTO TimeInterval (id, TimeOffset, Duration, s_TimeTable)
VALUES (25, '+0 0:0:0.0', '+1 0:0:0.0', 2);
COMMIT;
END;
--Посмотреть дерево иерархии:SELECT DECODE(level, 1, '', LPAD(' ', level * 5, ' ')) || h.descript AS descript
FROM HierarchyGroup h
START WITH s_parent IS NULL
CONNECT BY s_parent = PRIOR id
Был занят по работе, поэтому сразу не ответил.
__>Оба раза вы правы Пусть есть иерархическая группа: Университет -> Факультет -> Кафедра -> Группа. Тогда, если у Университета есть расписание, то интервалы университета будут более приоритетным ко всем другим интервалам подчиненных узлов.
Каким образом
Допустим, у нас есть расписание Университета:
9:00 — 11:00
14:00 — 16:00
и расписание кафедры
10:00 — 12:00
13:00 — 13:30
15:00 — 17:00
Каким будет результирующее расписание ?
Будут ли интервалы просто складываться или интервал Университета
отменяет интервал кафедры ?
Что касается интервалов для одной и той же иерархической группы,
но с разными приоритетами. Из приведенного ниже примера создается
впечатление, что интервал с приоритетом 2 отменяет все интервалы
с приоритетом 1. То есть для 03.09.2008 в результате не существует
ни одного интервала. Так ли это ?
__>[sql] __>-- Создание таблицы "Тип расписания" __>CREATE TABLE TimeTableType ( __> Descript VARCHAR2(128), __> Priority NUMBER(1), __> id NUMBER(1), __> PRIMARY KEY (id) __>)
__>-- Создать таблицу иерархических групп __>CREATE TABLE HierarchyGroup ( __> id NUMBER, __> Descript VARCHAR2(128), __> s_parent NUMBER, __> PRIMARY KEY (id), __> FOREIGN KEY (s_parent) REFERENCES HierarchyGroup (id) __>)
__>-- Создать таблицу расписание __>CREATE TABLE TimeTable ( __> id NUMBER, __> ValidFrom TIMESTAMP, __> ValidTo TIMESTAMP, __> Duration INTERVAL DAY TO SECOND, __> s_HierarchyGroup NUMBER, __> s_TimeTableType NUMBER(1), __> Descript VARCHAR2(128), __> PRIMARY KEY(id), __> FOREIGN KEY (s_HierarchyGroup) REFERENCES HierarchyGroup (Id) __>)
Насколько я понимаю,
ValidFrom — дата начала действия расписания
ValidTo — дата окончания действия расписания
Duration — период действия расписания
То есть в приведенном ниже примере создается расписание на 2 недели,
которое циклически повторяется, начиная с ValidFrom.
Вопрос: Это расписание всегда начинается с понедельника или может
начинаться с другого дня недели ?
__>-- Таблица временных интервалов __>CREATE TABLE TimeInterval ( __> id NUMBER, __> TimeOffset INTERVAL DAY TO SECOND, __> Duration INTERVAL DAY TO SECOND, __> s_TimeTable NUMBER, __> FOREIGN KEY (s_TimeTable) REFERENCES TimeTable (id), __> PRIMARY KEY (id) __>)
Могут ли интервалы из этой таблицы перекрываться ?
Как происходит генерация расписания для определенного узла
иерархии ? В моем представлении так (для приведенного примера):
1. Выбираем узел иерархии
2. Составляем основное расписание на основе расписаний этого
узла и всех родительских узлов
3. Составляем расписание-исключение на основе расписаний этого
узла и всех родительских узлов
4. Начинаем с 01.09.2008, используем интервалы из таблицы
TimeInterval. Для нахождения начала интервала прибавляем
TimeOffset к 01.09.2008 00:00. Для нахождения конца интервала
прибавляем Duration к началу интервала.
При этом применяем расписание-исключение к дате 03.09.2008,
в результате чего на эту дату нет интервалов.
5. Потом переходим к 14.09.2008 и повторяем пункт 3.
6. Повторяем до конца года.
Здравствуйте, Flying Dutchman, Вы писали:
FD>Каким образом FD>Допустим, у нас есть расписание Университета:
FD> 9:00 — 11:00 FD> 14:00 — 16:00
FD>и расписание кафедры
FD> 10:00 — 12:00 FD> 13:00 — 13:30 FD> 15:00 — 17:00
FD>Каким будет результирующее расписание ?
1 интервал: 9:00 — 11:00. Интервал принадлежит университету.
2 интервал: 11:00 — 12:99. Интервал принадлежит кафедре.
3 интервал: 14:00 — 16:00. Интервал принадлежит университету.
4 интервал 16:00 — 17:00. Интервал принадлежит кафедре.
FD>Будут ли интервалы просто складываться или интервал Университета FD>отменяет интервал кафедры ?
|==]---------[====| (результрующий интервал).
FD>Что касается интервалов для одной и той же иерархической группы, FD>но с разными приоритетами. Из приведенного ниже примера создается FD>впечатление, что интервал с приоритетом 2 отменяет все интервалы FD>с приоритетом 1. То есть для 03.09.2008 в результате не существует FD>ни одного интервала. Так ли это ?
Нет. Интервалы в одном расписании никак между собой не пересекаются. Приоритет интервала прямо зависит от его связи с иерархической группой. Чем "выше" группа, тем приоритетней ее интервал.
FD>То есть в приведенном ниже примере создается расписание на 2 недели, FD>которое циклически повторяется, начиная с ValidFrom.
Все верно
FD>Вопрос: Это расписание всегда начинается с понедельника или может FD>начинаться с другого дня недели ?
Расписание начинает быть действительным точно во время ValidFrom.
FD>Могут ли интервалы из этой таблицы перекрываться ?
Нет, в пределах расписания интервалы не пересекаются.
FD>Как происходит генерация расписания для определенного узла FD>иерархии ? В моем представлении так (для приведенного примера):
Алгоритм построения последовательности у меня такой:
1) Начинаем с иерархической группы. Для этой группы строим иерархию "вверх" — то есть, выбираем все вышестоящие группы.
2) Сверху вниз, начинаем генерировать интервалы. Если запись на период действия интервала уже есть, мы ее либо разбиваем, либо удаляем (если наша запись полностью перекрывает уже существующую).
Здравствуйте, __kain, Вы писали:
FD>>Что касается интервалов для одной и той же иерархической группы, FD>>но с разными приоритетами. Из приведенного ниже примера создается FD>>впечатление, что интервал с приоритетом 2 отменяет все интервалы FD>>с приоритетом 1. То есть для 03.09.2008 в результате не существует FD>>ни одного интервала. Так ли это ?
__>Нет. Интервалы в одном расписании никак между собой не пересекаются. Приоритет интервала прямо зависит от его связи с иерархической группой. Чем "выше" группа, тем приоритетней ее интервал.
Непонятно, как происходит наложение расписания исключений на основное расписание.
Еще раз, в приведенном примере расписание исключений содержит интервал для всего дня
на 03.09.2008, то есть по логике вещей все интервалы из основного
расписания, попадающие в это промежуток, отменяются (то есть все
занятия отменяются, потому что в Университете в этот день отключили свет).
Так ли это ?
FD>>Вопрос: Это расписание всегда начинается с понедельника или может FD>>начинаться с другого дня недели ?
__>Расписание начинает быть действительным точно во время ValidFrom.
Если расписание повторяется циклически
(то есть если Duration < (ValidTo — ValidFrom)),
то значение в колонке Duration таблицы TimeTable должно
быть равно 1 дню или быть кратно 7 дням. Так ли это ?
В противном случае его невозможно будет повторить.
Допустим, например, что Duration = 3. Начиная применять его с понедельника,
получим, что расписания повторяются таким образом:
Пн Вт Ср
Чт Пт Сб
Вс Пн Вт
Что, наверное, является неправильным. Или правильным ?
Не говоря уже о таком Duration, как 2.5 дня и т.д.
__>P.S. __>Сложновато получается, а? =)
Здравствуйте, Flying Dutchman, Вы писали:
FD>Здравствуйте, __kain, Вы писали:
FD>>>Что касается интервалов для одной и той же иерархической группы, FD>>>но с разными приоритетами. Из приведенного ниже примера создается FD>>>впечатление, что интервал с приоритетом 2 отменяет все интервалы FD>>>с приоритетом 1. То есть для 03.09.2008 в результате не существует FD>>>ни одного интервала. Так ли это ?
Интервалы дробятся. Представьте, сидит сторож 12 часов, и на последней минуте его дежурства отключают свет. Мы же не будем отменять весь интервал, только уменьшим для этой даты его продолжительность
FD>Если расписание повторяется циклически FD>(то есть если Duration < (ValidTo — ValidFrom)), FD>то значение в колонке Duration таблицы TimeTable должно FD>быть равно 1 дню или быть кратно 7 дням. Так ли это ?
Расписание начинается с ValidFrom, циклически повторяется с продолжительностью в Duration, и генерация интервалов заканчивается по достижение ValidTo.
Расписание исключений генерирует интервалы точно также. Просто сначала мы проходим снизу вверх с учетом основного расписания, потом мы опять проходим сверху вниз, но с учетом только расписаний исключения.
Я в принципе код набросал, осталось потестить. В декларативном виде без "своих" групповых функций здесь точно не получится...
Расписание исключений генерирует интервалы точно также. Просто сначала мы проходим снизу вверх с учетом основного расписания, потом мы опять проходим снизу вверх, но с учетом только расписаний исключения.
Реализовано для Oracle 10g и выше. Я реализовал только обработку
основных расписаний, но обработка расписаний исключений также может быть
добавлена.
Я произвел следующие изменения в структуре БД (в основном привел к
соответствию к моему собственному стилю — мне так удобнее):
1. Имена таблиц и колонок созданы с использованием '_' в качестве
разделителя слов.
2. Имена колонок — первичных ключей изменены с ID на
<Имя таблицы>_ID. Соответственно изменены имена колонок, используемых
во внешних ключах. Это дает возможность использовать
синтаксис Inner join ... using (...) в запросах.
3. Тип колонок ValidFrom и ValidTo изменен на более логичный тип DATE
4. Тестовые данные — только на английском (мой сервер что-то русские
буквы не воспринимает, а разбираться нет времени).
create_db.sql Создание БД
load_test_data.sql Загрузка тестовых данных
create_views.sql Создание представлений
Начинаем с представления V_Hierarchy
-- Get the list of all nodes with all parent nodescreate or replace view V_Hierarchy
as
with hierarchy as (
select Hierarchy_Group_Id,
sys_connect_by_path(Hierarchy_Group_Id, '/') || '/' as full_path,
level as Priority
from Hierarchy_Group
start with Parent_Group_Id is null
connect by Parent_Group_Id = prior Hierarchy_Group_id
)
select hier1.Hierarchy_Group_Id as Hierarchy_Group_Id,
hier2.Hierarchy_Group_id as Participating_Hier_Group_Id,
hier1.full_path,
hier2.Priority
from hierarchy hier1 cross join hierarchy hier2
where instr(hier1.full_path, '/' || to_char(hier2.Hierarchy_Group_id) || '/') > 0;
Назначение колонок результата:
Hierarchy_Group_Id — идентификатор узла иерархии.
Participating_Hier_Group_Id — идентификатор узла иерархии, начиная от
текущего и до самого верхнего родителя.
Priority — приоритет узла Participating_Hier_Group_Id (1 — самый
высокий).
Следующее представление генерирует все интервалы для всех расписаний,
разворачивая все цикличиские расписания.
create or replace view V_Unrolled_Interval
as
with numbers as (
select level num
from dual
connect by level <= 100),
all_interval as (
select Time_Table_Id,
Time_Table.Hierarchy_Group_Id,
Time_Table.Valid_From,
Time_Table.Valid_To,
Time_Table.Valid_From +
Time_Table.Duration * (numbers.num - 1) +
Time_Interval.Time_Offset as Start_Date_Time,
Time_Table.Valid_From +
Time_Table.Duration * (numbers.num - 1) +
Time_Interval.Time_Offset +
Time_Interval.Duration as End_Date_Time,
Time_Interval.duration as Duration
from Time_Table cross join numbers
inner join Time_Interval using(Time_table_Id)
)
select Hierarchy_Group_Id,
Time_Table_Id,
Start_Date_Time,
End_Date_Time,
Duration
from all_interval
where trunc(Start_Date_Time) <= Valid_To;
Следующее представление добавляет к интервалам каждого узла
иерархии интервалы для родительских узлов.
create or replace view V_All_Unrolled_Interval
as
select V_Hierarchy.Hierarchy_Group_Id,
V_Hierarchy.Priority,
V_Unrolled_Interval.Time_Table_Id,
V_Unrolled_Interval.Start_Date_Time,
V_Unrolled_Interval.End_Date_Time,
V_Unrolled_Interval.Duration
from V_Hierarchy inner join V_Unrolled_Interval
on V_Hierarchy.Participating_Hier_Group_Id = V_Unrolled_Interval.Hierarchy_Group_Id;
Наконец, самое сложное — комбинирование интервалов с разными
приоритетами.
create or replace view v_combined_interval
as
with-- Select all DISTINCT start/end points of all intervals
all_point as (
select Hierarchy_Group_Id,
start_date_time as date_time
from V_All_Unrolled_Interval
union
select Hierarchy_Group_Id,
end_date_time as date_time
from V_All_Unrolled_Interval),
-- Generate sequence of all intervals between consecutive points
all_interval0 as (
select Hierarchy_Group_Id,
date_time as start_date_time,
lead(date_time) over (partition by Hierarchy_Group_Id order by date_time) as end_date_time
from all_point
),
-- Remove the last interval with end_date_time is null
all_interval as (
select Hierarchy_Group_Id,
start_date_time,
end_date_Time
from all_interval0
where end_Date_time is not null
),
-- Split all intervals
split_interval as (
select V_All_Unrolled_Interval.Hierarchy_Group_Id,
V_All_Unrolled_Interval.priority,
all_interval.start_date_time,
all_interval.end_date_time
from V_All_Unrolled_Interval cross join all_interval
where V_All_Unrolled_Interval.Hierarchy_Group_Id = All_Interval.Hierarchy_Group_Id
and All_Interval.start_date_Time >= V_All_Unrolled_Interval.start_date_Time
and All_Interval.start_date_Time < V_All_Unrolled_Interval.end_date_Time
)
-- Select only intervals with highest priority (= lowest priority value).select Hierarchy_Group_Id,
min(priority) as priority,
start_date_Time,
end_date_Time
from split_interval
group by Hierarchy_Group_Id, start_date_Time, end_date_time;
Немного пояснений.
Результат представления V_All_Unrolled_Interval
(для одного дня и для Hierarchy_Group_Id = 3)
Результат промежуточного запроса SPLIT_INTERVAL — все исходные интервалы,
разбитые на меньшие интервалы, определяемые точками из результата
запроса ALL_POINT:
Мне кажется, что существует болле простое и элегантное решение,
но я пока не смог его найти.
Как видишь, все сделано декларативно и без курсоров, ни одной строки
процедурного кода.
Описание краткое и несколько сумбурное. Если будут вопросы — пиши.
На этом уже можно было бы остановиться, но приведеное решение
имеет один недостаток — существующие интервалы с приоритетом 1
оказались разбитыми на несколько интервалов.
FD>Мне кажется, что существует болле простое и элегантное решение, FD>но я пока не смог его найти.
FD>Как видишь, все сделано декларативно и без курсоров, ни одной строки FD>процедурного кода.
FD>Описание краткое и несколько сумбурное. Если будут вопросы — пиши.
FD>На этом уже можно было бы остановиться, но приведеное решение FD>имеет один недостаток — существующие интервалы с приоритетом 1 FD>оказались разбитыми на несколько интервалов.
FD>Я исправлю это через некоторое время.
Что тут скажешь? Снимаю шляпу, маэстро! Буду разбираться, как у вас это работает Удивительно, как килограммы кода превратились в пару вью... Я немного в прострации сейчас
FD>Мне кажется, что существует болле простое и элегантное решение, FD>но я пока не смог его найти.
Реализована только обработка основных расписаний, без
расписаний-исключений.
Сделаны следующие изменения по сравнению с первоначальным решением:
— Новое представление V_Max_Repetition_Number.
— Изменено представление V_Unrolled_Interval.
— Добавлены констрейнты для таблицы Time_Table.
— Добавлены комментарии.
— Изменена структура тестовых данных.
— Добавлены для учебных целей: тестовое представление v_test_interval,
Файл simplified_combined_interval_query.sql, Файл simplified_interval_query.sql
— Добавлено представление V_Interval
convert_test_data_to_1day.sql
convert_test_data_to_2day.sql
create_db.sql — Создание БД
create_views.sql — Создание представлений
load_test_data_1day.sql — Загрузка тестовых данных
simplified_combined_interval_query.sql — Упрощенный вариант запроса
для представления V_Combined_Interval
simplified_interval_query.sql — Упрощенный вариант запроса
для представления V_Interval
v_test_interval.sql Тестовые данные для запроса из файла
simplified_interval_query.sql
Начинаем с представления V_Hierarchy
-- Get the list of all nodes with all parent nodescreate or replace view V_Hierarchy
as
with hierarchy as (
select Hierarchy_Group_Id,
sys_connect_by_path(Hierarchy_Group_Id, '/') || '/' as full_path,
level as Priority
from Hierarchy_Group
start with Parent_Group_Id is null
connect by Parent_Group_Id = prior Hierarchy_Group_id
)
select hier1.Hierarchy_Group_Id as Hierarchy_Group_Id,
hier2.Hierarchy_Group_id as Participating_Hier_Group_Id,
hier1.full_path,
hier2.Priority
from hierarchy hier1 cross join hierarchy hier2
where instr(hier1.full_path, '/' || to_char(hier2.Hierarchy_Group_id) || '/') > 0;
Назначение колонок результата:
Hierarchy_Group_Id — идентификатор узла иерархии.
Participating_Hier_Group_Id — идентификатор узла иерархии, начиная от
текущего и до самого верхнего родителя.
Priority — приоритет узла Participating_Hier_Group_Id (1 — самый
высокий).
Следующее представление используется для получения максимального числа
повторений для повторяющихся расписаний:
--------------------------------------------------------------------------
-- Get the maximal repetition number for the cyclic timetables.
-- The value is not exact< but it's guaranteed to be no less that
-- the real value.
--------------------------------------------------------------------------create or replace view V_Max_Repetition_Number
as
with period_sec as (
select (trunc(Valid_To) - trunc(Valid_From) + 1) * 60 * 60 * 24 as Valid_Period_Sec,
(to_number(extract(second from Duration)) +
to_number(extract(minute from Duration)) * 60 +
to_number(extract(hour from Duration)) * 60 * 60 +
to_number(extract(day from Duration)) * 60 * 60* 24) as Duration_Sec
from Time_Table
)
select max(Valid_Period_Sec / Duration_Sec + 1) as Max_Repetition_Number
from period_Sec;
Следующее представление генерирует все интервалы для всех расписаний,
разворачивая все циклические расписания.
create or replace view V_Unrolled_Interval
as
with numbers as (
select level num
from dual
connect by level <= (select Max_Repetition_Number from V_Max_Repetition_Number)),
all_interval as (
select Time_Table_Id,
Time_Table.Hierarchy_Group_Id,
Time_Table.Valid_From,
Time_Table.Valid_To,
Time_Table.Valid_From +
Time_Table.Duration * (numbers.num - 1) +
Time_Interval.Time_Offset as Start_Date_Time,
Time_Table.Valid_From +
Time_Table.Duration * (numbers.num - 1) +
Time_Interval.Time_Offset +
Time_Interval.Duration as End_Date_Time,
Time_Interval.duration as Duration
from Time_Table cross join numbers
inner join Time_Interval using(Time_table_Id)
)
select Hierarchy_Group_Id,
Time_Table_Id,
Start_Date_Time,
End_Date_Time,
Duration
from all_interval
where trunc(Start_Date_Time) <= Valid_To;
Следующее представление добавляет к интервалам каждого узла
иерархии интервалы для родительских узлов.
create or replace view V_All_Unrolled_Interval
as
select V_Hierarchy.Hierarchy_Group_Id,
V_Hierarchy.Priority,
V_Unrolled_Interval.Time_Table_Id,
V_Unrolled_Interval.Start_Date_Time,
V_Unrolled_Interval.End_Date_Time,
V_Unrolled_Interval.Duration
from V_Hierarchy inner join V_Unrolled_Interval
on V_Hierarchy.Participating_Hier_Group_Id = V_Unrolled_Interval.Hierarchy_Group_Id;
Наконец, самое сложное — комбинирование интервалов с разными
приоритетами.
create or replace view v_combined_interval
as
with-- Select all DISTINCT start/end points of all intervals
all_point as (
select Hierarchy_Group_Id,
start_date_time as date_time
from V_All_Unrolled_Interval
union
select Hierarchy_Group_Id,
end_date_time as date_time
from V_All_Unrolled_Interval),
-- Generate sequence of all intervals between consecutive points
all_interval0 as (
select Hierarchy_Group_Id,
date_time as start_date_time,
lead(date_time) over (partition by Hierarchy_Group_Id order by date_time) as end_date_time
from all_point
),
-- Remove the last interval with end_date_time is null
all_interval as (
select Hierarchy_Group_Id,
start_date_time,
end_date_Time
from all_interval0
where end_Date_time is not null
),
-- Split all intervals
split_interval as (
select V_All_Unrolled_Interval.Hierarchy_Group_Id,
V_All_Unrolled_Interval.priority,
all_interval.start_date_time,
all_interval.end_date_time
from V_All_Unrolled_Interval cross join all_interval
where V_All_Unrolled_Interval.Hierarchy_Group_Id = All_Interval.Hierarchy_Group_Id
and All_Interval.start_date_Time >= V_All_Unrolled_Interval.start_date_Time
and All_Interval.start_date_Time < V_All_Unrolled_Interval.end_date_Time
)
-- Select only intervals with highest priority (= lowest priority value).select Hierarchy_Group_Id,
min(priority) as priority,
start_date_Time,
end_date_Time
from split_interval
group by Hierarchy_Group_Id, start_date_Time, end_date_time;
Немного пояснений.
Результат представления V_All_Unrolled_Interval
(для одного дня и для Hierarchy_Group_Id = 3)
Результат промежуточного запроса Split_Interval — все исходные интервалы,
разбитые на меньшие интервалы, определяемые точками из результата
запроса ALL_POINT:
Наконец, интервалы, примыкающие друг к другу, "cклеиваются" при помощи
представления V_Interval:
create or replace view V_Interval
as
with time_int2 as (
select Hierarchy_Group_Id,
Priority,
Start_Date_Time,
End_Date_Time,
case when lag(End_Date_Time) Over(Partition by Hierarchy_Group_Id, Priority order by Start_Date_Time) = Start_Date_Time then 0
else 1
end as flag
from V_Combined_Interval
),
time_int3 as (
select Hierarchy_Group_Id,
Priority,
Start_Date_Time,
End_Date_Time,
sum(flag) over(Partition by Hierarchy_Group_Id, Priority order by Start_Date_Time) as Interval_Group
from time_int2
)
select Hierarchy_Group_Id,
Priority,
interval_group,
min(Start_Date_Time) as Start_Date_Time,
max(End_Date_Time) as End_Date_Time
from time_Int3
group by Hierarchy_Group_Id, Priority, Interval_Group;
