Аналитические функции

scott@TKYTE816> select deptno, job,

10 11 12 13 14 15

1, ename, null)) ename l,

1, sal, null)) sal l,

2, ename, null)) ename 2,

2, sal, null)) sal 2,

3, ename, null)) ename 3,

3, sal, null)) sal 3,

4, ename, null)) ename 4, 4, sal, null)) sal 4

from (select deptno, job, ename, sal,

row number() over (partition by deptno, order by sal, ename)

rn from emp) group by deptno, job

max(decode(rn, max(decode(rn, max(decode(rn, max(decode(rn, max(decode(rn, max(decode(rn, max(decode(rn, max(decode(rn,

99 TURNER

99 WARD

Ранее в этой главе мы установили значение зарплаты 99 сотрудников отдела 30. Для транспонирования произвольного результирующего множества можно пойти еще дальше. Если имеется набор столбцов С1, С2, СЗ, ... CN и значения столбцов С1 ... Сх должны выдаваться во всех строках, а значения столбцов Сх+1 ... CN - в виде столбцов каждой строки, запрос будет иметь такой синтаксис:

Select Cl, C2, ... CX,

max(decode(rn,l,C{X+l},null)) cx+l l,.. .rnax(decode(rn,l,CN,null)) CN 1

max(decode(rn,2,C{X+l},null)) cx+l 2,...max(decode(rn,l,CN,null)) CN 2

max(decode(rn,N,c{X+l},null)) from (select Cl, C2, ... CN

row number() over (partition by Cl, C2, order by <столбцы>)

rn from T <условие>)

cx+l N,...max(decode(rn,l,CN,null)) CN N

group by Cl, C2,

В предыдущем примере в качестве С1 использовался столбец DEPTNO, в качестве С2 - JOB, СЗ представлял столбец ENAME, а С4 - SAL.

Для создания подобного запроса надо знать максимальное количество строк, которое может быть в фрагменте. Оно определяет количество генерируемых столбцов. В SQL

1074 Глава 12

необходимо знать количество выбираемых столбцов, т.к. в противном случае мы не сможем транспонировать результирующее множество. Таким образом, можно привести еще более общий пример создания запроса с транспонированием. Если заранее, до выполнения, общее количество столбцов не известно, придется использовать динамический SQL, чтобы справиться с переменным списком выбора в операторе SELECT. Для демонстрации этого можно написать PL/SQL-процедуру; в результате мы получим универсальную процедуру для транспонирования любого результирующего множества. Эта процедура (я поместил ее в пакет) будет иметь следующую спецификацию:

scott@TKYTE816> create or replace package my pkg

2 as

3 type refcursor is ref cursor;

4 type array is table of varchar2(30);

5 procedure pivot(p max cols in number default NULL,

6 p max cols query in varchar2 default NULL,

7 p query in varchar2,

8 p anchor in array,

9 p pivot in array, 10 p cursor in out refcursor);

12 end;

Package created.

Необходимо задать значения для параметра P MAX COLS или для параметра P MAX COLS QUERY. Для создания SQL-оператора необходимо знать количество столбцов в запросе, и эти параметры позволят создать запрос с соответствующим количеством столбцов. Задавать этим параметрам надо значение, полученное в результате выполнения такого запроса:

scott@TKYTE816> select max(count(*)) from emp group by deptno, job;

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

Параметр P QUERY - это запрос, собирающий данные. Для представленного выше примера можно передать следующий запрос:

10 from (select deptno, job, ename, sal,

11 row number() over (partition by deptno, job

12 order by sal, ename)

13 rn from emp)

Следующие два параметра - массивы имен столбцов. Параметр P ANCHOR указывает, значения каких столбцов остаются в строках, а параметр P PIVOT перечисляет столбцы, значения которых выносятся в строки. В рассмотренном ранее примере P ANCHOR = (DEPTNO, JOB), a P PIVOT = (ENAME.SAL). Отвлечемся ненадолго от нашей темы и рассмотрим, как может выглядеть вызов процедуры транспонирования результирующего множества:

scott@TKYTE816> variable x refcursor

scott@TKYTE816> set autoprint on

Аналитические функции

scott@TKYTE816> begin

2 my pkg.pivot

3 (p max cols query => select max(count(*)) from emp

4 group by deptno,job,

5 p query => select deptno, job, ename, sal,

6 row number() over (partition by deptno, job

7 order by sal, ename)

8 rn from emp a,

10 panchor => my pkg.array(DEPTNO,JOB),

11 ppivot => mypkg.array (ENAME, SAL),

12 pcursor => :x);

13 end;

PL/SQL procedure successfully completed.

DEPTNO JOB ENAME SAL 1 ENAME 2 SAL 2 ENAME 3 SAL 3 ENAME SAL 4

TURNER

99 WARD

9 rows selected.

Как видите, запрос динамически переписан на базе разработанного универсального шаблона. Реализация тела пакета достаточно проста:

scott@TKYTE816> create or replace package body my pkg 2 as 3

4 5 6

10 11 12 13 14 15 16

procedure pivot(p max cols in number default null,

p max cols query in varchar2 default null,

p query in varchar2,

p anchor in array,

p pivot

in array,

17 18 19

p cursor in out refcursor)

l max cols number; l query long; l cnames array; begin

оеделяем количество столбцов, которые надо возвращать мы либо ЗНАЕМ его, либо получаем запрос, с помощью которого его можно узнать if (p max cols is not null) then

l max cols := p max cols; elsif (p max cols query is not null)