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

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

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

Подсчет процентов в группе. Показать, какой процент от общей зарплаты по отделу составляет зарплата каждого сотрудника. Берем его зарплату и делим на сумму зарплат по отделу.

Запросы перв1х N. Найти N сотрудников с наибольшими зарплатами или N наи-

более продаваемых товаров по регионам.

Подсчет скользящего среднего. Получить среднее значение по текущей и предыдущим N строкам.

В1полнение ранжирующих запросов. Показать относительный ранг зарплаты сотрудника среди других сотрудников того же отдела.

Аналитические функции, появившиеся в версии Oracle 8.1.6, создавались для решения именно этих задач. Они расширяют язык SQL так, что подобные операции не только проще записываются, но и быстрее выполняются по сравнению с использованием чистого языка SQL. Эти расширения сейчас изучаются комитетом ANSI SQL с целью включения в спецификацию языка SQL.

1038 Глава 12

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

Пример

Простой пример подсчета промежуточной суммы зарплат по отделам с описанием того, что же в действительности происходит, позволят получить начальное представление о принципах использования аналитических функций:

tkyte@TK[E816> break on deptno skip 1

tkyte@TKIE816> select ename, deptno, sal,

runningtotal,

2 sum (sal) over

3 (order by deptno, ename)

4 sum(sal) over

5 (partition by deptno

6 order by ename) department total,

7 row number() over

8 (partition by deptno

9 order by ename) seq

10 from emp

11 order by deptno, ename

12 /

14 rows selected.

В представленном выше коде удалось получить значение RUNNINGTOTAL для запроса в целом. Это б1ло сделано по всему упорядоченному результирующему множеству с помощью конструкции SUM(SAL) OVER (ORDER BY DEPTNO, ENAME). Также удалось подсчитать промежуточные суммы по отделам, сбрасывая их в ноль при переходе к следующему отделу. Этого удалось добиться благодаря конструкции PARTITION BY

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

DEPTNO в SUM(SAL) - в запросе б]ла указана конструкция, задающая условие разбиения данных на группы. Для последовательной нумерации строк в каждой группе, в соответствии с критериями упорядочения, использовалась функция ROW NUMBER() (для в]дачи этого номера строки б]л добавлен столбец SEQ). В результате видно, что SCOTT - четвертый по списку сотрудник в отделе 20 при упорядочении по фамилии (ENAME). Функция ROW NUMBER() используется и во многих других ситуациях, например для транспонирования или преобразования результирующих множеств (как будет описано далее).

Этот новый набор функциональных возможностей содержит много замечательного. Он открывает абсолютно новые перспективы работы с данными. Можно избавиться от большого объема процедурного кода и сложных (или неэффективных) запросов, требующих много времени на разработку, и получить при этом желаемый результат. Чтобы почувствовать, насколько эффективными могут быть аналитические функции по сравнению с чисто реляционными способами , давайте оценим производительность в случае 1000 строк, а не 14. При этом сравним производительность двух запросов: с новыми аналитическими функциями и на основе стар1х реляционных методов. Следующая пара операторов позволит создать аналог таблицы SCOTT.EMP с тремя столбцами - ENAME, DEPTNO и SAL - и индексом (единственным необходимым в данном примере). Я буду выбирать данные по столбцам DEPTNO и ENAME:

tkyte@TKYTE816> create table t

2 as

3 select object name ename,

4 mod(object id,50) deptno,

5 object id sal

6 from all objects

7 where rownum <= 1000

Table created.

tkyte@TKYTE816> create index t idx on t(deptno,ename); Index created.

Повторим запрос, но к новой таблице, задав установку AUTOTRACE TRACEONLY, чтобы увидеть, сколько и чего пришлось делать (для этого необходимо наличие роли PLUSTRACE):

tkyte@TKYTE816> set autotrace traceonly tkyte@TKYTE816> select ename, deptno, sal,

2 sum (sal) over

3 (order by deptno, ename) running total,

4 sum(sal) over

5 (partition by deptno

6 order by ename) department total,

7 row number() over

8 (partition by deptno

9 order by ename) seq

10 from t emp

11 order by deptno, ename101