Решение

Используйте в операторе MODEL агрегацию и выражения CASE точно так же, как делали бы это при разворачивании традиционными методами. Основное отличие в данном случае в том, что для хранения значений, полученных в результате агрегации, используются массивы, и массивы возвращаются в результирующем множестве:

select max(d10) d10, max(d20) d20, max(d30) d30 from ( select d10,d20,d30

from ( select deptno, count(*) cnt from emp group by deptno ) model

dimension by(deptno d) measures(deptno, cnt d10, cnt d20, cnt d30) rules(

d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end, d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end, d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end

Обсуждение

Оператор MODEL является исключительно полезным и мощным дополнением к языку SQL для Oracle. Начав работать с MODEL, вы обна-

поработать с Oracle-оператором MODEL. В отличие от оператора SQL Server PIVOT оператор Oracle MODEL предназначен не для разворачивания результирующих множеств. На самом деле, я не ошибусь, если скажу, что применение оператора MODEL для разворачивания является применением его не по назначению; очевидно, что он не для этого был задуман. Тем не менее оператор MODEL обеспечивает интересный подход к обычной задаче. В данном конкретном случае требуется преобразовать следующее результирующее множество:

select deptno, count(*) cnt from emp group by deptno

DEPTNO CNT

10 3

20 5

30 6

в такое множество:

D10 D20 D30

ружите такие удобные возможности, как итерация, доступ к значениям строк как к элементам массива, возможность применения к результирующему множеству логики upsert 1 и возможность создавать справочные модели. В данном рецепте не используется ни одна из всех этих предлагаемых MODEL замечательных возможностей, но ведь здорово уметь посмотреть на проблему с разных сторон и использовать разные средства неожиданным образом (если нет других причин, то хотя бы, чтобы понять, в каких случаях те или иные возможности более полезны, чем остальные).

Первый шаг к пониманию решения - проанализировать вложенный запрос конструкции FROM. Он просто подсчитывает в таблице EMP количество служащих в каждом отделе. Результаты показаны ниже:

select deptno, count(*) cnt from emp group by deptno

DEPTNO CNT

10 3

20 5

30 6

Результирующее множество - это то, что получает для работы MODEL. Посмотрев на конструкцию MODEL, можно выделить три подоператора: DIMENSION BY, MEASURES и RULES. Начнем с MEASURES.

Элементами списка MEASURES являются массивы, которые были объявлены для этого запроса. В запросе используется четыре массива: DEPTNO, D10, D20 и D30. Как и у столбцов в списке оператора SELECT, у массивов в списке MEASURES могут быть псевдонимы. Как видите, три из четырех массивов являются значениями поля CNT вложенного запроса.

Если в списке MEASURES находятся массивы, тогда в подоператоре DIMENSION BY - индексы массивов. Рассмотрим следующее: массив D10 - просто псевдоним CNT. Если взглянуть на результирующее множество вложенного запроса, приведенного выше, увидим, что CNT имеет три значения: 3, 5 и 6. При создании массива CNT создается массив с тремя элементами, а именно тремя целыми числами, 3, 5 и 6. Как теперь организовать доступ к каждому из этих элементов по отдельности? С помощью индекса массива. Индекс, определенный в подоператоре DIMENSION BY, имеет три значения: 10, 20 и 30 (из приведенного выше результирующего множества). Итак, к примеру, следующее выражение:

d10[10]

Upsert - изменить данные, если они существуют/вставить, если их еще нет. - Примеч. перев.

Как видите, именно подоператор RULES меняет значения массивов. Если это до сих пор непонятно, выполните запрос, закомментировав выражения в конструкции RULES:

select deptno, d10,d20,d30 from ( select deptno, count(*) cnt from emp group by deptno ) model

dimension by(deptno d) measures(deptno, cnt d10, cnt d20, cnt d30) rules(

d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,

равно 3, поскольку здесь осуществляется доступ к значению CNT в массиве D10 для DEPTNO 10 (которое равно 3).

Поскольку все три массива (D10, D20, D30) содержат значения CNT, для всех трех результаты одинаковые. Как тогда поместить элемент в соответствующий массив? Введем подоператор RULES. Из результирующего множества, показанного ранее, можно увидеть, что значениями DEPTNO являются 10, 20 и 30. Выражения с участием CASE в конструкции RULES просто определяют каждое значение массива DEPTNO:

Если значение равно 10, сохраняем значение CNT, соответствующее DEPTNO 10, в D10[10], в противном случае сохраняем 0.

Если значение равно 20, сохраняем значение CNT, соответствующее DEPTNO 20, в D20[20], в противном случае сохраняем 0.

Если значение равно 30, сохраняем значение CNT, соответствующее DEPTNO 30, в D30[30], в противном случае сохраняем 0.

Если вы почувствовали себя Алисой, падающей в кроличью нору, не волнуйтесь; просто остановитесь и выполните все то, что мы обсудили. Иногда проще прочитать, взглянуть на код, реализующий то, что было прочитано, вернуться и прочитать все еще раз. Представленный далее код на самом деле довольно прост, надо лишь увидеть его в действии:

select deptno, d10,d20,d30 from ( select deptno, count(*) cnt from emp group by deptno ) model

dimension by(deptno d) measures(deptno, cnt d10, cnt d20, cnt d30) rules(

d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end, d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end, d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end