Работаем с системой Oracle (Oracle Database 10g или более поздней версией) и хотим использовать оператор MODEL.

Решение

В данном решении используются итеративные возможности оператора Oracle MODEL. Методика заключается в ранжировании служащих каждого отдела (по EMPNO, выбор произволен) с помощью ранжирующей функции ROWNUMBER OVER. Поскольку MODEL обеспечивает доступ к элементам таблицы как к элементам массива, доступ к предыдущим элементам массива можно организовывать путем вычитания рангов. Итак, для каждой строки создаем список, включающий имя каждого служащего плюс имя служащего, согласно рангу являющегося предыдущим по отношению к рассматриваемому:

1 2 3 4

10 11 12 13 14 15 16 17 18 19 20 21

24 25

29 30 31 32 33 34

select deptno, list

from ( select *

from (

select deptno,empno,ename,

lag(deptno)over(partition by deptno

order by empno) prior deptno

from emp )

model

dimension by

deptno,

row number()over(partition by deptno order by empno) rn

ename,

prior deptno,cast(null as varchar2(60)) list, count(*)over(partition by deptno) cnt, row number()over(partition by deptno order by empno) rnk

rules

list[any,any]

order by deptno,rn = case when prior deptno[cv(),cv() ] is null

then ename[cv(),cv()] else ename[cv(),cv()],

list[cv(), rnk[cv(),cv()]-1]

where cnt = rn

measures

Обсуждение

Первый шаг - используя функцию LAG OVER, получить значение DEPTNO предыдущего служащего (сортировка по EMPNO). Результаты группируются по DEPTNO, поэтому для первого служащего в отделе (соответственно EMPNO) будет возвращено значение NULL, для всех остальных - значение DEPTNO. Результаты следующие:

select deptno,empno,ename,

lag(deptno)over(partition by deptno

order by empno) prior deptno

from emp

Следующий шаг - проанализировать подоператор MEASURES оператора MODEL. Элементы списка MEASURES являются массивами:

ENAME

Массив всех значений ENAME таблицы EMP. PRIOR DEPTNO

Массив значений, возвращенных оконной функцией LAG OVER. CNT

Массив количества служащих по отделам. RNK

Массив рангов (по EMPNO) служащих в каждом DEPTNO.

Индексами массивов являются DEPTNO и RN (значение, возвращенное ранжирующей функцией ROW NUMBER OVER в подоператоре DIMENSION BY). Чтобы увидеть содержимое всех этих массивов, закомментируем код подоператора RULES оператора MODEL и выполним запрос в следующем виде:

select * from (

select deptno,empno,ename,

lag(deptno)over(partition by deptno

order by empno) prior deptno

from emp )

model dimension by (

deptno,

row number()over(partition by deptno order by empno) rn

measures (

ename,

prior deptno,cast(null as varchar2(60)) list, count(*)over(partition by deptno) cnt, row number()over(partition by deptno order by empno) rnk

rules

list[any,any]

order by deptno,rn = case when prior deptno[cv(),cv()] is null

then ename[cv(),cv()] else ename[cv(),cv()],

list[cv(),rnk[cv(),cv()]-1]

order by 1

Теперь, когда точно известно назначение каждого элемента оператора MODEL, перейдем к подоператору RULES. Посмотрим на выражение CASE. Как видим, в нем происходит вычисление текущего значения PRIOR DEPTNO. Значение NULL говорит о том, что в текущий мас-