Любой разработчик, имеющий дело с генерацией отчётности из баз данных, регулярно сталкивается с построением громоздких запросов. Часто это бывает связано с ошибками проектирования БД, и, ещё чаще, со сложностями преобразования извлекаемых данных. К последним можно отнести применение итерационных методов вычисления, подсчёт промежуточных итогов по подгруппам, расчёты, в которых используются значения соседних строк выборки, сложное форматирование строк и подобные задачи. Такие преобразования часто выносятся с уровня БД на уровень сервера приложений или клиента, что сказывается на производительности и удобстве сопровождения кода. Для решения этих задач SQL СУБД Oracle предоставляет аналитические функции и оператор MODEL — о нём и пойдёт речь в этой статье.Это расширение конструкции SELECT стало доступно с СУБД версии 10g. MODEL позволяет обращаться к данным выборки как к многомерным массивам, изменять и добавлять элементы, проводить сложную агрегацию, а также решать ряд задач, которые до этого требовали использования PL/SQL. При этом языковые конструкции остаются читабельными и декларативными. Одним словом — Excel-like, плюс вся нагрузка ложится на плечи сервера БД.
Синтаксис
MODEL [IGNORE NAV] [RETURN UPDATED ROWS]
[PARTITION BY (partition_column_1, ...)]
DIMENSION BY (dimension_column_1, ...)
MEASURES (measured_column_1, ...)
RULES [AUTOMATIC ORDER | ITERATE (value) [UNTIL (expression)]] (
rule_1, ...
);Оператор MODEL обрабатывается в числе последних, после него только DISTINCT и ORDER BY. В результате применения столбцы выборки отображаются в массивы measured_column_* c измерениями dimension_column_*. Необязательный параметр PARTITION BY определяет паритиции, аналогичные используемым в аналитических функциях (каждая из них обрабатывается правилами rule_* как независимый массив). Правила применяются в порядке перечисления.
Простейшие примеры
Для начала смоделируем выборку чисел 1, 2 и 3:
SELECT *
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (dummy)
RULES (
dummy[5] = 1,
dummy[6] = 2,
dummy[7] = 3
);
DIMENSION R
---------- -
0 X
7 3
6 2
5 1В данном случае, по трём правилам заполняется массив dummy с измерением dimension. Алиас 0 dimension определяется для того, чтобы добавить новый столбец. Разберём преобразование подробнее. Первым делом происходит определение и отображение столбцов выборки (0 dimension в DIMENSION, dummy в MEASURES), затем по этим столбцам происходит выборка (возвращается строка dummy = X, dimension = 0) и только после этого выполняются правила. Сначала ищется строка с dimension = 5, но т.к. она не находится, создаётся новая и заполняется dummy = 1, аналогично для оставшихся двух правил. Если необходимо, с помощью директивы RETURN UPDATED ROWS можно вывести только обновлённые строки:
SELECT result, dummy
FROM dual
MODEL RETURN UPDATED ROWS
DIMENSION BY (dummy)
MEASURES (0 result)
RULES(
result[5] = 1,
result[6] = 2,
result[7] = 3
);
RESULT DUMMY
---------- ------
3 7
2 6
1 5Применять наборы правил можно и в циклах. Следующий запрос рассчитывает несколько элементов последовательности Фибоначчи:
SELECT sequence
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (0 sequence)
RULES ITERATE (100500) UNTIL (sequence[iteration_number] > 10) (
sequence[iteration_number] =
CASE iteration_number
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE sequence[iteration_number - 2] + sequence[iteration_number - 1]
END
);
SEQUENCE
----------
0
1
1
2
3
5
8
13ITERATE задаёт количество итераций цикла (начиная с 0), а необязательная директива UNTIL — условие выхода из него (которое сработало, судя по тому что вы ещё не нажали Ctrl + End). Доступ к счётчику осуществляется через переменную iteration_number.
Диапазоны и агрегирование
Выше мы видели примеры с расчётом значений отдельных элементов. Кроме этого, правила можно задавать для их групп, отфильтрованных по заданным условиям. Для этого используются второй тип ссылок — символические (ранее были только позиционные). Индексы в символических ссылках могут содержать проверку условий истинности, например:- cnt[day < 6, type LIKE 'latt%']
- cnt[day IN (3, 6), cv(type)]
- cnt[day BETWEEN 1 AND 16, regexp_like(type, '^.+(sso|tte)$')]
- cnt[2, 'black']
- cnt[7, 'latte']
Рассмотрим таблицу, хранящую информацию о выпитом в течение недели кофе:
SELECT * FROM coffee;
TYPE CNT DAY
-------------------- ---------- ----------
turkish 1 1
espresso 1 1
turkish 2 2
black 1 2
espresso 1 2
latte 3 3
black 2 4
ice 1 4Предположим, мы хотим вывести отчёт о том сколько всего чашек кофе выпито в каждый из дней и в общем, заодно учтём, что в четверг порции чёрного кофе были двойными. Итак, запрос:
SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[4, 'black'] = cnt[cv(day), 'black'] * 2,
cnt[FOR day FROM 1 TO 4 INCREMENT 1, ' total for day'] = sum<(cnt)[cv(day), ANY],
cnt[NULL, 'GRAND TOTAL'] = sum(cnt)[ANY, ' total for day']
)
ORDER BY day, type DESC;
DAY TYPE CNT
--------- -------------------- ---------
1 turkish 1
1 espresso 1
1 total for day 2
2 turkish 2
2 espresso 1
2 black 1
2 total for day 4
3 latte 3
3 total for day 3
4 ice 1
4 black 4
4 total for day 5
GRAND TOTAL 14Разберём правила подробнее. Первое удваивает количество выпитого в четверг кофе. Функция cv(dimension_name) возвращает текущее значение индекса по измерению dimension_name для обрабатываемого элемента (т.е., в данном случае, вместо cv(day) можно было указать day = 4 или, при желании, сослаться на предыдущий день как day = cv(day) — 1). Второе правило вычисляет подытоги с понедельника по четверг. Кроме цикла (который и так нагляден), здесь используются ссылки на диапазоны элементов в правых частях равенств. Указывать диапазоны можно теми же способами, что и проверки в конструкции WHERE, дополнительное ключевое слово ANY служит для выбора любых значений индекса. Ссылки на диапазоны в правой части равенства необходимо агрегировать, в данном случае используется функция sum. И, наконец, третье правило считает сумму подытогов.
Движемся дальше. Рассмотрим запрос, выводящий подытоги выпитого по видам кофе, количество латте, выпитого во вторник, а также сообщение о том, достигнута ли цель недели — выпить эспрессо в среду:
SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[NULL, FOR type IN (SELECT DISTINCT type FROM coffee)] = sum(cnt)[ANY, cv(type)],
cnt[NULL, 'GRAND TOTAL'] = sum(cnt)[NULL, ANY],
cnt[NULL, ' drank ' || cnt[2, 'latte'] || ' cups of latte on 2 day'] = NULL,
cnt[NULL, CASE
WHEN cnt[3, 'espresso'] IS PRESENT THEN ' ACHIEVED'
ELSE ' FAILED'
END || ': drank espresso on 3 day'] = NULL
)
ORDER BY day, type DESC;
DAY TYPE CNT
---------- ---------------------------------------- ----------
1 turkish 1
1 espresso 1
2 turkish 2
2 espresso 1
2 black 1
3 latte 3
4 ice 1
4 black 2
turkish 3
latte 3
ice 1
espresso 2
black 3
GRAND TOTAL 12
drank cups of latte on 2 day
FAILED: drank espresso on 3 dayВ первом правиле используется цикл с итерациями по значениям выборки. Вложенные запросы, используемые внутри MODEL должны быль некореллированными. Следующее правило вычисляет итоговую сумму. Обратите внимание на строку «drank cups of latte on 2 day». Т.к. элемент cnt[2, 'latte'] не был найден, мы получили NULL по ссылке. Это поведение можно изменить директивой IGNORE NAV (добавляется после слова MODEL), тогда вместо ненайденных элементов и NULL, в расчёты будут подставляться: 0 для чисел, 1 января 2001 года для дат, пустая строка для строковых типов и NULL для всего остального. И, наконец, четвёртое правило демонстрирует использование выражения IS PRESENT, оно возвращает истину в случае, если заданный элемент существует, но, увы, эспрессо в среду не было выпито.
На этом вводная завершена, надеюсь вы получили общее представление и привыкли к синтаксису. Во второй части будет рассказано об управлении обновлением и созданием элементов, контроле вывода MODEL, вопросах применимости метода и его производительности. И, конечно же, будут разобраны более сложные примеры.
Читать продолжение
Литература
- Oracle® Database Data Warehousing Guide 11g Release 2 — Chapter 22 «SQL for Modeling».
- The SQL Model Clause of Oracle Database 10g.
