1070

Глава 12

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

Запрос с транспонированием

При запросе с транспонированием (опорный запрос

pivot query) берутся данные вида:

и выдаются в следующем виде:

Cl C2 C3(l) C3(2) C3(3)

Этот запрос преобразует строки в столбцы. Например, можно в]дать должности сотрудников отдела в виде столбцов:

JOB 3

DEPTNO

JOB 1

10 CLERK 20 ANALYST 30 CLERK

а не в виде строк:

DEPTNO JOB

JOB 2

MANAGER ANALYST MANAGER

PRESIDENT CLERK SALESMAN

10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN

Я представлю два примера запросов с транспонированием. Первый - разновидность описанного выше запроса трех сотрудников с максимальными зарплатами. Второй пример показывает, как транспонировать любое результирующее множество, и дает шаблон необходимых для этого действий.

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

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

по величине зарплатой и т.д. С помощью новых аналитических функций это сделать просто (а до их появления - практически невозможно):

ops$tkyte@DEV816> select deptno.

2 max(decode(seg,1,ename,null))

3 max(decode(seq,2,ename,null))

4 max(decode(seq,3,ename,null))

5 from (SELECT deptno, ename,

6 row number() OVER

7 (PARTITION BY deptno

8 ORDER BY sal desc NULLS LAST)

9 FROM emp)

10 where seg <= 3

11 group by deptno

12 /

DEPTNO HIGHEST PA SECOND HIG THIRD HIGH

highest paid, second highest, third highest

10 KING 20 SCOTT 30 BLAKE

CLARK FORD ALLEN

MILLER

JONES

TURNER

Мы создали внутреннее результирующее множество, где сотрудники отделов пронумерованы по убыванию зарплат. Функция decode во внешнем запросе оставляет только строки со значениями номеров 1, 2 или 3 и присваивает взятые из них фамилии соответствующему столбцу. Конструкция GROUP BY позволяет избавиться от лишних строк и получить сжатый результат. Возможно, понять, что я имею в виду, проще, если сначала посмотреть на результирующее множество запроса без конструкций GROUP BY и МАХ:

scott@TKYTE816> select deptno,

2 decode(seq,1,ename,null) highest paid,

3 decode(seq,2,ename,null) second highest,

4 decode(seq, 3,ename,null) third highest

5 from (select deptno, ename,

6 row number() over

7 (partition by deptno

8 order by sal desc nulls last)

9 seq from emp)

10 where seq <= 3

11 /

DEPTNO HIGHEST PA SECOND HIG THIRD HIGH

10 10 10

20 20 20

KING

SCOTT

30 ALLEN 30

CLARK

FORD

BLAKE

MILLER

JONES

1072 Глава 12

30 MARTIN

9 rows selected.

Функция агрегирования МАХ будет применяться конструкцией группировки GROUP BY по столбцу DEPTNO. Значение в столбце HIGHESTPAID для отдела только в одной строке будет непустым - в остальных строках этот столбец всегда будет иметь значение NULL. Функция МАХ будет выбирать только строку с непустым значением. Поэтому сочетание группирования и функции МАХ позволит, убрав значения NULL, свернуть результирующее множество и получить желаемый результат.

Если есть таблица Т со столбцами С1 и С2 и необходимо получить результат вида:

Cl C2(l) C2(2) .... C2(N),

где столбец С1 должен присутствовать во всех строках (значения в]даются по направлению к концу страницы), а столбец С2 должен быть транспонирован так, чтобы он представлялся в виде строк (значения С2 выдаются по направлению к концу строки, они становятся столбцами, а не строками), надо создать такой запрос:

select cl,

mах(decode(rn,1,с2,null)) c2 l, max(decode(rn,2,c2,null)) c2 2,

max(decode(rn,N,c2,null)) c2 N from (select cl, c2,

row number() over (partition by Cl

order by <столбцы>)

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

group by Cl

В представленном выше примере в качестве С1 использовался столбец DEPTNO, a в качестве С2 - ENAME. Поскольку упорядочение выполнялось по критерию SAL DESC, первые три полученные строки соответствовали трем наиболее высокооплачиваемым сотрудникам соответствующего отдела (напоминаю: если максимальные зарплаты получало четыре человека, одного из них мы теряем).

Второй пример: транспонировать результирующее множество. Рассмотрим более общий случай, когда опорный (отсюда и второе название запроса - опорный) столбец, С1, и транспонируемый столбец, С2, представляют собой наборы столбцов. Решение очень похоже на то, что представлено выше. Предположим, необходимо для каждого отдела и должности выдать фамилии и зарплаты сотрудников. При этом в отчете фамилии и соответствующие зарплаты должны выдаваться в строке, как столбцы. Кроме того, в строке сотрудников надо упорядочивать слева направо по возрастанию зарплат. Для решения этой проблемы необходимо выполнить следующее:

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

В результате мы получаем количество столбцов. Теперь можно создавать запрос: