Диалект Oracle SQL: Model в примерах. Часть 1

Любой разработчик, имеющий дело с генерацией отчётности из баз данных, регулярно сталкивается с построением громоздких запросов. Часто это бывает связано с ошибками проектирования БД, и, ещё чаще, со сложностями преобразования извлекаемых данных.

К последним можно отнести применение итерационных методов вычисления, подсчёт промежуточных итогов по подгруппам, расчёты, в которых используются значения соседних строк выборки, сложное форматирование строк и подобные задачи. Такие преобразования часто выносятся с уровня БД на уровень сервера приложений или клиента, что сказывается на производительности и удобстве сопровождения кода. Для решения этих задач 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
        13

 

ITERATE задаёт количество итераций цикла (начиная с 0), а необязательная директива UNTIL — условие выхода из него (которое сработало, судя по тому что вы ещё не нажали Ctrl + End). Доступ к счётчику осуществляется через переменную

iteration_number

.

 

Диапазоны и агрегирование


image

Выше мы видели примеры с расчётом значений отдельных элементов. Кроме этого, правила можно задавать для их групп, отфильтрованных по заданным условиям. Для этого используются второй тип ссылок — символические (ранее были только позиционные). Индексы в символических ссылках могут содержать проверку условий истинности, например:

 

  • 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, вопросах применимости метода и его производительности. И, конечно же, будут разобраны более сложные примеры.

 

Литература

  1. Oracle® Database Data Warehousing Guide 11g Release 2 — Chapter 22 «SQL for Modeling». HTML
  2. The SQL Model Clause of Oracle Database 10g. PDF



Автор материала: exaide



Опубликовал admin
10 Авг, Вторник 2010г.



Программирование для чайников.