У нас есть таблица событий. Мы хотим выбирать события, фильтруя их по времени. Управлять фильтром хотим с помощью строки даты или диапазона дат.
Постановка задачи
Есть таблица или представление - в нем, конечно же, есть смысловая информация, но нам для рассуждений достаточно только первичного ключа и отметки времени
...events_vi (id number primary key, date_created date, ...):
select id, date_created
from events_vi
;
8123 18.03.2005 14:34:17
8124 18.03.2005 15:25:31
8125 18.03.2005 16:28:28
8126 21.03.2005 10:42:33
...
Мы бы хотели управлять выборкой из этого представления при помощи строки, например,
18.03.2005
и получать результат запроса типа такого:
select id, date_created
from events_vi
where trunc(date_created) = to_date('18.03.2005', 'dd.mm.yyyy')
;
8123 18.03.2005 14:34:17
8124 18.03.2005 15:25:31
8125 18.03.2005 16:28:28
Но, во-первых, даже в случае индексированного столбца date_created запрос с функцией от индексированного столбца будет неэффективным и приведет к полному сканированию источника, а во-вторых, управляющая строка может иметь и другой формат, например,
18-21.03.2005
и мы бы хотели, чтоб наш фильтр это понимал. Кроме того, мы бы хотели получить компактное решение, которое могли бы дописывать к неким достаточно сложным смысловым запросам. Т.е. мы бы хотели получить что-то типа представления, которое могли бы join'ить в смысловой запрос, и которое бы понимало некоторый набор шаблонов фильтрующих по времени строк.
Подход к решению
Вероятно, решить такую задачу в общей постановке сложно или вообще невозможно, поскольку строку
18-19
можно трактовать и как 2018-2019, и как 18-19 числа текущего месяца текущего года. Поэтому вначале нам необходимо создать библиотеку шаблонов, которые наш фильтр будет принимать. Например, такую:
2019 -- год
2018-2019 -- диапазон лет
01.2019 -- месяц
01-02.2019 -- диапазон месяцев
01.01.2019 -- дата
01-05.02.2019 -- диапазон дат
15.01-02.2019 -- дата на диапазоне месяцев
10-15.01-02.2019 -- диапазон дат на диапазоне месяцев
Тогда нам потребуется реализовать три действия:
- Определение шаблона по фактической фильтрующей строке;
- Выбор соответствующего шаблону - желательно оптимального - обработчика;
- Формирование фильтрованного множества - желательно в таблице-подобном виде.
Информацию для шагов (1) и (2) мы можем организовать в виде таблицы базы данных из двух (в минимальном случае) столбцов:
- Шаблон;
- Соответствующий шаблону обработчик,
а шаг (3) - в виде конвейерной функции, возвращающей табличный тип. Тогда мы сможем обратиться к нашему фильтру при помощи, например, такого SQL выражения:
select *
from table(util_pkg.filter_event_date(:str))
;
где filter_event_date() - наша фильтрующая конвейерная функция, util_pkg - ее содержащий PL/SQL пакет, :str - фильтрующая строка-аргумент.
Определение шаблона по фильтрующей строке
Мы можем описать наши шаблоны при помощи механизма регулярных выражений:
create table time_filter_tab(example..., pattern..., ...);
create view time_filter_vi as select * from time_filter_tab;
2019 ^[0-9]{4}$
2018-2019 ^[0-9]{4}\-[0-9]{4}$
01.2019 ^[0-9]{2}\.[0-9]{4}$
01-02.2019 ^[0-9]{2}\-[0-9]{2}\.[0-9]{4}$
01.01.2019 ^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$
01-05.02.2019 ^[0-9]{2}\-[0-9]{2}\.[0-9]{2}\.[0-9]{4}$
15.01-02.2019 ^[0-9]{2}\.[0-9]{2}\-[0-9]{2}\.[0-9]{4}$
10-15.01-02.2019 ^[0-9]{2}\-[0-9]{2}\.[0-9]{2}\-[0-9]{2}\.[0-9]{4}$
Тогда, сравнив полученную фильтрующую строку-аргумент с хранимыми в библиотеке регулярными выражениями, мы можем определить необходимый шаблон и, как следствие, необходимый обработчик. Для этого мы можем применить "перевернутую" функцию regexp_like().
Почему функция перевернутая? Обычно она применяется для поиска в таблице значения(ий) (первый аргумент), соответствующего(их) шаблону, заданному в виде регулярного выражения (второй аргумент), т.е. для поиска в базе по регулярному выражению, являющемуся параметром. Например так:
select example
from time_filter_vi
where regexp_like(example, '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$')
;
Мы же будем действовать наоборот: параметром будет фактическая строка, а искать в таблице мы будем соответствующее ей регулярное выражение:
select ... from time_filter_vi where regexp_like(:str, pattern)
;
Т.е. мы поменяли местами параметр и проверяемый столбец в базе - и функция исправно работает:
select example
from time_filter_vi
where regexp_like('18.03.2005', pattern)
;
01.01.2019
Т.е. по полученной строке мы, проверив библиотеку шаблонов в форме регулярных выражений, определили правильный шаблон. И обработчик для него.
Соответствующий шаблону обработчик
Обработчик - это SQL выражение, фильтрующее события в соответствии с заданной фильтрующей строкой, по-возможности оптимальное относительно политики индексации источника и соответствующего заданной строке хранимого шаблона. Кроме того, это SQL выражение должно быть записано таким образом, чтобы единообразно вызываться из фильтрующей функции. И конечно, хранить это SQL выражение следует в виде столбца таблицы шаблонов, рассмотренной в предыдущем разделе.
Например, для фильтрующей строки '18.03.2005' обработчик может выглядеть следующим образом:
with t as (select :str as a from dual)
select * from events_vi
where date_created
between to_date((select a from t), 'dd.mm.yyyy')
and to_date('235959'||(select a from t), 'hh24missdd.mm.yyyy')
;
Может возникнуть вопрос "почему так сложно?"
- Конструкция between является эффективной для поиска по индексированному полю в отличие от, скажем, функции trunc() от него - поэтому мы выбираем именно between;
- Конструкция between требует двух параметров - "от" и "до", оба их мы будем вычислять по полученной фильтрующей строке :str, но, поскольку мы будем вызывать обработчик из функции для исполнения в виде динамического SQL, то мы хотели бы передать параметр единообразно для всех обработчиков - а он может понадобиться и один раз, и два, и три - хорошо бы передавать его всегда однократно, а уже внутри обработчика снимать с него нужное количество копий; что мы и делаем при помощи подзапроса
with t as (select :a as a from dual); - Конструкция between является включающей, поэтому мы явно записываем "по последнюю секунду дня включительно", чтоб не захватить нулевую секунду следующего дня.
Для строки, например, такого вида '10-15.01-02.2019' обработчик может выглядеть так:
with t as (
select
regexp_replace(a, '\-[0-9]{2}\.', '.') as b
, regexp_replace(a, '[0-9]{2}\-', '') as c
from (select :str as a from dual)
)
select * from events_vi
where date_created
between to_date((select b from t), 'dd.mm.yyyy')
and to_date('235959'||(select c from t), 'hh24missdd.mm.yyyy')
and to_number(to_char(date_created, 'dd'))
between to_number(substr((select b from t), 1, 2))
and to_number(substr((select c from t), 1, 2))
;
Здесь уже в подзапросе мы выделяем минимальную (b) и максимальную (c) границу предварительного диапазона, в который заходим по индексу, а уже в пределах этого - относительно небольшого - предварительного диапазона применяем не очень эффективный подход фильтрации по функции от столбца to_number(to_char()).
Т.е. к данному моменту мы имеем библиотеку шаблонов и соответствующих им обработчиков, которые можем вызывать из конвейерной фильтрующей функции, которая будет выполнять роль динамического фильтрующего представления для включения в смысловые запросы.
Фильтрующая функция
Прежде всего, нам следует создать табличный тип данных, которые будет возвращать наша функция:
create or replace type eventType as object (id number, date_created date);
create or replace type eventTypeSet as table of eventType;
Теперь мы можем записать саму функцию (детали для краткости опускаем):
function filter_event_date (p_str varchar2)
return eventTypeSet
pipelined
as
...
begin
select sql_str into v_sql from time_filter_vi where regexp_like(p_str, pattern);
...
loop
...
pipe row(out_rec);
end loop;
...
return;
end filter_event_date;
Теперь мы можем подключать наш фильтр к смысловым запросам, рассматривая фильтрующую функцию как динамическое представление:
select *
from table(util_pkg.filter_event_date(:str))
;
Мы решили задачу.
