Аналитические функции и иерархические запросы работают с несколькими строками таблицы Oracle. Конструкция SELECT MODEL дает еще большие возможности.
Предположим, в таблицу базы данных записываются документы, которые поступают пачками, при этом каждый документ в пределах пачки нумерован, но номер пачки по каким-то причинам не регистрируется. Т.е. мы имеем лишь монотонный непрерывный идентификатор строки и номер документа в пачке. Имитируем такие данные. Три вложенных иерархических запроса генерируют номера документов в трех пачках, охватывающий запрос генерирует идентификаторы строк:
select rownum as id, doc_no from
(
-- Первая пачка:
select level as doc_no from dual connect by level <= 3
union all
-- Вторая пачка:
select level as doc_no from dual connect by level <= 5
union all
-- Третья пачка:
select level as doc_no from dual connect by level <= 2
)
;
-- Первая пачка:
1 1
2 2
3 3
-- Вторая пачка:
4 1
5 2
6 3
7 4
8 5
-- Третья пачка:
9 1
10 2
И вот теперь при наличии таких данных нам нужно пронумеровать пачки, восстановить забытую или не предусмотренную нумерацию.
Это можно выполнить с применением поразительной конструкции, лишь недавно попавшейся мне на глаза:
Chris Saxon. Assinging Batch Numbers to Query Rows
-- Имитатор данных:
with docs as (
select rownum as id, doc_no from (
select level as doc_no from dual connect by level <= 3 union all
select level as doc_no from dual connect by level <= 5 union all
select level as doc_no from dual connect by level <= 2 )
)
-- Смысловой запрос:
select * from docs
model
dimension by (id)
measures (doc_no, 0 as batch_no)
rules (
batch_no[any] = case
when doc_no[cv()] <= doc_no[cv()-1] then batch_no[cv()-1] + 1
else nvl(batch_no[cv()-1], 1)
end
)
;
-- Пачка № 1:
1 1 1
2 2 1
3 3 1
-- Пачка № 2:
4 1 2
5 2 2
6 3 2
7 4 2
8 5 2
-- Пачка № 3:
9 1 3
10 2 3
Предложение dimension by () описывает "индекс массива", в данном случае вполне пригодный для этого монотонный непрерывный идентификатор id. Если такого нет, то можно было бы использовать нумератор по какому-нибудь сортируемому полю
row_number() over (order by ...).
Предложение measures () перечисляет следующие за "индексом массива" извлекаемые поля. В данном случае это номер документа doc_no и вычисляемый номер пачки batch_no, который мы сначала определяем как ноль.
Предложение rules () описывает правила для этих полей, по сути являясь программным фрагментом, включенным в SQL запрос и выполняемым в пространстве SQL, а не в пространстве процедурного языка.
В данном случае этот фрагмент говорит следующее: для каждой выбираемой строки проверять, не сбросился ли счетчик номера документа (пошла новая пачка), и если такое событие произошло, то увеличивать номер пачки для текущей строки на единицу по сравнению с предыдущей строкой; в противном случае брать для текущей строки тот же номер пачки, что и для предыдущей; обработать на пустое значение самую первую строку, присвоив значение 1. При этом конструкция cv() обозначает текущий "индекс массива", т.е. номер строки.
В данном примере рассмотрена простая закономерность - начинающийся с единицы (т.е. сбрасываемый) счетчик номеров документов в пачке. На практике это может быть существенно более сложная, но описываемая закономерность.
Описанный метод может быть полезен при анализе данных, при записи которых произошла частичная потеря первичной информации, но сохранились закономерности. Затем Oracle APEX позволяет быстро визуализировать результаты анализа.