решите его не использовать, полезен тем, что демонстрирует некоторые возможности оператора MODEL, такие как итерации и доступ к строкам как к элементам массива.

Чтобы упростить объяснение, я собираюсь показать небольшие вариации подзапроса, содержащего оператор MODEL. Приведенный далее код является кодом подзапроса из решения за исключением того, что здесь жестко запрограммировано возвратить двоичное представление значения 2:

select bin from dual model

dimension by ( 0 attr ) measures ( 2 num,

cast(null as varchar2(30)) bin,

0123456789ABCDEF hex

rules iterate (10000) until (num[0] <= 0) ( bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)bin[cv()],

num[0] = trunc(num[cv()]/2)

Следующий запрос выводит значения, возвращенные в результате одной итерации конструкции RULES из запроса выше:

select 2 start val,

0123456789ABCDEF hex,

substr(0123456789ABCDEF,mod(2,2)+1,1) cast(null as varchar2(30)) bin, trunc(2/2) num from dual

START VAL HEX BIN NUM

2 0123456789ABCDEF 0 1

Поле START VAL содержит число, для которого должно быть получено двоичное представление, в данном случае это 2. Значение столбца BIN - результат операции SUBSTR над строкой 0123456789ABCDEF (в оригинальном решении HEX). Значение NUM - это признак выхода из цикла.

Как видно из предыдущего результирующего множества, после первой итерации цикла BIN равен 0, и NUM равен 1. Поскольку значение NUM не меньше или не равно 0, выполняется следующая итерация, результаты которой представляет приведенное ниже выражение SQL:

select num start val,

substr(0123456789ABCDEF,mod(1,2)+1,1) bin bin,

2 1 0

2 0 10

Разворачивание ранжированного результирующего множества

Задача

Требуется ранжировать значения таблицы, затем развернуть результирующее множество и получить три столбца. Идея в том, чтобы показать в разных столбцах три наибольших значения, три следующих значения и все остальные. Например, необходимо ранжировать слу-

trunc(1/2) num from ( select 2 start val,

0123456789ABCDEF hex,

substr(0123456789ABCDEF,mod(2,2)+1,1) cast(null as varchar2(30)) bin, trunc(2/2) num from dual )

START VAL BIN NUM

1 10 0

После следущей итерации операция SUBSTR над HEX возвращает 1, к которой присоединяется предыдущее значение BIN, 0. Проверочное значение, NUM, теперь равно 0; таким образом, это последняя итерация, а возвращенное значение 10 - двоичное представление числа 2. Разобравшись с тем, что происходит, можно убрать итерацию из оператора MODEL и строка за строкой проследить, как применяются правила для получения окончательного результирующего множества, что показано ниже:

select 2 orig val, num, bin from dual model

dimension by ( 0 attr ) measures ( 2 num,

cast(null as varchar2(30)) bin,

0123456789ABCDEF hex

rules (

bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)bin[cv()], num[0] = trunc(num[cv()]/2),

bin[1] = substr (hex[0],mod(num[0],2)+1,1)bin[0], num[1] = trunc(num[0]/2)

ORIG VAL NUM BIN

жащих в таблице EMP по значениям SAL и затем развернуть результаты в три столбца. Должно быть получено следующее результирующее множество:

TOP 3

NEXT 3

REST

KING (5000) BLAKE (2850)

FORD (3000) CLARK (2450)

SCOTT (3000) ALLEN (1600)

JONES (2975)

TURNER (1500)

MILLER (1300)

MARTIN (1250)

WARD (1250)

ADAMS (1100)

JAMES (950)

SMITH (800)

Решение

Прежде всего, в данном решении необходимо применить ранжирующую функцию DENSE RANK OVER, которая выполнит ранжирование служащих по SAL, допуская при этом одинаковые значения ранга для дублирующихся значений. В результате этого можно сразу увидеть три наивысшие, следующие три и все остальные заработные платы. Далее с помощью ранжирующей функции ROW NUMBER OVER сортируем служащих в группах (группа три наибольшие , следующие три или группа остальные ). Чтобы облагородить результаты, выполняем после этого классическое транспонирование, применяя при этом встроенные функции для работы со строками, доступные на используемой платформе. В следующем решении используется синтаксис Oracle. Поскольку DB2 и SQL Server 2005 поддерживают оконные функции, трансформировать решение под эти платформы не составит труда:

1 select max(case grp when 1

3 max(case grp when 2

4 ill

5 max(case grp when 3

6 (

7 from (

8 select ename,

9 sal,

10 rnk,

11 case when rnk <= 3

12 when rnk <= 6

13 else

14 end grp,

15 row number()over (

16 partition by case 17

19 end

20 order by sal

then rpad(ename,6) sal ) end) top 3, then rpad(ename,6) sal ) end) next 3, then rpad(ename,6) sal ) end) rest

then 1 then 2

when rnk <= 3 then 1 when rnk <= 6 then 2

else 3

desc, ename